Monday, February 21, 2011

Recovering Data from Deleted SQL records

I previously posted about parsing iPhone SMS database.  The particular focus was the recovery of deleted messages.  I explained there are really two types of deleted messages in play here: records flagged as deleted within the database (thus not really deleted at all) and records deleted from the database itself.  I discuss the second type of deleted data recovery here.

A SMS message deleted by the iPhone user is flagged in the database as "deleted." What happens next is not clear to me because I don't currently have an iPhone with which to experiment and I am not a sqlite expert.  I am uncertain if the database immediately deletes the record or if that occurs on sync (there are a couple of database "triggers" I don't yet fully understand).  If the data is only flagged deleted, then the record can be read with sqlite tools, which is what I discussing in my previous post.

But at some point a record can be deleted from the database, and as a result, it is not viewable with sqlite tools. So how do we find that data, and more importantly, how do we distinguish it from the non-deleted data?  It helps, at this point, to understand what happens to deleted records in sqlite.  When you delete a record, the space allocated to the record gets added to a free-list.  In other words, the size of the database doesn't get any smaller with record removal, but the space is marked as available for future records. This remains true until the database is "vacuumed."

A database can have it's free space removed with the conveniently named "vacuum" command.  This rebuilds the entire database, removing the space in the free-list and shrinking the database.  Sqlite can be compiled to do this automatically, but fortunately for us, this is not currently the case for the sqlite compilation in iOS.  We can use the vacuum command to help differentiate the data from the deleted records and non-deleted records, however.

The method I used was simple and would apply to any sqlite database, not just the iPhone sms.db.

  1. Make a copy of the sms.db: 'cp sms.db sms.vac.db'
  2. Vacuum the database with: 'sqlite3 sms.vac.db vacuum'
  3. Examine the difference between the vacuumed file and the original file: 'diff sms.db sms.vac.db'
There are obvious shortcomings with such a method.  The foremost problem is that the data is unstructured, and this causes interpretation difficulties.  However, there is no other method of which I know that will produce structured data.  And unstructured data can still be useful in an investigation, if only to verify a statement or corroborate another piece of data.

I am aware of one attempt at forensic recovery of deleted sqlite records.  It is specific to the Firefox browser history.  For more information, take a look here.