Identifying deleted records in DB and WAL

by | Mar 23, 2016 | Forensic Toolkit for SQLite

I have been working with a user who is processing an Android mmssms.db with its associated WAL journal and it became clear that another forensic tool that he was using was not identifying deleted records. My colleague wanted to create a report showing just the deleted records from the SMS table for a specific phone number.

First a little back ground regarding Write Ahead Log (WAL) journals.

When a new record is to be written to an SQLite database and WAL journalling is in operation the SQLite engine identifies which page of the database needs to be updated with the new record and writes a new copy of that page to the WAL file (with the new record) but leaves the old page in the DB untouched. If subsequently another record is to be written to the same page then another copy of the page (with the new record and the previous new record) will be appended to the WAL file.

The same happens if a record is deleted – the page that needs to be updated (with the deleted record) is appended to the WAL and the DB left unchanged.

If you have been following all that you will realise that you can get multiple copies of a page in the WAL file and therefore multiple copies of the records in a page.

The Forensic Browser can process a DB along with the WAL file and will identify which of all of these records is the current “live” record but will still display all of the other records, because, as forensic investigators we need to see everything. If the record is not the actual live record that would be displayed by a normal SQLite browser, then it is marked as False in the sfIsLive column.

If you have really been following this you will realise that if a record is written to the WAL and then subsequently deleted you will get both copies of the page (one with the live record and one without it – i.e with it deleted) in the WAL file. The recovered deleted record of course will not be marked as live, because it is not.

The task therefore is to identify all the records for the user (last four digits of phone number = 9937) that have been deleted. the process I used was as follows.

Open the Forensic Browser for SQLite and create a case file – we will be creating some views later and these will be saved to the case file.

Open the DB, the associated WAL file and choose to recover records.

Choose to process all of the frames in the WAL

When the tables have all loaded we run our first query to select all of the records from the SMS table that belong to the user we are interested in. the query is

Code:

SELECT *
FROM sms
WHERE sms.ADDRESS = '+xxxxxxxx9937'
To make our final query simpler we now need to create a view based on this query. This is done from the queries menu and when prompted we give the view a name – I called it “all_9937”.

The query above can now be replaced by :
Code:

SELECT *
FROM all_9937
The next step is to create a similar view but only on the live records for this particular phone number. The Browser records which records are live and which are not by using ‘True’ or ‘False’ as appropriate in the sfisLive column. So the query to identify live records is simply

Code:

SELECT *
FROM sms
WHERE sms.ADDRESS = '+xxxxxxxx9937' and sms.sfisLive = 'True'
We create a view on this query and call it “live_9937”

The final task is to create a correlated subquery to return the difference between the two tables, this sounds more complex than it is and the query is simply:

Code:

SELECT *
FROM ALL_9937
WHERE ALL_9937._ID NOT IN (SELECT LIVE_9937._ID FROM LIVE_9937)
This in English simply says select all records from 9337 that are not present in the live records from 9937.

The final results looked like this

The views can be replaced by the original queries but this makes the SQL a little harder to follow:

Code:

SELECT *
FROM sms
WHERE sms.ADDRESS = '+xxxxxxx9937' AND sms._ID NOT IN
(SELECT sms._ID FROM sms WHERE sms.ADDRESS = '+xxxxxxx9937' AND sms.sfIsLive = 'True')