This is an interesting little nugget I recently came across, in what could be considered a bit of a follow up to the Missing SQL Records paper I wrote with Shafik earlier this year.
I was contacted by someone trying to understand more about what could be learned from the missing records in the sms.db database which is what led here. It certainly isn't something that will be useful on every case, but one day you may be glad you know it.
I'm sure everyone has seen the Reactions that can be sent in response to iOS messages by now. A feature present in many other apps, but missing from iOS messaging for a long time up until around iOS10.
|An example of a Reaction
I'm also confident that anyone who's looked at the sms.db will know that the Reactions are saved in the table as it's own record. Just like if it was a message that was sent and includes all account/recipient/timestamp information etc.
For all intents and purposes, the record may as well have been the user literally sending a messages that says "I Love the message"Yeah"."
Here for example, you can see that the last message (Row 95) is a LOVE reaction to the message "Yeah" that can be found in Row 90.
So far so good. Here's where things get a little more interesting.
What if the original message (the one at Row 90) is deleted? Well, let's try it.
As expected, Row 90 is gone. But Row 95, the Reaction, remains. And as it contains the original message, we can see what was said, even if we don't know when or who actually sent it (although we may be able to figure that out using other sources of data).
It also doesn't seem to matter how long the message is, it's still quoted.
You may notice above though, that "Loved a Movie" is a little less informative. Which movie is it referring to?
Well, things get better. Further along the list of fields for message 95 we find "associated_message_guid":
Does that GUID look familiar? It should. It's the GUID of the original message that got deleted (The one at Row 90).
And, it gets EVEN BETTER.
GUID's appear consistent across devices. The Sender and Receiver both have the message with the save GUID.
|Comparing GUIDs between the sender and receiver devices
So what does this all mean?
Well, lets create a hypothetical situation where Device 1 and Device 2 are communicating with Messages and Reactions.
Messages are subsequently deleted from Device 2 which is then examined. We can use the gaps in ROWIDs to identify where messages should be even if we don't know anything about the message.
We can also use the Reaction messages that remain to fill in some blanks as they still contain useful information.
This could be important for identifying if messages have been selectively deleted from the device being examined. Such as may be the case where the owner is trying to control the narrative.
I have previously had a case where the owner of the device being examined had selectively deleted all messages that cast them in a bad light but kept all the replies.
Who knows when and if Apple will realise this mistake. But for now, try to make the most of it.