A. When a journal is in use (potentially).

The raison d’etre for a journal, be it a traditional rollback journal or the newer SQLite Write Ahead Log (WAL) file is to maintain database integrity. Simply put if an operation fails for whatever reason then the changes to the database are unwound to put the DB back to its last known good state. It might seem obvious then to state that a copy of securely deleted data would need to be kept in order to facilitate this functionality. This securely deleted data can and sometimes does exist for quite some time.

We also need to understand that SQLite groups actions together in transactions, transactions can be one database update (write, modify, delete) or it can be many thousands of such actions as determined by the user. Think of a simple messaging application whereby a message is received asking to be “friends” – our hypothetical app needs to write the friend request to the messages table and add a user to the users table. It would make no sense to update the messages table with a message that referred to user x when user x’s details had not yet been added to the users table, so both these actions could be wrapped in a transaction so either both tables are updated or neither is updated.

So how does this impact secure delete? 

First we need to understand what secure delete does, according to the SQLite website the command (pragma) that initiates secure delete says “When secure-delete is (sic) on, SQLite overwrites deleted content with zeros.”

Well that gives me a warm fuzzy feeling, when I delete it, it’s gone! But hang on, if SQLite immediately overwrites something I delete and something goes wrong before the transaction completes how can SQLite rewind the DB to its last good state? 

The simple answer is it can’t. in order to maintain database integrity SQLite MUST maintain a copy of the data that has been deleted somewhere until it ‘knows’ the last transaction has completed correctly, that somewhere is the journal.

Rollback Journals

Lets look at an example with a rollback journal. For the purposes of this demo I do the following:

  • create a couple of tables (messages and users)
  • add some users
  • add 9 numbered records to the messages table
  • delete records 1-3
  • add a further 9 records
  • delete records 11 and 12

Also unless you specifically tell SQLite differently it treats each individual database update as a transaction, so I group the updates into sets of 4 so each of the 4 updates forms a transaction. The simplified instructions I use are as follows:


begin transaction;
insert into users (1, 'System');
insert into users (2, 'Helen');
insert into messages (1, strfTime('%s','2016-03-01 09:12:45'), 0, 1, 1, 'Hello welcome to Sanderson Forensics test messaging database');
insert into messages (2, strfTime('%s','2016-03-02 15:08:14'), 1, 1, 2, 'Hi Honey just got this new messaging app - it''s awful :)');
end transaction;
begin transaction;
insert into messages (3, strfTime('%s','2016-03-02 15:10:12'), 0, 1, 2, 'Sounds great - another app, I''m soo excited');
insert into messages (4, strfTime('%s','2016-03-02 15:12:45'), 1, 1, 2, 'haha, thought you''d like it');
insert into messages (5, strfTime('%s','2016-03-02 15:14:42'), 1, 1, 2, 'anyway i''ll be home in a couple of hours hour xx');
insert into messages (6, strfTime('%s','2016-03-03 17:12:45'), 1, 1, 2, 'just left the office....');
end transaction;
begin transaction;
insert into messages (7, strfTime('%s','2016-03-04 09:02:01'), 1, 1, 2, 'just got to work');
insert into messages (8, strfTime('%s','2016-03-04 09:10:12'), 0, 1, 2, 'cool have a nice day x');
insert into messages (9, strfTime('%s','2016-03-04 09:22:02'), 0, 2, 1, 'Paul, be my friend, Darcy');
insert into users (3, 'Darcy');
end transaction;
begin transaction;
delete from messages where id < 4;
insert into messages (10, strfTime('%s','2016-03-04 09:25:43'), 1, 1, 3, 'Hiya mate - didn''t know you were on this app');
insert into messages (11, strfTime('%s','2016-03-04 09:27:43'), 0, 1, 3, 'No time for pleasantries, Ive transferred the money from the company account');
insert into messages (12, strfTime('%s','2016-03-04 09:29:22'), 0, 1, 3, 'This is really scary - I dont do illegal - no more after this');
insert into messages (13, strfTime('%s','2016-03-04 09:29:22'), 1, 1, 3, 'OK good - thats it we are quits now');
end transaction;
begin transaction;
insert into messages (14, strfTime('%s','2016-03-04 10:03:21'), 0, 1, 2, 'Hi honey - are you working late? what time will you be home?');
insert into messages (15, strfTime('%s','2016-03-04 10:05:10'), 1, 1, 2, 'about 8pm hopefully');
insert into messages (16, strfTime('%s','2016-03-04 13:13:40'), 0, 1, 3, 'Oh - I forgot to say, delete any trace of this conversation');
insert into messages (17, strfTime('%s','2016-03-04 14:08:21'), 0, 1, 2, 'Hi honey - did you get my message form earlier?');
end transaction;
insert into messages  (18, strfTime('%s','2016-03-04 17:05:08'), 1, 1, 2, 'been in a meeting, leaving shortly, quick pint with the boys and then ill be on the train');
delete from messages where id = 11 or id = 12;
end transaction;

