Wednesday, February 2, 2011

Parsing the iPhone SMS Database

I was asked recently to help recover deleted messages from an iPhone SMS database.  Conveniently, this is called "sms.db" on the iPhone and it is located in the /mobile/Library/SMS/ directory.  It is a sqlite3 file type, and there are several GUI tools to read sqlite3 databases, as well as a sqlite3 command line shell.

It seems like a pretty straight forward exercise at first blush.  However, "How do I recover deleted messages from an iPhone database" is not as simple as question as it first seems.  When a user chooses to delete a message on the iPhone, the record is flagged as deleted in the database, but the record is not deleted from the database.  This means that records flagged as deleted are still recoverable with sqlite3 tools.

But when the phone is synced with iTunes, the records flagged "deleted" are actually removed (deleted) from the database itself and no longer recoverable with sqlite3 tools.  However, the data is still within the database, but the space it occupies is added to a "free list" for use by new data.  In other words, the data can still be recovered before it is overwritten by new data or the database is "vacuumed", a sqlite3 process that rebuilds the database removing all the free space and reducing the size of the database.  As a caveat, sqlite3 can be configured to overwrite records immediately upon deletion, but this is not the case for the iPhone at present.

So really, there are two types of deleted data to be sought: records flagged as deleted (not really deleted records at all), and records deleted from the database.  I'll discuss the first type in the remainder of this discussion.  I'll consider ways to recover deleted records in another post.

The sms.db has the following tables:
_SqliteDatabaseProperties, msg_group, group_member, msg_pieces, message

The message table contains the text messages of interest.  The contents of the table can be displayed from the shell with:
$ sqlite3 -header sms.db "select * from message"
ROWID|address|date|text|flags|replace|svc_center|group_id|association_id|height|UIFlags|version|subject|country|headers|recipients|read
1|+17132619725|1281033415|Hey, what's up?|2|0||1|0|0|4|0||us|||1
...
As you can see the interesting fields (revealed because the -header argument was used) are "ROWID," "address" (which is the source phone number), "date" (in unix epoch format), "text," and of less obvious value--"flags."  Flags indicates the type of message, i.e.,:
  • 2 = received
  • 3 = sent
  • 33 = Message send failure
  • 129 = deleted
  • (source: Adam Crosby)
With a little sqlite razzle dazzle, we can get a well formatted output in a more human readable form:
$ sqlite3 -header sms.db "select ROWID as row, case flags when 2 then 'rcvd' when 3 then 'sent' when 33 then 'fail' when 129 then '*del' else 'unkn' end as type, address as phone_no, datetime(date,'unixepoch','localtime') as date, text as message from message"
row|type|phone_no|date|text
1|rcvd|+17132619725|2010-08-05 11:36:55|Hey, what's up?
...
SQL syntax can be a bit tricky, and look a bit intimidating.  But by using internal commands, you can get a tremendous speed boost over using external text tools.  By way of explanation, I'll break down the command:

  1. sqlite3 -header sms.db  #open the database with sqlite and display column names 
  2. "select  #display the following columns from the table
  3. ROWID as row,  #display the ROWID column first, but rename it "row" in the output
  4. case flags when 2 then 'rcvd' when 3 then 'sent' when 33 then 'fail' when 129 then '*del' else 'unkn' end as type,  #display the flags column next, but change the "2" flag to "rcvd", "3" to "sent", etc., and rename the row "type"
  5. address as phone_no,  #display the address column renamed as "phone_no"
  6. datetime(date,'unixepoch','localtime') as date,  #convert the content of the date field to local time and display the column as "date"
  7. text as message  #display the text column renamed as "message"
  8. from message"  #all the columns of data are read from the message table
The command can be simplified if column renaming isn't required.  I included it here to make the output as clear as possible, and since the command can be incorporated into a script, it need only be typed once.  The quoted part of the command could be inserted into a GUI sqlite browser if that is your tool of preference.  The query can be adjusted to show just deleted messages, for example, by appending "where type like '*del'".

Notice I did not link the data in the message table to any other tables in the database.  While this can be done, my task was to seek out deleted messages.  And as I said earlier, I will explore methods for recovering deleted records from sqlite databases in a future post.

As always I welcome any comments or suggestions....


