In the first part of our series on SQLite analysis we talked about accessing corrupted SQLite files and recovering deleted SQLite records from the freelist. Today we will cover two more important topics: SQLite write-ahead log (WAL) and unallocated space of SQLite databases, and how to analyze them using Belkasoft Evidence Center.
Write-ahead logs, or WAL, work in an opposite way to freelists. While the freelist contains deleted SQLite records, the write-ahead log is used by the SQLite engine to store pages not yet committed into main database.
Belkasoft Evidence Center is an integrated forensic product with advanced out-of-the-box analysis of SQLite databases. Unlike many other tools on the market, Evidence Center automatically parses freelists and write-ahead logs, merging found records together with data from the main database.
How much information can a write-ahead log contain? Usually, that would be a few hundred records, and it is an awful lot if we are talking, for example, about instant messengers. So what exactly is write-ahead log, and why does SQLite use it?
Rollback journals provided a robust and reliable way of safeguarding information. Unfortunately, their use required a lot of extra read and write operations, causing significant slowdowns on heavy load. Since the release of SQLite 3.7.0 back in 2010, the database engine no longer uses rollback journals. A new commit and rollback method called write-ahead log ("WAL") was introduced.
Write-ahead logs no longer back up information from the main database. Instead, the new commit scheme uses a temporary database file to write new records to, only merging the temp file with the main database on commit. In essence, write-ahead logs work in the opposite way to rollback journals. Where the rollback journal saved a copy of the original database content into a separate file and then wrote new data directly into the database file, WAL preserves the original content in the main database while writing new data into a separate WAL file. WAL was found to be significantly faster in most scenarios compared to the old journaling mechanism, providing better concurrency and optimizing disk I/O operations.
The two files (the main database and the write-ahead log) are merged when committed. The commit event occurs when a certain size of the write-ahead log is reached, or if a manual commit event is received. Typically, SQLite automatically commits after the WAL reaches the size of 1000 records. Until then, the database reads new records from the WAL file. Does that ring a bell?
Indeed, when analyzing a SQLite database, one can access up to a thousand new records by parsing the WAL file, and read all the old records from the main database file. General-purpose SQLite tools don’t normally offer the choice, either parsing the main database only or (more commonly) automatically merging the content of the WAL file with the main database, thus overwriting old records. Neither approach is good for digital forensics.
Remember how many records a write-ahead log may contain? By default, SQLite commits a checkpoint when the WAL file reaches a threshold size of 1000 pages. A thousand records is an awful lot in the context of chatting or casual Web browsing. A typical chat session never triggers the commit checkpoint, leaving all sent and received messages uncommitted and stored in the WAL file.
In some cases, cleaning up a database or deleting records does not remove entries from the write-ahead log. This results in the most recent records (up to 1000 in typical conditions) being available for analysis.
Similar to disk space allocated by the file system, unallocated space in SQLite can be just empty. However, it may contain deleted data or remnants of previously used pages. In other words, unallocated space is constituted from page fragments that contain random pieces of data.
Analyzing unallocated space in SQLite databases is not easy. Since unallocated space does not contain valid data or pointers and is not referenced from the page index, data stored in unallocated areas is difficult to extract and almost impossible to reconstruct into something meaningful. You may find that examining unallocated space can be difficult and time-consuming. Even if you are able to locate a fragment, you will not be able to tell which page used to contain it. Recover the broken relations is also not possible.
It is important to note that absolutely no general-purpose SQLite tools using standard access methods and high-level API’s can access unallocated space or extract data from these areas. You will need a forensic-grade product such as Belkasoft Evidence Center in order to discover unallocated areas inside a SQLite database, view and extract information.
Why should you bother analyzing unallocated space? These free, unallocated areas may contain bits and pieces of data deleted by the user long time ago.
Belkasoft Evidence Center is one of very few products that allow you to examine unallocated space of SQLite databases. The product extracts data located in unallocated space completely automatically. After that, you can use the built-in Hex Viewer for thorough manual examination, or you can simply open the built-in SQLite Viewer and select the "Unallocated space" tab.
Notably, Belkasoft Evidence Center can carve unallocated SQLite space for hundreds of supported artifacts. If anything found, you can review "Carved data from unallocated space" tab for the findings (see screenshot below).
This feature makes work with SQLite databases in Evidence Center even more convenient: instead of spending hours looking through chunks of binary data in Hex Viewer, you can just open the tab with carved data from unallocated space in SQLite Viewer and review it – sorted out by columns, formatted and laid out cleanly. Besides, SQLite Viewer allows you to create reports directly from within its interface, so that, when you find what you were looking for, you can export the findings immediately:
Belkasoft Evidence Center is able to recognize hundreds of applications that use SQLite, extracting and displaying mobile apps data, browser histories, smses, messages, call logs or chat logs discovered in current, deleted, uncommitted and unreferenced database records.
