Forensic Analysis of SQLite Databases: Free Lists, Write Ahead Log, Unallocated Space and Carving
SQLite is a widely popular database format that is used extensively pretty much everywhere. Both iOS and Android employ SQLite as a storage format of choice, with built-in and third-party applications relying on SQLite to keep their data. A wide range of desktop and mobile Web browsers (Chrome, Firefox) and instant messaging applications use SQLite, which includes newer versions of Skype (the older versions don’t work anyway without a forced upgrade), WhatsApp, iMessages, and many other messengers.
Forensic analysis of SQLite databases is often concluded by simply opening a database file in one or another database viewer. One common drawback of using a free or commercially available database viewer for examining SQLite databases is the inherent inability of such viewers to access and display recently deleted (erased) as well as recently added (but not yet committed) records. In this article, we’ll examine the forensic implications of three features of the SQLite database engine: Free Lists, Write Ahead Log and Unallocated Space.
SQLite Recovery and Analysis Tool
For the purpose of this article, we’ll be using Belkasoft Evidence Center to illustrate the low-level approach to handle SQLite databases. Belkasoft Evidence Center is an all-in-one digital forensic tool to help investigators reliably carve the disk or disk image for SQLite databases, extract and analyze information from all available sources including freelists, rollback journals and write ahead logs. The tool can discover and display data stored in unallocated areas via built-in SQLite Viewer or HexViewer.
Belkasoft Evidence Center is perfectly equipped to handle existing, emptied, deleted or corrupted SQLite databases
The powerful carving algorithms can identify and extract known types of data (such as browser history or mobile app data among many others) from existing, deleted and unreferenced database records. You may request a free evaluation copy at https://belkasoft.com/trial.
Freelists: Access to Deleted Entries and Cleared Databases
Before we begin, let’s have a brief look at how SQLite stores and manages data. Regular records are stored in a file called the “Database Image”. The database image is broken up into “pages” of a fixed size. The size of each page is specified in the file header. Each page may have one of the many roles assigned by SQLite. One of these roles is storing the actual data. Each page has a number assigned by the SQLite engine. Page numbering starts with 1.