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.
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
WHERE sms.ADDRESS = '+xxxxxxxx9937'
The query above can now be replaced by :
WHERE sms.ADDRESS = '+xxxxxxxx9937' and sms.sfisLive = 'True'
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:
WHERE ALL_9937._ID NOT IN (SELECT LIVE_9937._ID FROM LIVE_9937)
The final results looked like this
WHERE sms.ADDRESS = '+xxxxxxx9937' AND sms._ID NOT IN
(SELECT sms._ID FROM sms WHERE sms.ADDRESS = '+xxxxxxx9937' AND sms.sfIsLive = 'True')