Forensic analysis of SQLite Write-Ahead Log (WAL)

SQLite databases are a common source of digital evidence during forensic examinations both mobile and desktop operating systems. Beginning with version 3.7.0, a new “Write-Ahead Log” or WAL feature was introduced and replaced rollback journal. In this article we’ll present a brief overview of this SQLite feature and show how to examine WAL-files with open source tools.
 

Write-Ahead Log overview

So, what’s the WAL file’s purpose?  it implements atomic commit and rollback. Write-ahead logs allow the database engine not to touch the main database file for writing  a new or altered page, instead a separate WAL-file is used. This file can be found in the same directory as the database file and also has the same name as the database, but with 4 character added to the end – “-wal” (Fig. 1).

Figure 1. A WAL-file extracted from a jailbroken iPhone 

The new data remains in the WAL-file until committed by the “Checkpoint” event. This event occurs automatically after the WAL-file reaches a certain size – by default it’s 1000 pages.

As you can see, it’s a digital forensic goldmine – just imagine, a thousand records, especially when we are talking about instant messaging or web browsing data.

There is another file on figure 1 – an SHM-file or Shared-Memory File. These files are used as indexes for the WAL files and contain no persistent content. The only purpose of such files is to provide a block of shared memory for use by multiple processes all accessing the same database in WAL mode.

Extracting data from WAL-files

There are a lot of commercial forensic suites capable of extracting data from Write-Ahead Log files. For example, Oxygen Forensic and Belkasoft Evidence Center can accomplish this task with ease and style. Let’s see how to perform a forensic analysis of a WAL-file with Belkasoft Evidence Center.

Create a new case and choose “Selected folder” as the source – we’ll be examining a folder with three files, presented earlier (fig 2.).

Figure 2. Selecting the source folder

We know that our database is an iOS SMS database, so we tell BEC to parse only this data (fig. 3).

Figure 3. Selecting iOS Sms to be parsed

Now let’s go to the SQLite viewer. There are 12771 records in the “message” table, 159 of which are colored in blue – these are record, extracted from the WAL-file. Now just check these item and create the report right clicking on the table and choosing “Create report for checked items…”.

Yes, it’s that simple if you use a commercial forensic suite like Belkasoft Evidence Center.

But what about an open source alternative? We’ve found one for you! It’s developed by Kyeongsik Lee and called “Walitean”. You can download this WAL journal forensic toolkit here.

Here is how to use it:

 
python walitean.py -f [WAL FILE] -x [EXPORT FILENAME(SQLITE DB)]
 
As a result you’ll get a database file with all the data, contained in the WAL-file which can be examined with your favorite SQLite viewer.
 
About the authors:

Igor Mikhaylov

Interests: Computer, Cell Phone & Chip-Off Forensics

Oleg Skulkin

Interests: iOS forensics, Android forensics, Mac OS X forensics, Windows forensics, Linux forensics