SQLite Write Ahead ...
 
Notifications
Clear all

SQLite Write Ahead Log (WAL)

5 Posts
2 Users
0 Likes
609 Views
(@alexc)
Posts: 301
Reputable Member
Topic starter
 

If you've dealt with any SQLite data, whether on a desktop or a mobile device recently, you may well have seen "-wal" files alongside the main database file. This is because of a journalling mechanism called "Write Ahead Log".

This mechanism affords us some new opportunities but also some potential pitfalls. I've written up my research as a blog here http//digitalinvestigation.wordpress.com/2012/05/04/the-forensic-implications-of-sqlites-write-ahead-log/

Hope that's useful.

 
Posted : 04/05/2012 3:19 pm
(@mykulh)
Posts: 11
Active Member
 

Excellent work Alex, very interesting.
Just wondering though, if the database and the WAL files were separated (for example if only the db had been extracted from an image) without a checkpoint being reached would the database fail to open or revert to the original and now out of date data?

Best regards
Mike

 
Posted : 04/05/2012 10:32 pm
(@alexc)
Posts: 301
Reputable Member
Topic starter
 

Glad you enjoyed it, I mention that situation towards the end

One behaviour which hasn’t been described in full so far is that a database file in WAL mode isolated from its associated “-wal” file is, in almost all circumstances, a valid database in its own right. For example, consider the test database above as it is at the end of Step 8. If the database file was moved to another directory, as far as the SQLite database engine is concerned this is a complete database file. If this isolated database file was queried, the data returned will be that which was present at the last checkpoint (in our test case, this would be the 3 live records present at the checkpoint performed in step 7).

This raises an important consideration when working with a SQLite database contained in a disk image or other container (eg. a TAR archive) if the database file is extracted from the image or container without its associated WAL files, the data can be out-of-date or incomplete. The other side of the coin is that the “full up-to-date” version of the data (viewed with the WAL present) may lack records present in the isolated database file because of deletions pending a checkpoint. There is, then, an argument for examining databases both ways complete with WAL files and isolated as it may be possible to obtain deleted records “for free”.

So to answer the question - it should open but it'd behave as it was at the last checkpoint.

 
Posted : 05/05/2012 3:51 am
(@mykulh)
Posts: 11
Active Member
 

Sorry Alex, must read and re-read before posting my random thoughts )

So I guess if you have a WAL file it might make sense to view the db with and without it to see what data is altered, added or removed.

Cheers
Mike

 
Posted : 16/05/2012 6:05 pm
(@alexc)
Posts: 301
Reputable Member
Topic starter
 

Exactly - if there have been deletions in the database (which are then represented as alterations in the WAL) viewing without the "-wal" file present might get you some deleted data "for free". It's just important to understand why that is, in case it doesn't tally with what you see if you view the data on, for example, a mobile phone or in a virtual environment.

 
Posted : 16/05/2012 8:50 pm
Share: