Hey guys,
I'm analyzing an iPhone 6 (iOS 9.2.1) with Cellebrite to recover deleted iMessages. After loading the extraction, it was able to locate and parse many deleted iMessages from the sms.db file.
I notice that most deleted iMessages will display the participants number and contact information, however there are some that don't and is instead blank. The iMessages that have a blank participant entry does appear to be apart of the same conversation. My thoughts are that he maybe deleted the contact entry from his phone and now it won't show, but this is speculation.
Any ideas why this conversation may have a blank participant entry?
*Note, these are all iMessages and found from the SMS.db file.
Messages on IOS are a little complex but the information is held in multiple places so you might get lucky.
The message table itself does not contain the contact details of the user that the owner of the phone is chatting with but rather a pointer (actually two pointers) that link to other tables that hold the information.
The first such table is the handle table and a simple join can be made to show the contact info as follows
The second join is slightly more complex and joins the message table to the chat table via an intermediate (pivot) table called helpfully chat_message_join
Both these queries show the same report but the contact info is taken from different places.
I don't know how cellebrite works when matching deleted records across tables (i.e. which of these two joins it does) so I would suggest that you really need to look at the recovered data with an appropriate SQLite database viewer (link to mine below).
Of course as we are discussing deleted data then you need to find records in both tables (or all three tables for the more complex joins) and if records and contacts have been deleted then you again need to be able to recover records all relevant tables to make a match.
The SMS.db uses WAL (Write Ahead Logging) as its journalling mechanism so if a record is deleted then this is first implemented as an update to the appropriate table where the update is written to the WAL and the sms.db file itself is not touched until the WAL is checkpointed (usually after 1000 pages of data have been appended to the WAL). Again I don't know how Cellebrite handles old copies of pages in the WAL (there are often a few copies of pages in various states) so a thorough look with an appropriate tool might be useful.
My software can show you all the recovered records from a WAL and the associated database side by side. I can talk you through anything you find.
Link below - you can request a fully functional demo via a link on this page
http//
I hope this helps?
A bit more information on this.
I had a support call last week from a user who had the same problem and I offered to look further into it for him.
Essentially the problem is that the sms database is very well normalised and when messges are deleted the associated records from related tables are deleted automatically. When these records are deleted although they remain in the DB, SQLite, because of its use of freeblocks, overwrites the first four bytes, including the primary key, of each deleted record and the relationships between them are lost.
All the above is already known - however, I found another relationship from within a blob field with an embedded binary plist, which can, in certain circumstances establish who the third party in a conversation was.
I have written up my findings and an associated solution using the Forensic Browser for SQLite here
http//
Hope you find it interesting.
Hopefully my repeated posting on this thread wont upset anyone )
I have finally had time to write a blog post about the automated deletion process that takes place when a message (or a thread) is deleted from an IOS sms.db database. Essentially a series of foreign key constraints and triggers ensure that the database remains in a consistent state.
The article is a little complex but it shows how a well designed database can operate and will allow those who have managed to recover a deleted message but can't recover the 'handle' of the third party to the message to explain why this is.
http//
Given there have been no responses, here's a quick one to say that this is excellent stuff, thank you for looking so deeply into it. )
Thanks Chris )
It's useful to get feedback - if only to know whether people are interested or not.
Hi Paul,
Just a quick one to say I also appreciate your posts and being a current user of your tools, always appreciate your insights.
Thanks
I get a lot of similar questions around joining deleted records (especially iOS SMS/MMS/iMessage) and Paul's response is excellent. Most tools will attempt to automatically do this for you but when you have a break in the chain, it gets really difficult and may need some more manual analysis with tools like Paul's. Many apps will need 2 or 3 tables to join the required data and thus recovering multiple deleted records becomes less likely to get the complete picture. A single record with all the data you need is usually pretty easy, the more records you need to join together, the more difficult it gets.
Great write up Paul.
Jamie
Thanks Badgerau and mcman
I agree with everyone else. I was going to post a link to your site hoping to shed some light on this subject, but Paul, you went to great detail well beyond anything I could hope to accomplish by posting a link.
I learned a good deal as well. Thanks!
John
