When iOS 11 came out, there was a new column that
was introduced into the sms.db.
This really messed up all the parsing from the tools.
So what happens is every September,
Apple releases a new iOS version.
It tends to correlate to when I'm teaching
at network security in Vegas.
And students always have expectations
that I will have the answers to that iOS version
the second it's released.
But I try to do that.
So iOS 11 is released.
As the students are doing their labs,
I decide I'm going to do simple things on my iPhone.
I always do create a new contact, place some calls,
place FaceTime calls, send regular text messages,
send iMessages, search for maps, browse the internet,
take the picture, dump my device.
That is my typical methodology.
With iOS 11, when I loaded the messages,
I was like, what is happening?
It's not showing me.
I thought the data was encrypted.
And I panicked a little bit.
But then I realized, when I went to sms.db,
there was just a new column added.
Now, what's crazy is you're going
to see these conversation start dates in this query--
if you were already friends with someone or chatting with them
prior to iOS 11, a new column was created,
and it put an 18-digit Mac timestamp, instead of
nine-digit.
If you started a new conversation,
it went into a different column.
So depending on your friendship with the person,
the tools were losing their minds.
Because think about this-- when we click on that arrow--
and as we have been, when we see a database,
we click on that arrow, it gives us one option.
It's like, hey, this is what I can do for you.
If there's two different ways to decode that, it becomes a mess.
So what I did, with the help of Lee, was write a query.
And I remember this was a Friday night,
and included a lot of bourbon and frustration.
And finally, I called Lee.
I'm like, I can't do the math.
I have no idea what's going on.
So here is your query.
And it still works through iOS 15.
It's massive.
So if you look at this, it starts--
all the messages-- it will work on every single version.
Now, the joins at the bottom--
we have not talked about joins yet--
they're a lot of work.
What I found in SMS is there are tables
that link to tables that then point to other tables.
You can modify this as you want, but it tells you
if a message is incoming or outgoing, read or not read.
You could add case statements to it.
You can do anything you want.
If you prefer a script, what I ask
is that you please be kind to me.
I did write a script.
And you can see iOS message parser.
I also include the old versions in the readme.
I tell you to please forgive me that it's not beautiful
if you run this in Windows.
So if you run my script on Windows,
you will not see emojis.
It's definitely an encoding situation.
If you run it on Linux or the SANS SIFT workstation,
you will see emojis.
Emojis matter in many investigations.
So if you run my query, you'll see them,
but it's an encoding thing in the script.
However, most of the tools are parsing it now.
A good example of a tool that doesn't is iBackupBot.
So if you want to see what it looks like,
how it's all messed up, look at this data in iBackupBot.