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.


  1. hi am completely new to sqlite i opened the sms.db file but i dont know where and how to perform tje commands lusted above. every time i di it i keep on getting a syntax error can u help me please?

  2. It would be far too much to try to guide you in sqlite syntax through a blog. You can find a nice tutorial at

  3. Hi specifically use the above 3 sql commands, I am using SQLite Database Browser. I then go into the 'EXECUTE SQL' tab and enter 'cp sms.db sms.vac.db' in the 'SQL STRING' field. I then click on 'EXECUTE QUERY'.....but nothing happens. The Browser appears to stall/crash. Does this command take a long time to work or am I doing something wrong? Again, I am a novice. Thanks

  4. Those commands were meant to be run from the Linux command line: cp = copy.

    It would be too much for me to try to teach sqlite in comment boxes. Please refer to the link in my previous comment and check out for the sqlite command line program for your platform.

  5. Was able to recover some text from a DELETED SMS using this method, running iOS 6.0.1.

    - Pull the database out of a backup (must have a backup or access to the phone's direct database). In the iOS6 backup, it is SHA hashed (comes out to a file name of "3d0d7e5fb2ce288813306e4d4636395e047a3d28" in your backup).
    The default backup location is /Users//Library/Application Support/MobileSync/Backup/

    - Make a copy of the database, as suggested, then run the sqlite3 vacuum command on the COPY.

    - I then pulled up the FileMerge tool (comes with Xcode) and compared the two files. It may complain that the files are not ASCII, but do it anyway. Even with my i7 processor, 8GB RAM, and SSD drive, this took SEVERAL minutes to compare. But I was able to search the resulting text output for the text I had mistakenly deleted.


Time Perspective

Telling time in forensic computing can be complicated. User interfaces hide the complexity, usually displaying time stamps in a human reada...