Forensic analysis of SQLite Write-Ahead Log (WAL)

Hi

Sorry for the long post but I hope you find it useful.

At the risk of plugging my own software these tools (as far as I can see) miss on a few very important facets of WAL file forensics.

Firstly as a bit of background - you correctly mention that data can reside in a WAL file until a checkpoint occurs (default when a transaction completes and the WAL is > 1000 pages). But, and this is a huge but, the pages that are in the WAL can comprise a number of transactions and therefore a number of previous database "states".

Transactions, for those that don't know, simply put are there to enforce ACID compliance, or in other words (again simplified) it insures DB integrity such that the database file itself always goes form one valid state to another, i.e. an error writing (say) one table that related to another table could not affect the integrity of the DB. This means for instance that if you make a change to a messaging DB and want to update the message table with a post for a new user then you could wrap an update to the messages table and the users table inside a transaction and the write will only be successful if both tables (and any associated indexes) are updated together. A further advantage of using transactions is that multiple consecutive writes wrapped in a transaction are usually much quicker than single writes to a database (a single write is also a transaction which is started and ended by the DBMS/SQLite automatically). Each transaction is appended to the current end of the WAL but it is important to remember/know that the WAL is not truncated when checkpointed, writes just start again from the beginning so there is a sort of WAL slack.

So what does this mean for us? It means that

a) the database (i.e. the main database file) is not updated at all until the WAL is checkpointed.

b) it means that multiple transaction can and often do exist in the WAL and this also means that if you have the correct approach you can often unwind the WAL and not just see the database as it was before the last update, but before the previous X updates.

c) if you want to see the DB in its current state you must process the WAL as the pages that make up the current state are only taken from the main database file if a later version is not present in the WAL

d) it is not enough to know that page comes from the WAL it needs to be looked at and its place in the transaction history understood.

e) one transaction in a WAL builds on a previous transaction – i.e. if SQLite needs to read a page from the DB it looks in the current transaction in the WAL, then the previous transaction… then the DB itself until it finds the latest copy of the page.

f) Finally you need to understand that a page in the WAL is different from a record within that page. If you update (add/modify/delete) one record in a page then the whole page will be written to the WAL so just because a record of interest is found within a WAL you need to understand its context.

With my software when I open a database I also provide the user with the option of also processing the WAL. If this is taken then a copy database is created with all records from the main database and all records from the WAL (you can also select to recover deleted records from both main db and WAL). My software will also scan though the WAL and build a list of all transactions and choose whether to see the DB in its latest state (as SQLite would see it) and also offers you the ability to rollback to any of the previous transactions and see the DB as it used to be. Importantly it still shows all of the other records/pages and identifies where the page comes (Page number DB/WAL and the WAL frame and transaction/commit number) from so you the investigator sees everything and can then filter the records if required to show the ones you are interested in.

The following screenshots hopefully show the process I use

This shows the process dialog used when selecting a database, I am choosing not to recover deleted records from DB or WAL pages to keep the example clearer.

You then get presented with a dialog asking you which WAL frame (transaction) you want to process up until. The default is to process all of the WAL frames and see the database in its current state

Processing then completes and you see all of the records from all of the pages both in the database and in the WAL. Some points to note

There are multiple copies in the WAL for the same page, this is because each time a page changes it gets written (appended) to the WAL up until the point a checkpoint occurs when all pages are then committed to the database.

The current live records are marked as true in the sfIsLive column, the others are marked as false. False does not mean they have been deleted it means they are not from the current live page.

The screen shot below does show a deleted record text = " the password is my date of birth"

So now I can process the DB again, but look at its state at an earlier point in it's history so I choose transaction number 8 which is WAL commit frame 12

In this screenshot with live records extracted from the WAL up until checkpoint 8 you can see the records marked as live now include the deleted record referred to above (the one about my dodgy guessable password) i.e. this is the state of the database/table before the delete operation.

I hope this helps and shows why just knowing a record is from a WAL is not enough.

More information on the Forensic Browser for SQLite (part of the Forensic Toolkit for SQLite) and a link to request a fully functional demo, here http//sandersonforensics.com/forum/content.php?198-Forensic-Browser-for-SQLite

Thanks for a great reply, Mr. Sanderson! I'll check your product for sure!

I have just added a new article to my web site whcih shows how you can identify a window of time when a record was deleted when WAL journals are in use.

http//sandersonforensics.com/forum/content.php?261-Timelining-events-in-a-WAL-based-SQLite-DB

Hopefully you find it interesting.