|For corporate or law enforcement investigators a fully functional licence for the Forensic Toolkit for SQLite can be obtained by clicking here and providing your official email address, full name and position within your organisation|
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
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 :
SELECT * FROM all_9937
SELECT * FROM sms WHERE sms.ADDRESS = '+xxxxxxxx9937' and sms.sfisLive = 'True'
The final task is to create a correlated sub query to return the difference between the two tables, this sounds more complex than it is and the query is simply:
SELECT * FROM ALL_9937 WHERE ALL_9937._ID NOT IN (SELECT LIVE_9937._ID FROM LIVE_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:
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')