Interpreting Dates in the sms.db
BlackLight parses out dates and times of iMessages and SMS messages, showing each message’s date sent, and perhaps date read and/or date delivered, depending on the devices and settings involved.
But how does BlackLight interpret these dates?
Let’s look at how to find dates and times of messages on iOS devices, and what it would take to manually pinpoint what BlackLight automatically locates and organizes for the examiner. We will first discuss the database where message data is stored and then follow with some easy-to-understand examples of dates that can be found in that data.
On iOS devices, message data is stored in the SQLite database /mobile/Library/SMS/sms.db. Note that although the file is named sms.db, SMS messages and iMessages are both stored in this database.
Here’s an example of an iMessage sent from an iPhone:
We select the iMessage in BlackLight’s ‘Content Pane,’ then select the Preview tab in the ‘File Content Viewer’ to view the contents of the sms.db.
Inside the database, we select the ‘message’ table. Upon scrolling to the right we see dates expressed in a WebKit epoch. The ‘date’ value represents the date and time the message was sent.
Selecting the value in the ‘date’ column, one sees it is a WebKit date expressed in UTC. We need only take a quick glance at the ‘Data Interpreter’ in the lower right of the BlackLight window to see that the WebKit date stands out as a plausible date.
On the iPhone or iPad, the date and time for a message are determined after being converted using the time zone offset, which can be accessed and changed on the device at: Settings > General > Date and Time.
In the sms.db, the ‘date_read’ value for an iMessage is also a WebKit date expressed in UTC. This date is either:
1. For outgoing messages, the date and time the other person (i.e., the recipient) read the message, if the recipient’s iOS device had the Send Read Receipts option enabled, which is accessed at: Settings > Messages > Send Read Receipts;
2. For incoming messages, the date the user first opened the message. On a received message the value is changed from unread to read when the user opens the Messages app and, if not already selected, selects the conversation that contains the unread message. Again, this value is UTC and based on the settings of the user’s phone.
The ‘date_delivered’ date is received back from the recipient’s phone and is another WebKit date expressed in UTC.
Let’s examine a message in Hex. We see that each message starts with the Message ID. Dates are found near the end of the message, and represented in Big Endian. Here’s the date this message was sent.
The sent time of 455739079 we saw above in the ‘date’ column is 0x1B2A06C7 in Hex.
Once this value is converted to Big Endian in the ‘Data Interpreter,’ we see the date in WebKit as: 06/11/2015 18:11:19 UTC.
The ‘date_ read’ value of 455815258 is 0x1B2B305A in Hex.
The value is Big Endian, and it’s 06/12/2015 15:20:58 UTC.
Lastly, the ‘date_delivered’ from above, 455815258, is 0x1B2B305A in Hex.
Once again the value is Big Endian and is 06/12/2015 15:20:58 UTC.
Unlike iMessages, SMS messages include a ‘date’ sent (for all messages) and ‘date_read’ (for incoming messages only) but no ‘date_delivered.’ This lack of a ‘date_delivered’ value is because an SMS message does not use data — only cell signals.
Here’s an SMS example in the SQLite database:
The ‘date’ for the highlighted message, 436357175, is WebKit in UTC from the local device.
In the Hex view, this value is expressed as 0x1A024837, which when converted in Big Endian is: 10/30/2014 10:19:35 UTC.
As previously noted, with SMS messages a ‘date_read’ value only appears for incoming messages. In this example, 436928307 is expressed in Hex as 0x1A0AFF33, and when converted to Big Endian is: 11/06/2014 00:58:27 UTC.
With either an SMS message or iMessage, BlackLight handily parses dates sent and, where applicable, dates read and/or delivered. While these dates are properly organized for easy viewing in the ‘Content Pane’ (by selecting the ‘Communication’ view and the ‘Messages’ subview), examiners can also utilize BlackLight to see where it is getting the message dates it parses.
If you have additional questions about BlackLight or interpreting dates in the sms.db, don’t hesitate to reach out to BlackBag’s training team.