With each iOS incarnation, key databases change structure. This is no secret to anyone who examines data from iDevices. The iOS4 sms.db differs greatly from the iOS5 sms.db, and both differ significantly from the new iOS6 sms.db. This is expected, and no heartburn here at all.
But last month I was slapped in the face by Apple in an unexpected way: I found two different versions of the sms.db from the same version of iOS! This is unexpected, and highlights why me must not take our tools for granted and assume our output in this case is accurate because of a tool test we conducted in a previous case.
The Quandry
- iPhone #1: Product type: iPhone 4,1; Product Version 5.1.1
- iPhone #2: Product type: iPhone 4,1; Product Version 5.1.1
So, for all intents and purposes, I was dealing with the same phone and operating system.
Take a look at the sms.db message table schemas:
iPhone #1
CREATE TABLE message (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT, date INTEGER, text TEXT, flags INTEGER, replace INTEGER, svc_center TEXT, group_id INTEGER, association_id INTEGER, height INTEGER, UIFlags INTEGER, version INTEGER, subject TEXT, country TEXT, headers BLOB, recipients BLOB, read INTEGER, madrid_attributedBody BLOB, madrid_handle TEXT, madrid_version INTEGER, madrid_guid TEXT, madrid_type INTEGER, madrid_roomname TEXT, madrid_service TEXT, madrid_account TEXT, madrid_flags INTEGER, madrid_attachmentInfo BLOB, madrid_url TEXT, madrid_error INTEGER, is_madrid INTEGER, madrid_date_read INTEGER, madrid_date_delivered INTEGER, madrid_account_guid TEXT);
iPhone #2
CREATE TABLE message (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT, date INTEGER, text TEXT, flags INTEGER, replace INTEGER, svc_center TEXT, group_id INTEGER, association_id INTEGER, height INTEGER, UIFlags INTEGER, version INTEGER, subject TEXT, country TEXT, headers BLOB, recipients BLOB, read INTEGER, madrid_attributedBody BLOB, madrid_handle TEXT, madrid_version INTEGER, madrid_guid TEXT, madrid_type INTEGER, madrid_roomname TEXT, madrid_service TEXT, madrid_account TEXT, madrid_account_guid TEXT, madrid_flags INTEGER, madrid_attachmentInfo BLOB, madrid_url TEXT, madrid_error INTEGER, is_madrid INTEGER, madrid_date_read INTEGER, madrid_date_delivered INTEGER);
Do you see it? I didn't initially, because I tried to automate extracting the text messages from iPhone #1 with a python program I had previously authored. When it failed, I was very confused because I had just used the program on iPhone #2 similar device days earlier. And frankly, it didn't dawn on me to immediately check the schema while seeking the error source, which is the purpose of this post: saving you some of my pain.
Finding the Worms
iPhone #1
(CREATE TABLE message (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT, date INTEGER, text TEXT, flags INTEGER, replace INTEGER, svc_center TEXT, group_id INTEGER, association_id INTEGER, height INTEGER, UIFlags INTEGER, version INTEGER, subject TEXT, country TEXT, headers BLOB, recipients BLOB, read INTEGER, madrid_attributedBody BLOB, madrid_handle TEXT, madrid_version INTEGER, madrid_guid TEXT, madrid_type INTEGER, madrid_roomname TEXT, madrid_service TEXT, madrid_account TEXT, madrid_flags INTEGER, madrid_attachmentInfo BLOB, madrid_url TEXT, madrid_error INTEGER, is_madrid INTEGER, madrid_date_read INTEGER, madrid_date_delivered INTEGER, madrid_account_guid TEXT);
iPhone #2
CREATE TABLE message (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT, date INTEGER, text TEXT, flags INTEGER, replace INTEGER, svc_center TEXT, group_id INTEGER, association_id INTEGER, height INTEGER, UIFlags INTEGER, version INTEGER, subject TEXT, country TEXT, headers BLOB, recipients BLOB, read INTEGER, madrid_attributedBody BLOB, madrid_handle TEXT, madrid_version INTEGER, madrid_guid TEXT, madrid_type INTEGER, madrid_roomname TEXT, madrid_service TEXT, madrid_account TEXT, madrid_account_guid TEXT, madrid_flags INTEGER, madrid_attachmentInfo BLOB, madrid_url TEXT, madrid_error INTEGER, is_madrid INTEGER, madrid_date_read INTEGER, madrid_date_delivered INTEGER);
Ok, you say, "I see the highlights, but they are the same content. What's the big deal?" I concur, they do say the same thing... but the "madrid_account_guid" is in a different position in the database, or to be more linguistically correct, the field order is different in the two databases! Does it matter? Well, yes, and no...
Consider:
'SELECT ROWID, address, text, madrid_date_read FROM message;'This query would work equally well in either phone's message table because it calls the fields by name. Any application, forensic or otherwise, making specific queries would continue to operate completely oblivious of the database differences. But a more generic query, could lead to trouble.
'SELECT * FROM message;'This query would output every field in the record, and any tool that tried to read data output positionally would get unexpected data in the last eight fields (in one case, anyway). This is what happened in my program. "Well, stupid," you say, "don't code like that." Again, I concur, and I fixed my program by changing the manner in which I queried the database... but it turns out I'm not the only one coding this way.
What I failed to mention was why I was processing these phones. iPhone #1 was part of a shooting investigation. I retrieved from a suspect vehicle and initially processed it by making an iTunes backup of the running device. The second phone was brought to me after an up-to-date Cellebrite UFED failed to extract ANY message from the iMessage Service. The 'madrid' fields relate to the iMessage service, and it is the madrid fields that are thrown out of order by the database schema change. Seems that Cellebrite may have been thrown by the flag order in the same way I was. At least I'm in good company!
This also has implications in SQLite record carving. In the raw data, fields are laid down in the order of the schema. If a template for carving fields from dropped SQLite records has the wrong schema (and why would you expect one iOS 5.1.1 sms.db to differ from another), then you are getting incorrect and unreliable data.
I have been working quite hard on recovering dropped records from SQLite pages and have been quite successful. Stay tuned for what I've learned on this front...
No comments:
Post a Comment