39 comments:

  1. not showing deleted records or records tagged for deletion i should say.. I have not used anything to sync since accidentally deleting a couple texts.. what am i doing wrong??

    ReplyDelete
  2. I'm sorry Bryan, I can't possibly tell you because you haven't told me what you've done. How did you gain access the sms.db? With what tools are you looking at the db? Did you look in the flags column for flag 129?

    ReplyDelete
  3. I SSH'ed into my phone, copied the sms.db over to my MAC.. Opened up Terminal and ran the command you show in your post.. Nothing comes up as "del". Every entry shows either "rcvd" or "sent" when using the above command. I've also used RazorSQL, SQLite Studio and SQLite browser to search for these "deleted" texts and I'm not showing anything that's flagged to be deleted. Looks like they actually were removed somehow. I never did a sync to my phone. I actually never closed the sms.app on my phone either(kept it running in the backround) hoping it would stay in memory possibly. When using any SQL utilitiy, I did not run any commands of any kind. Simply was browsing the sms.db file to try and find these tagged records, but no luck..

    ReplyDelete
  4. Actually, one thought just came to mind. I received a new text message from the same recipient I accidentally deleted messages from AFTER the accidental deletion took place. Do you think that would remove the old records and create a new one for some reason??

    ReplyDelete
  5. So here's the latest. I'm using a trial version of RazorSQL to open my sms.db file on my mac. The file opens in the editor and if I do a search for the phone number related to the deleted batch of texts, I can find these messages. However it's only in the editor, so it's not really easy to read. So this is telling me that at least some of the messages are still there. At this point, I'm searching to find a way to find the flags that are telling the db to delete these messages.. What app can I use for this?? If i open the file with SQLite DB Browser, I'm getting a much more manageable output, but it seems as though the deleted texts might be hidden?? As they do not show up here at all. Is there a way to unhide these records and then change the flag to sent or rcvd?? I've been searching all over the place and can't seem to find anything, even on general DB's, not specific to the iphone DB's. Any help or input you might have would be greatly appreciated.. Thank you very much..

    ReplyDelete
  6. It's impossible for me to know what is your specific issue without looking at the data. A few thoughts, though:

    1) If you know some of the content of the messages you are seeking, first verify they are still in the db, either as a record or in free space, with a strings search. You may be looking for something that simply isn't there.

    2) The sms.db may vary from one iOS version to the next. If you study the schema of the db, you may get an idea of what happens to a message when it is flagged as deleted by the user. Hint: look at the triggers, which cause an action in the db based on the occurrence of another action.

    3) The "rcvd", "sent", "*del", etc., are changes I made to the standard output to make it more readable. If you are looking at the sqlite db with a viewer like Sqlite Browser, you won't see these flags. Look for "129" in the flag column of the message table instead.

    4) If the db has a trigger that removes the record AND that trigger activated since you deleted the message, you will not find in in the sms.db with any of the methods you tried, including the method I describe in this article. You will need to attempt the method I describe in part 2 of this article, i.e., copy the db, vacuum, and examine the difference between the files.

    ReplyDelete
  7. Thanks again for your response.. I have my sms.db file extracted from my phone and sitting on my desktop. If i open the file with a standard text editor, I can search for and find the deleted text messages. If i open that same sms.db file with an SQL browser, those deleted texts do not show up in any type of search thru the database. So I'm thinking that when opening with an SQL browser/editor, they are hidden since they are tagged for deletion. Is there a command to unhide these records?? I've been unable to find a good source of information on commands in SQL and don't know the app well at all.. Should i be able to find these, is there a way to go about unhiding them permanently and then removing the tags to have them deleted so that they show back up on the phone..

    ReplyDelete
  8. Brian, I'm sorry, but the sqlite data you seek is not hidden from view. The records are simply no longer in the database, but are in the "free" space created after the records were removed (deleted). I explain in part 2 of this article and in my previous response how to locate deleted records, which essentially you have done. I'm afraid there is currently no way to restore the records.

    ReplyDelete
  9. Hi!

    I need some help!

    I accidentally deleted a contact's sms before sync it with iTunes
    The deleted contact's sms contains about 50 sms
    I used iFunBox to extract sms.db from my iPhone 4
    I opened sms.db using Windows Notepad
    I saw all the sms that is still on my iPhone 4 appearing on sms.db with Windows Notepad
    I also saw the oldest 3 of the 50 sms from the deleted contact on sms.db with Windows Notepad together with some unreadable characters
    The 50 sms are deleted at the same time because I click on delete contact
    I'm using a jailbreaked iPhone 4 with firmware version 4.0.1
    Is the rest of the about 47 sms recoverable?
    I don't need to recover the sms back to my iPhone 4, I just need to know the contents of the sms where I can view it on my PC
    I did not sync my iPhone 4 after I deleted the contact's sms

    ReplyDelete
  10. yukina, the only help I can offer you is covered in another post that you can find here: http://linuxsleuthing.blogspot.com/2011/02/recovering-data-from-deleted-sql.html. Since you are using Windows, to follow the procedures described in the post will require you to install a 'nix environment in windows like Cygwin (http://www.cygwin.com/). Once you install cygwin, be sure to install the sqlite3 module.

    ReplyDelete
  11. Thanks for the response

    I installed Cygwin and sqlite3 module.

    I also followed the guide from http://linuxsleuthing.blogspot.com/2011/02/recovering-data-from-deleted-sql.html and tried doing the steps.

    Make a copy of the sms.db: 'cp sms.db sms.vac.db'
    Vacuum the database with: 'sqlite3 sms.vac.db vacuum'
    Examine the difference between the vacuumed file and the original file: 'diff sms.db sms.vac.db'

    My sms.db is on my Desktop.

    But I receive error message from Cygwin "No such file or directory".

    ReplyDelete
  12. yukina, when you first launch cygwin, your working directory is not the Windows desktop. You need to move the db to the working directory, or change the working directory to the location of your db. If you don't know your way around the Linux command line, go to http://linuxcommand.org/ for a tutorial.

    Alternatively, if you know the Windows command line, you can install sqlite for Windows to vacuum the database, but I don't know of a 'diff' command in Windows that will help you accomplish the rest.

    ReplyDelete
  13. Thanks again for the help.

    I managed to get the first command 'cp sms.db sms.vac.db' working after reading the Linux tutorial but I can't get the second command, 'sqlite3 sms.vac.db vacuum' to work though.

    ReplyDelete
  14. yukina, the command will complete and exit silently if no errors. In Linux, success is generally demonstrated by a return to the command prompt without an error.

    Your next step will be to 'diff sms.db sms.vac.db' which will print the difference of the two files to the screen. Redirect the output to a file with 'diff sms.db sms.vac.db > result.txt'. This method does not let you restore the deleted records to the db, just determine their content in a very coarse manner.

    ReplyDelete
  15. Error, 'bash: sqlite3: command not found' occurs when I enter the 2nd command, 'sqlite3 sms.vac.db vacuum'

    ReplyDelete
  16. yukina, it appears to me that you don't have sqlite3 installed. You can try 'which sqlite3' to verify. If installed, you'll receive output like '/usr/bin/sqlite3'. Otherwise, you'll get a longer response that indicates 'which: no sqlite3 in () where is your Cygwin and Windows paths.

    ReplyDelete
  17. Ya, I receive a longer response which I think I did not installed properly. I tried installing the sqlite3 module again but it does not work either. Is it possible for me to send you my sms.db for you to do the parsing of the iphone sms database for me?

    ReplyDelete
  18. Sorry yukina, I don't think I can do that. My goal with blog is to document my discovery's for future reference and help others learn to perform the same actions.

    I understand that learning new commands in a new operating system can be difficult and confusing. I turned to Linux about two years ago and experience the same difficulties. All I can suggest is to keep plugging away until you can understand the issues and solve them. The rewards are immense!

    However,since you have a one-time recovery issue, you might try epilog, a forensic recovery tool for sqlite databases. The trial version is fully functional for 7 days. You'll find it at http://www.ccl-forensics.com/Software/epilog-from-ccl-forensics.html.

    Good luck.

    ReplyDelete
  19. hi,
    I really need some help!
    i've accidently deleted a contact's sms!And i don't have any backup!!I have found the file sms.db and opened it with the program TextPad!I found some of the sms!!Would you please tell me how to restore them to my jailbreaked iphone 3G and how to rearrange them so that i can view them in order.I have another question : i am going to format my iphone and they told me that all the sms present are going to be deleted!can i restore them to the iphone after formating it?
    I really thank you so much in advance for your answer that i really need!

    ReplyDelete
  20. So I went through all the steps and they all work, but when I sent it to the .txt file, all that pops up is "Files sms.db and sms.vac.db differ"

    What did I do wrong?

    ReplyDelete
  21. Hi Jackie, I assume you are talking about trying to recover deleted records, which is the subject of a different post. Without knowing your specific commands or Linux distro and version, I'm afraid I can't help you other than to say this: you should start with reading the diff command for your distro: 'man diff' or 'diff --help'. It could be that you need to pass an argument to display the difference between the original sms.db and the vacuumed sms.db.

    ReplyDelete
  22. Thanks for your articles about data recovery on the iPhone.

    I have created a backup of an iPhone 3Gs on a Mac and then copied the files to my Ubuntu laptop.

    I am not seeing a file called sms.db. I have many sqlite databases with hex names and files called Manifest.mbdb, Manifest.mbdx, Manifest.plist, Status.plist, info.plist.

    I tried to open the Manifest files on the Mac using the sqlite GUI program you mentioned. It said the db was encrypted. I entered the itunes password but it did not open anything and did not give an error.

    I think I am lost. Did I not get the sms.db file in the backup I made from iTunes?

    I was not successful at backing up or getting to root of the phone in Linux, I keep getting errors from idevicebackup and cannot seem to get idevicebackup2 installed properly from package. When I try to install from source I can't get one pesky python library to create my makefile.

    So, can you tell me if I have the sms messages already? If not, can you point me in the right direction so I can make another attempt?

    ReplyDelete
  23. Hi Brandon, sorry for the delayed response (your message was picked up by the spam filter).

    The original filenames are replaced by 40 character hexadecimal strings during the iTunes backup process. The Manifest.mbdb and Manifest.mbdx files are not sqlite files, which is why you get the sqlite error. The Manifest* files can be read in tandem to determine the original path, MAC times, file size, file name, etc.

    I have a tool called 'iphone_backup' at http://code.google.com/p/linuxsleuthing/ to do that if you have python2.7 installed (you do if you are running Ubuntu). If you are not familiar with running python, you might want to try something else.

    A great project for backing up and restoring an iPhone is libimobiledevice (http://www.libimobiledevice.org/) which is installed in Ubuntu. However, the version you will want is the latest 'master' of the development version which you will find at http://cgit.sukimashita.com/libimobiledevice.git/.

    If you are looking for the sms.db SQLite database only, then the fastest thing to do is change directories to the backup, and run 'grep -l message * | while read i; do sqlite3 $i .tables 2>/dev/null | grep -q message; [ $? = 0 ] && echo $i; done'. That will show you the hexadecimal name for sms.db.

    I'll try to blog soon about how to make and examine a iPhone backup in Linux.

    ReplyDelete
  24. HEY
    thanks for your post, am on windows 7 I downloaded razorblade my file is on my desktop, however I dont know alot of things on SQLITE whenever I copy paste the command 'sqlite3 sms.vac.db vacuum'I keep on getting a message saying syntax error near SQlite, I really need to find the deleted sms messages can I send you my sms.db file so you can have a go at it?I can leave you my email address andreasmariou@hotmail.com

    ReplyDelete
  25. It would be far too much to try to guide you in sqlite syntax through a blog. You can find a nice tutorial at http://www.w3schools.com/sql/default.asp.

    ReplyDelete
  26. hi i'm not really familiar with all these technicalities but can i possibly restore sms from my iphone4 after already synching? really need them badly

    ReplyDelete
  27. I'm currently working on a personal (maybe it will turn public) project where I upload my database file and parse it with php into a mysql database where I can search a view my messages.

    Looking through the database there seems to be a lot of things added to support iMessages. I'm no database expert but the way iMessages were added to the database seem very poorly thought out.

    Anyway, I was wondering if you have any information about what some of the "madrid_" columns of the database represent and what their values mean. Thanks!

    ReplyDelete
  28. I've just got my first copy of an iOS5 sms.db. I do not yet know the meaning of the madrid tables or columns in the messages table. Sorry. If I learn something, I'll post it.

    ReplyDelete
  29. For the iMessage flags (Madrid etc) take a look here: https://github.com/toffer/iphone-sms-backup/blob/master/sms-backup.py

    ReplyDelete
  30. This may help with iOS5 SMS databases. Enjoy.

    --This will get info from message table and format it. Includes iMessage for iOS 5. Does not get group messages.
    select ROWID as Row, case flags when 0 then case when madrid_date_read>0 then 'Received' else 'Sent' end when 2 then 'Received' when 3 then 'Sent' when 33 then 'fail' when 129 then '*del' else 'Unknown' end as Type, case when address then address when group_id=0 then madrid_handle else group_id end as 'Phone_No/GroupID', case when date<978307200 then datetime(date + 978307200,'unixepoch','utc') else datetime(date,'unixepoch','utc') end as 'Date', case is_madrid when 0 then 'SMS/MMS' when '1' then 'iMessage' end as MsgType, text as Message, case madrid_flags when 36869 then 'Sent from iPhone to 1 person' when 102405 then 'Sent to 1 person (contains email, phone, or url)' when 32773 then 'Sent from iPhone to Group' when 98309 then 'Sent to Group (contains email, phone, or url)' when 12289 then 'Received by iPhone' when 77825 then 'Received (text contains email, phone, or url)' end as 'iMsg Flags', recipients as recipients from message order by Date asc

    ReplyDelete
    Replies
    1. There is one inconsistency in the query that needs to be corrected:

      "...case when madrid_date_read>0 then 'Received' else 'Sent' end when 2 then 'Received' when 3 then 'Sent' when 33 then 'fail' when 129 then '*del' else 'Unknown' end as Type..."

      should read:
      "...case when madrid_date_delivered>0 then 'Sent' else 'Received' end when 2 then 'Received' when 3 then 'Sent' when 33 then 'fail' when 129 then '*del' else 'Unknown' end as Type..."

      This is because the original case statement conflicts with the madrid_flags. The message can be "read" by sender later after it was "sent" leading to madrid_date_read having a value greater than 0 though the madrid_flag indicates sent.

      Delete
    2. I meant to add, however, great job on the query. I've modeled it many times in my own exams.

      Delete
  31. This comment has been removed by the author.

    ReplyDelete
  32. What program do I use to open iOS5 sms.db on windows?
    Ive tried with sqlite3explorer and its just empty.

    ReplyDelete
    Replies
    1. The sms.db in iOS5 is still SQLite. Any SQlite browser should work. If your database was empty, they you likely had a export problem with the database.

      Delete
  33. I'm a fellow forensic examiner looking at this phenomenon. Specifically on iOS 4.2.1. At this point I'm trying to determine the following:

    1). Is the iTunes sync process the ONLY trigger that will cause the vacuum of the SMS.db database? If not, what other criteria can trigger this vacuum command to remove "flag deleted" rows from the database?

    2). Is there ever a situation where the SMS.db can be vacuumed WITHOUT syncing or attaching at all to iTunes. Basically, does iTunes alone dictate/cause database cleanup actions?

    3). Will an iTunes backup cause a vacuuming of the SMS.db from the device?

    4). Does an iTunes manual backup operation really sync first then backup, thus causing the database vacuum?

    5). Does a software update potentially kick off the vacuum operation?

    I will be testing a bunch of this today, but wanted to see if you had any thoughts on that.

    ReplyDelete
  34. Hi Rick, excellent questions but I'm afraid I can't satisfactorily answer any of them. You'll just have to study the phone and its interaction with iTunes using known test data. I don't have an iPhone myself so I cannot do so. One general principal to keep in mind with SQLite: Even if triggers cause 'automatic' changes to the db on certain events, nothing stops an external application from issuing a SQLite command, like vacuum or otherwise making changes to the db, so long as the application has access to the db.

    ReplyDelete
  35. I don't know if the sms.db will be recreated if you delete it. I suspect that it will, based on how SQLite works. If you are concerned, you can also use SQLite to drop records in bulk, say, from a particular recipient or before a certain date, rather than delete the whole database. Keep in mind that since iOS 5, the sms.db has a different construct, and sms messages through the Apple service use Mac Absolute Time where sms messages through your cellular carrier use Unix Epoch time.

    ReplyDelete
  36. Thank you very much for your post, I could retrieve my SMS change datetime value into localtime in very short time and perfectly! :)

    ReplyDelete

Time Perspective

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