By James McGee, Digital Forensic Examiner, Metadata Forensics, LLC—Specially for Belkasoft
Forensics Question: When sent or received iMessage/SMS/MMS messages are permanently deleted from Apple iPhone's native Messages Application, rows of data are removed from the sms.db—the storage database for the application. When these messages have been removed and data is no longer available, even in the sms.db-wal, how can an examiner/investigator identify these messages and where can they look for this potentially valuable content?
Tools
DB Browser for SQLite Version 3.12.2
Background
The Messages Application has been around for some time and is the built-in system application to send messages on Apple devices. Support for sending and receiving MMS messages was introduced in iOS 3, while support for sending and receiving iMessages was introduced in iOS 5. iMessage/SMS/MMS message content for the Messages Application are stored within the sms.db. The sms.db, sms.db-shm, and sms.db-wal all have a file source location of /var/mobile/Library/SMS. These files can be obtained through an iTunes backup, Logical Extraction, Full File System Extraction, etc.
Apple allows three retention periods for Messages content: 30 days, 1 year, or Forever. The default for Messages content is Forever; however, the user of the device can change the retention period through Settings > Messages > Keep Messages. The Messages retention period is important to know as an examiner/investigator because it provides a foundation for the timeline of data likely to be present on the device. If it is claimed a conversation took place within Messages and the retention period is set to Forever then the data should be present on the device, unless a user driven action has taken place. Namely, a user-device interaction where messages were deleted from the device.
Somewhat recently, with iOS 16, messages deleted by the user were still present within the sms.db and available through the Messages Application "Recently Deleted" option. Messages have three options once tagged for deletion by the user: the message can be selected and permanently deleted, the message can be selected and recovered for view within the Messages Application, or messages are deleted after 30 days. For the purposes of this article, our interest is with permanently deleted messages no longer available for review within the sms.db but possessing timestamps within the set retention period.
This article will explore data remaining within the sms.db following user-driven message deletion, another data source typically reviewed for Message Application data, and the current remaining locations for deleted Message Application messages.
Deleting Content from the sms.db
The focus of our analysis of the sms.db will be within the "message" table of the database. This table has 59 columns of data and the number of rows align with the number of messages still present for review in the database. When viewing the "message" table of the database, you can see messages are numbered within the "ROWID" column, which begins at 1 and increases with each sent or received message. Major message content highlights are present within additional columns in the table: the message content itself within the "text" column, an identifier for additional chat participants within the "handle_id" column, a boolean value for sent or received messages within the "is_from_me" column, and a timestamp of messages within the "date" column. Take the following example conversation, parsed with a simple query similar to that used by forensic software.
Note, the ROWID value was not added into the query, on par with typical parsing
by forensic software. This image and subsequent images are depicted through DB Browser
for SQLite.
Review of these messages appears to display a full conversation. Unfortunately, when a message is deleted the entire row of data is removed and the only evidence of sent or received messages ever existing is the skipped value within the "ROWID" column. "ROWID" values begin at 1, increase uniformly by one value with each sent or received message, do not skip values, and do not duplicate values. This information, for our analysis, is incredibly valuable as it provides the framework for our SQL Query to retrieve the data needed from the sms.db. After all, scanning a database that can range from 1—100,000+ rows of data can be taxing, and is inefficient for any case load.
The key to the SQL Query is the Lag function. The Lag function provides the ability to compare rows of data with previous rows of data, set off of a specific column name. The Lag function—based off our "ROWID" Column—gives us the following, initial, SQL Query:
In this query we are telling DB Browser to select all data from the "message" table, ordered by "ROWID", where "ROWID" minus the alias value "Previous ROWID" is greater than 1, and where "Previous ROWID" is established as a lag of "ROWID" by one. This SQL Query against our test sms.db provides the following data return:
Here we see the first evidence of missing rows, or messages, from the sms.db! This return states ROWIDs 9, 10, 11, 12, and 13 are missing from the messages table, along with all other data contained within those rows. But like most SQL query starting points, we can expand the query for additional information:
Adding three rows to our query greatly expands the usefulness of the return. Here, we added line four which subtracts the "ROWID" value by our Lag function of "ROWID" and an additional value to obtain the total number of missing rows. (An additional value is required to be subtracted as well because 14 - 8 = 6; however, the number of rows between 14 and 8 is instead 5.) Lastly, we duplicate our Lag function once more in query rows 5 and 6, this time structured around our "message" table "date" column.
Altogether, this query provides the following data return:
Our query is now complete and we have added another metric as uniform as the sms.db "ROWID" values but much more universally significant: Time.
With time added the data return displays at a glance that "ROWID" values 9, 10, 11, 12, and 13 were timebound between 2023-02-21 01:47:06 and 2023-02-21 01:48:44. This knowledge provides right and left time limits to search within our data for the possible missing messages. What still remains, now removed from the sms.db? Chat participants, timestamps, actual message content? Well, if you know where to look and your extraction was completed in a timely manner there is hope to recover these values.
Searching for Deleted Message Content
While the sms.db can be obtained through an iTunes Backup all the way up to a Full File System Extraction, the path for recovering deleted message content from the sms.db requires a Full File System Extraction.
interactionC Database: Typically, my go-to for Apple Native Application interactions is the interactionC database, which is available even in an encrypted iTunes Backup. The file source location for this database is /private/var/mobile/Library/CoreDuet/People/InteractionC.db and the database contains all pertinent data for incoming and outgoing Messages Application communications, except the actual message content itself. Sadly, within my testing, it was found that the deletion of messages from the Messages Application also removed data rows from the interactionC database.
Biome AppIntents: Since stumbling upon what I have come to call "Biome" or the "Biome Directory" back in August, 2021, the Biome location has been my starting point for all deleted Messages Application content. While there have been expansions into the file structure of Biome, the location has been present since iOS 14. The file source location for our files of focus, files possessing Message Application content, is \private\var\mobile\Library\Biome\ Streams\public\AppIntent\local. The propagation of this data is dependent on user settings and whether Siri has been provided permissions to learn from the Messages Application within Settings > Siri & Search > Messages > Learn from this App. This setting is on by default when Siri has been set-up on a device.
Siri and the ability to learn from an application is ultimately tied to Siri Suggestions, a quick option interface available on the home screen by selecting the center of the screen and pulling downward until Siri Suggestions displays. Through past testing, it appears the applications highlighted within Siri Suggestions can vary from application usage or time of day; namely, typically after using Messages the user next uses the Phone Application or between 9:00 PM and 10:00 PM the user typically uses the Clock Application, possible setting an alarm for the morning. The likely unforeseen aspect of this feature is that message content of iMessage/ SMS/MMS communications are also stored within the file structure of the Biome directory.
Removing the data from the native Messages applications does not impact the data within the Biome directory as the data is written during the initial iMessage/SMS/MMS action and stored by the device until the Biome directory files are overwritten after about 28 days. It is my believe this short time span of 28 days is a purposeful decision on Apple's part to provide the user the best device experience and most useful Siri Suggestions: what you have done recently is more pertinent than your routine six months ago, but I cannot confirm this concretely. Additional information is available within my past article published by DFIR Review.
Exploring the \private\var\mobile\Library\Biome\streams\public\AppIntent\local location will reveal a number of 1MB files, if data is present. These files contain bplist data containing, most relevant to this article, iMessage/SMS/MMS data from the Messages Application but other Application Intents are present as well. Manual parsing of this data is covered in-depth in my previous work; however, happily there are now both commercial and open-source forensic software options which parse this data. I still recommend the manual review of these files for validation of software parsing when significant artifacts are present within Biome.
Remember our query from earlier, identifying the missing "ROWID" values and timestamps? Finding this data within the Biome files allows us to add the data back in for review, providing the following:
Now our full conversation has been recovered, reviewed, and could prove significant when tied with other areas of the investigation, or could provide the probable cause to seize Steve's device for examination as well.
KnowledgeC Database: Historically, the KnowledgeC database is known for storing a wealth of data, to include but not limited to: Application Activity, Application Focus, Device Battery Percentage, Device Lock State, and Device Orientation. The file source location for the database is /private/var/mobile/Library/CoreDuet/Knowledge/KnowledgeC.db.
A somewhat new update is that, in simple terms, a duplication of the Message Application iMessage/SMS/MMS content stored in Biome is also stored within binary plists (bplists) inside blobs within the KnowledgeC.db. Basically, the same message content as stored within Biome is duplicated within the KnowledgeC database. A manual review of this data is time intensive but possible using a database viewer such as DB Browser. Commercial forensic software parses this data currently and open-source forensic software has the same capability, just requiring the export of blobs storing bplists from the database. The biggest benefit of iMessage/SMS/MMS content storage within the KnowledgeC.db is that the retention period within the database is longer on average than that of the Biome. The exact retention period is variable with each dataset so a definite timeline cannot be provided.
Conclusion
The Messages Application has long been the Apple default for sending and receiving iMessage/SMS/MMS data, stored within the sms.db. While this database is capable of retaining data forever, device settings can be changed and the user of the device is freely able to delete message content. Knowing where to recover iMessage/SMS/MMS data from an Apple device could be the make or break of an investigation. This article explored databases known for storing additional data for present messages as well as the remaining locations for finding and recovering deleted message content.
Sources/References
- "Messages." The iPhone Wiki, 13 Oct 2015, https://www.theiphonewiki.com/wiki/Messages
- McGee, J. (2022). An Alternate Location for Deleted SMS/iMessage Data in Apple Devices. DFIR Review. Retrieved from https://dfir.pubpub.org/pub/yp6efc8q