Advanced SQLite Analytics with Belkasoft Evidence Center (Part II)

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: Access to Non-Committed Data

From our original article on SQLite forensics you may already know that straightforward analysis of a SQLite file rarely shows the complete picture. Indeed, free and open-source SQLite tools rarely (if ever) deal with freelists. Yet another thing they do not normally deal with is write-ahead logs.

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.

Evidence Center’s SQLite Viewer is a convenient and powerful built-in tool for thorough examination of SQLite databases

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?

SQLite Journaling and Write-Ahead Logs

SQLite is a transaction-based database. Historically, SQLite used rollback journals, the atomic commit and rollback mechanism to guard against potential write errors. Rollback journals worked by saving old copies of pages being overwritten with new data into a separate journal file. SQLite removed the journal file if the write operation was concluded successfully. If an error occurred, the engine would roll back the original page from the journal file, returning the database to original state by merging data from rollback journals into the main file.

Skype databases: main.db (8 572 KB) and main.db-journal (1 588 KB). Using regular tool, you can lose up to 20% of records! Belkasoft Evidence Center will automatically merge data from both files to a single list

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.

Accessing Write-Ahead Logs and Rollback Journals with Belkasoft Evidence Center

Belkasoft Evidence Center natively supports both the old journaling method and the newer write-ahead logs. When opening a SQLite database, Belkasoft Evidence Center will automatically look for rollback journal and write-ahead log files. If either file is discovered, Belkasoft Evidence Center will parse both the main database file as well as the temporary rollback and write-ahead files. As a result, you will be able to see both the old (historic) copy of a page as well as the new (uncommitted) copy of the same page stored in the write ahead log (or vice versa if an older version of SQLite with rollback journals is used). Uncommitted records are then highlighted with a different color:

SQLite Viewer allows you to see the full picture by reviewing both committed and uncommitted records (the latter are highlighted with blue)

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.

SQLite Unallocated Space Analysis

When it comes to storing information in a file, SQLite features a fairly complex structure. As many other databases, SQLite breaks information stored in a file into pages. Inside these pages there are smaller chunks of information called cells. Due to the way SQLite allocates space, new cells are normally placed towards the end of the page. Preceding cells that have not yet been used constitute unallocated areas.

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).

Belkasoft Evidence Center can recover information from unallocated SQLite space

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:

Create report from SQLite Viewer in just a few clicks

Belkasoft Evidence Center allows you customize your report by choosing format (PDF, CSV, XML, and others), adjusting logo, fonts, table size and contents, number of pages, etc:

Reports created with Belkasoft Evidence Center are accepted in courts

Belkasoft Evidence Center: Advanced SQLite Analysis at Your Fingertips

Belkasoft Evidence Center implements the lowest-level approach to handling SQLite databases. When it comes to SQLite evidence, Belkasoft Evidence Center is an all-in-one digital forensic tool, and is as close to a one-button solution as at all possible in the complex world of digital forensics. With Belkasoft Evidence Center, you can carve the disk, forensic disk image, or a memory dump for SQLite databases, automatically extract and analyze information from all available sources including freelists, rollback journals and write ahead logs. The built-in SQLite Viewer and Hex Viewer, as well as some of other advanced features, allow you to perform low level examination and, for instance, can help discover evidence that is still available in unallocated areas of the SQLite database.


Belkasoft Evidence Center is perfectly equipped to handle existing, emptied, deleted or corrupted SQLite databases

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.

Interested in SQLite analysis? Get your free evaluation license of Belkasoft Evidence Center at http://belkasoft.com/trial!

Leave a Comment