Analyzing SMS.db question

Hi Daft,

Not sure what version of SMS.db you are looking at nor exactly which OS it may have been recovered from.

Have a look at the message_pieces table. If there is any data here you have MMS in the database also, the column marked message_id should relate to the ROWID in the message table. This may account for some of the issues you are seeing.

The deleted data in the database is unlikely to be shown as the reference to it will have been removed. If the number is missing have you tried scrolling to the right to see if there is a madrid_handle present? The method of message exchange could be via an iMessage using an email address to identify the recipient/sender or the number could be present in this column.

If you are interested in recovering deleted data there are various tools out there which can do this. We have developed a tool at CCL called Epilog which can do this.

http//www.ccl-forensics.com/Software/epilog-from-ccl-forensics.html

It always feels like a sales pitch so apologies. x

If this doesn't answer your questions is there anything else you can tell us about the iOS version or tables contained within the database?

Colin

If you have a MAC you can use a open source software called isms. This will recover the entries from the database file.

if you're trying to parse out an iphone sms database you can use the script found on this blog http//linuxsleuthing.blogspot.com.au/2011/02/parsing-iphone-sms-database.html

I haven't used epilog but I've heard good things about it

Apparently sqlite browser doesnt show you deleted sms', whereas epilog does
otherwise there's always physical analyser by cellebrite if you can afford it

I figured I would resurrect an old thread rather than create a new one.

I've been dealing with quite a few iPhone's lately belonging to very active imessage users dealing with 10s of thousands of messages. I've been tasked with reporting on a few individual chat threads that deal with quite a bit of deleted information. I'm finding that with both Cellebrite & XRY are reporting on deleted messages where the opposite handset's phone number is missing from the report. When you break the chats up in a timeline format, you can make sense of it, as long as the user of the phone isn't chatting with multiple people at a time.

So I dig into the sms.db file, and what I'm guessing is happening, is the link between the thread ID, and the individual messages are lost. (perhaps connected via the chat_message_join table?)

Does the DB shred this info when the message is deleted? More and more iPhones are coming in like this, making it very difficult to report on imessage chats.

I have recently released SQLite Forensic Toolkit that should be able to help with your problem. One of the tools, SQlite Forensic Explorer, breaks down each B-Tree and shows you exactly what is stored, where it is stored and how it is encoded.

The screenshot below shows the root node and two leaf nodes sqlite_master table. With one of the leaf nodes (pages) displayed. Within this you can see there are 4 records (offset 8 ) in this B-Tree leaf and one of the strings at offset 592 is selected and highlighted.

Uisng SQlite Forensic Explorer you should be able to see exactly what data is in the node/record.

More information here http//sandersonforensics.com/forum/content.php?194-SQLite-Forensic-Explorer and a short article I have just written here http//sandersonforensics.com/forum/content.php?196-SQLite-Forensic-Explorer-overview

Hope this helps.

I figured I would resurrect an old thread rather than create a new one.

I've been dealing with quite a few iPhone's lately belonging to very active imessage users dealing with 10s of thousands of messages. I've been tasked with reporting on a few individual chat threads that deal with quite a bit of deleted information. I'm finding that with both Cellebrite & XRY are reporting on deleted messages where the opposite handset's phone number is missing from the report. When you break the chats up in a timeline format, you can make sense of it, as long as the user of the phone isn't chatting with multiple people at a time.

So I dig into the sms.db file, and what I'm guessing is happening, is the link between the thread ID, and the individual messages are lost. (perhaps connected via the chat_message_join table?)

Does the DB shred this info when the message is deleted? More and more iPhones are coming in like this, making it very difficult to report on imessage chats.

The SMS database on iOS is now "done properly", in that it's pretty well normalised, unfortunately, that causes some additional issues when you're recovering the deleted data.

The "normal" way to find participants is from the message via the chat_message_join, then on to the chat_handle_join and finally to the handle. This is, despite the complexity a fairly good idea from a database design standpoint - it allows a nice, normalised way to have multiple people in a conversation and reduces duplication of data. Unfortunately, those relationships are deleted by triggers in the database when the related message is deleted. From a recovery of deleted data point of view, the chances of getting all of those relationships intact is kinda low, and the very small, generic looking records in the two "join" tables are nightmarish to recover without getting lots of false hits.

Luckily there's still also a 2 table one-to-many relationship with most messages between the "handle_id" column in the "message" table and the "ROWID" column in the "handle" table - if you have, or can recover the corresponding records in the "handle" table you can link messages back to a handle - I don't think XRY and Cellebrite do this though.

If you have a tool to recover deleted records you could rebuild the database and query it directly. I haven't yet used Paul's tool, but it does look to have some pretty neat features and it may well do the job. We use Epilog to do this work here and it's a fairly routine task (full disclosure - I wrote Epilog, but we have found it does work well for this particular job).