Exporting text messages from an iPhone

Last week, I was asked to acquire the text messages from an iPhone and to pull out only the messages that were to/from a particular party in a particular date range.  This took a little research to pull off so I'm posting this to share the steps we took.  I hope that this will be useful to others doing forensic investigations or e-discovery.

The first phone we needed to pull messages from was an iPhone.  To start with, we backed up the phone to the user's computer via iTunes.  On Mac OS X, the backups are stored in ~/Library/Application Suppport/MobileSync/Backup/{UDID}.  The individual backup files have no extension and the names of the files are the SHA-1 hashes of the original file path and name from the phone.  In this particular instance, the name of the database containing the SMS messages was 3d0d7e5fb2ce288813306e4d4636395e047a3d28, the same name cited in other articles.  Be careful, however, as this name can change.  If your backup does not contain this file name, a quick grep for 'chat_handle_join' (or any other tell-tale sign) should show you the correct sms.db file.

After locating the correct file, I made a copy, renamed it with a .db extension and opened it in DB Browser for SQLite. There are several tables in the database, the most pertinent are handle and message.  The handle table contains a list of phone numbers and other IDs.

In order to match individual messages with their senders or recipients, you'll need to find the numbers that you're interested in under the id field and note the corresponding ROWID.  For example, phone number (916) 555-1111 has ROWID 2.

In the message table, the most pertinent fields are:

• text: the content of the message
• handle_id: the ROWID in the handle table of the number or ID associated with this message.
• date: when the message was sent
• date_read: when the message was read
• date_delivered: when the message was delivered
• is_from_me: 1 if true.  0 if false.  In other words, 1 means this phone sent it and 0 means it was sent by someone else to this phone.

In the example below, we can see that handle_id 1, which was +19165556789 in the handle table, sent or received a message suggesting "Let's get lunch."  In order to tell if our phone sent or received this message, we would have to scroll to the right to check the is_from_me field.

In order to search either the handle or message tables, we can type in the "Filter" box at the top of any column.  By default, the filtering looks for an exact match.  So, if we type 1 in the handle_id filter box, we'll see only those messages to/from handle_id 1.

For a wildcard search, we can use '%'.

The filtering in DB Browser is good for a quick peek, but I did not see an option to export only select records.  So, I exported everything: File->Export->Table(s) as CSV file...

Then, I opened the resulting CSV file in Excel, filtered by handle_id, and took out several columns.  I also wanted to narrow the results to a particular date range but Excel did not recognize the data from the various columns as a date.  The sms.db file stores each date as the number of seconds since 1/1/2001 according to Greenwich Mean Time.  To convert this to a readable date format, I added a new column called Converted Date and used the formula 

=(L2-28800)/(60*60*24)+DATE(2001,1,1)

L2 is the date field I wanted to convert, substitute as necessary.  The -28800 is necessary to convert the date to my local time (Pacific).  28800 = 3600 * 8.  Simply using 

=L2/(60*60*24)+DATE(2001,1,1) 

will convert to GMT.