Parsing the iPhone SMS Database (iOS6)

If you combined "How to Access and Read the iPhone SMS Text Message Backup Files" with "Parsing the iPhone SMS Database" in your iOS5 (or older) iPhone you'd have had easy access to your SMS data.

Recently upgrading to iOS6 broke all the queries I used on my database - the sneaky devils look like they've changed the database format.

Nothing stays the same

The most obvious change is that there are no longer non-normalised numbers in the message table; previously you could have '+44 7966 123 456' and '+447966123456' in the address field. This has sensibly been replaced with the id column in the handle table.

As you can see below the tables are castly different after upgrading from iOS5.

The 'handle' table

 $ sqlite3 -header db/sms.db  
 sqlite> SELECT * FROM handle WHERE id LIKE '%68' LIMIT 1;  
 ROWID|id|country|service|uncanonicalized_id  
 266|+4479xxxxxx68|gb|SMS|  

The 'message' table

 sqlite> .mode line  
 sqlite> SELECT * FROM message WHERE text LIKE '%A test message%' LIMIT 1;  
                 ROWID = 1  
                  guid = 5F5A13F4-C382-49F9-8C45-30C3FCA24CCA  
                  text = Chisel here. A test message for when orange get their act together.  
               replace = 0  
        service_center =  
             handle_id = 1  
               subject =  
               country = uk  
        attributedBody =  
                         streamtyped▒▒▒@▒▒▒NSMutableAttributedString  
               version = 1  
                  type = 0  
               service = SMS  
               account =  
          account_guid =  
                 error = 0  
                  date = 271463425  
             date_read = 271463425  
        date_delivered = 271463425  
          is_delivered = 0  
           is_finished = 1  
              is_emote = 0  
            is_from_me = 1  
              is_empty = 0  
            is_delayed = 0  
         is_auto_reply = 0  
           is_prepared = 1  
               is_read = 0  
     is_system_message = 0  
               is_sent = 1  
        has_dd_results = 0  
    is_service_message = 0  
            is_forward = 0  
        was_downgraded = 0  
            is_archive = 0  
 cache_has_attachments = 0  
       cache_roomnames =  
     was_data_detected = 0  
      was_deduplicated = 0
 sqlite>  

Most of the columns appear to be self-explanatory, except for the date fields. Some further searching reveals an existing article about the iOS6 sms.db and that the dates are in fact Mac Absolute time.

Extracting useful data

You can explore the tables and learn much more than I'm writing in this post, but you'll probably want a quick way to extract messages and senders.

The X most recent messages

 sqlite> SELECT h.id, m.text, m.service FROM message m JOIN handle h ON m.handle_id=h.ROWID ORDER BY m.date DESC LIMIT 1;  
      id = +4479xxxxxx68  
    text = Testing No Cellular Data  
 service = SMS  

All messages from +4479xxyyzz58

 sqlite> SELECT h.id, m.text, m.service FROM message m JOIN handle h ON m.handle_id=h.ROWID WHERE h.id='+4479xxyyzz58' ORDER BY m.date ASC LIMIT 1;  
      id = +4479xxyyzz58  
    text = Testing  
 service = SMS  

What services have I received messages with?

 sqlite> .mode column  
 sqlite> SELECT DISTINCT(m.service) FROM message m;  
 service  
 ----------  
 SMS  
 iMessage  

What accounts have I received messages for?

 sqlite> .width 40  
 sqlite> SELECT DISTINCT(m.account) FROM message m;  
 account  
 ----------------------------------------  
   
 p:+4479xxyyzz68  
 e:chisel@xxyyzz.net  
 p:+4479xxyyzz22  
 p:+4479xxyyzz58  

Once I realised that this database had been examined and written about in far more detail than I currently have time for I decided to stop; if you want to read a far more thorough article describing the database, please check out "Who's Texting? The iOS6 sms.db".