Before we look at the recovered data with the Forensic Browser for SQLite a quick summary of how a rollback journal works.

Essentially before a change to a page in the database is made the page is copied to the rollback journal. The database is then updated and only when this is successful the rollback journal is invalidated by wiping the journal header (in fact the default mode is to delete the rollback journal file in its entirety - more on this later). If something goes wrong e.g. the application/devices crashes. When it is restarted the valid rollback journal is still there so SQLite will know there has been a problem and will transfer the cached/saved records back into the database to restore it to its last known good state.

When opening a database with the Forensic Browser for SQLite if there is an associated journal or WAL then the user is prompted as to whether they want to process this also. All records from both the database and the journal/WAL are then added to the parsed data and marked as to their source:

The following screenshot shows an SQLite database and its associated journal loaded into the Forensic Browser for SQLite. You can see that the records that are in the DB are marked as live and that records 1-3 and 11 & 12 are missing as we expect.

The journal however still has a copy of the deleted records 11 & 12, from the last transaction. Records 1-3 have been overwritten in the journal by the subsequent transactions since they were deleted.

So while the journal exists the secure delete is not as effective as you may think. To be clear this is an intended consequence of the operation of SQLite and should not be considered a bug.

It is also useful to consider when we may find helpful data in a journal. Rollback Journals held on disk operate in one of three modes, delete, truncate and persist. These three modes work as follows.

When a journal is in delete mode (the default) and a transaction is completed successfully the journal file is deleted. In this instance, the journal could potentially be recovered using normal forensic techniques and any securely deleted data from the last transaction recovered.

When a journal is in truncate mode and a transaction completes successfully, the journal file size is reset to 0 bytes, the file is truncated. As above the content of the truncated journal could potentially be recovered forensically and securely deleted records from the last transaction recovered.

When a journal is in persist mode (this is the mode I used for my demo) and a transaction completes successfully, the header for the journal file is overwritten but otherwise the journal stays on disk and of course, all securely deleted data from the last transaction will be easily accessible.

WAL Journals

What happens when we do the same operation with WAL files? This is much more fun 

First - how do WALs operate? The explanation for the SQLite web site is that they turn traditional journaling on its head and that rather than copying the page that will be changed to the journal they leave the original page in the database and write the new page to the WAL.

The database reader when it needs to read a page looks for the latest copy in the WAL and only reads the main database if it can't find a copy in the WAL.

The database is always left in a good state as any errors (power cuts etc.) will only affect writes to the WAL.

There is another difference/complication. Rather than copy the changed pages from the WAL to the database after every transaction, multiple transactions are appended in the WAL one after another until a WAL 'checkpoint' occurs. By default, this is when a transaction takes the WAL above 1000 pages of data, if the application specifically triggers one or when/if the application shuts down gracefully.

At a checkpoint, all changes pages are written back to the main database file. However, after a successful checkpoint, the WAL is not deleted or truncated, any new pages start to be written from the start of the WAL file leaving all older pages in a sort of WAL slack.

The WAL file is only deleted when the application is shut down gracefully and all of the changed pages in the WAL have been successfully written to the database.

After opening the database and associated WAL in the Forensic Browser for SQLite the summary of the data in the messages table is as follows. As you can see from the messages marked as true in the sfIsLive column the live records are missing records 1, 2, 3, 11 & 12 as we expect:

We can also see multiple copies of the securely deleted records 1-3 and a copy of the securely deleted records 11 & 12.

There are multiple copies of records 1-3 which were in database page 3 because each time page 3 was updated a new copy of it was written to the WAL. We can see that records 1-9 where all in DB page 3 at some time and also see that page 3 was updated in commits 1, 2 & 3.

Of interest, we can get an idea of what SQLite is doing in the background as we can see that message ID 4 has been in both DB page 3 and DB page 4 and so at some point, SQLite has moved these records within the DB.

We can also see that there are no pages of data actually in the database, this is because in our example a checkpoint has not yet occurred and all pages are waiting to be written to the main database file.

As stated writes to WAL files start at the beginning of a file after a checkpoint takes place. WAL files are deleted when the application shuts down (after any associated successful checkpoint). So if a WAL file exists or can be recovered then it may be possible to recover records from multiple previous transactions.

There is a further feature in the Forensic Browser for SQLite that allows you to "look" at a database as it used to be. When you open a DB and choose to process the associated WAL the Browser will ask "which is the last commit frame that you want to use?". Typically this will be the latest but by choosing an older commit frame you can get the Browser to show you the database as it was when that frame was the last to be written. Essentially you can wind back the clock on the database state.

The table below shows the database when commit frame 5 was the last live frame. In the screenshot, you can see that records 1-3 have yet to be deleted and that records 7 onwards have not yet been written (although they are still shown as this is a forensic tool after all).

So in summary:

If a journal file exists or can be recovered then you can potentially find evidence of any securely deleted records from the last transaction.

If a WAL file still exists or can be recovered you can potentially find evidence of any securely deleted records from often many previous transactions.