First, I would like to thank Heather Mahalik for her help with this process and for allowing me to post something on her blog. It’s an honor! Additionally, thanks to Jared Barnhart for his assistance with research and with testing.
I must apologize if you have already taken the time to read through this blog. It was my first research blog and after it was posted, I felt it was missing a few things. I debated over rewriting it or just following up with an additional blog. I came to the conclusion editing the original and reposting the entire blog was the best method to get you all of the information.
Synopsis: During an examination and analysis, I learned some interesting things and would like to share them with you. After the examination of an Apple iPhone 7, I discovered some photos were captured using the camera application (com.apple.camera.CameraMessagesApp) from within the native iPhone messaging application (com.apple.MobileSMS). As a result of photos being captured, several files were created that I have not observed during my past examinations and I had a few questions.
Was this because I was examining a full file system extraction?
Was it because I haven’t been paying close enough attention during my exams?
Either way, I set out to test and validate what I discovered.
Suspect’s Device: Test Device:
Apple iPhone 7 (A1778) Apple iPhone XS (A1920)
iOS 13.4.1 (17E262) 13.5.1 (17F80)
During testing, I did not find any significant changes between 13.4.1 and 13.5.1 that would make the testing invalid. I did notice when looking an iOS 12.*.* FFS extraction there were some differences.
Important to note: While working on cases after this blog was initially written I noticed there were some difference between iOS 12 and iOS 13. Mainly, iOS 13 devices contained more data in /private/var/mobile/Containers/Data/PluginKitPlugin/<UUID>/tmp/ locations than iOS 12 devices. I only mention this so you are aware there could be additional differences that have not been discussed.
- After First Unlock (AFU) Full File System (FFS) (Suspect’s device and test device).
- Cellebrite UFED 4PC Advanced Logical and Logical extraction (Suspect’s device only).
- Cellebrite UFED 4PC (18.104.22.168)
- Cellebrite Physical Analyzer (PA) (7.35.00.33 – 22.214.171.124)
- Magnet AXIOM (126.96.36.19914)
- Artifact Examiner (188.8.131.52) https://www.doubleblak.com/
- Mushy Plist Viewer (184.108.40.206)
- iLEAPP (1.2) https://github.com/abrignoni/iLEAPP
- APOLLO (1.1) https://github.com/mac4n6/APOLLO
- Zimmerman Hasher (220.127.116.11)
- Navicat for SQLite (15.0.1)
- DB Browser (3.12.0)
While examining the suspect’s device and analyzing the data, I had a few questions about the data being displayed and how it was created. I formulated a few scenarios that might help demonstrate and explain what happened:
Scenario #1 – What happens when a photo is captured (com.apple.camera.CameraMessagesApp) within the native iOS messenger (com.apple.MobileSMS) and sent as an attachment?
Scenario #2 – What happens when a photo is captured from within native iOS messenger, sent as an attachment message and the message that contained the attachment is later deleted from the conversation thread (/private/var/mobile/Library/SMS/Attachments/)?
Scenario #3 – What happens when a photo is captured within native iOS messenger, sent as an attachment message and the photo sent as an attachment is later deleted from the Photos Application (/private/var/mobile/Media/DCIM/)?
Scenario #1 – What happens when a photo / live photo is captured using the camera application within native iOS messenger and sent as an attachment:
During the testing I followed the steps below to capture both photos and live photos:
- Launched the native iOS messenger application (Figure 1.1) and entered a conversation thread (Figure 1.2).
- From the conversation thread clicked the camera icon (Figure 1.3), a photo was captured (Figure 1.4) and clicked done (Figure 1.5). Note: Figure 1.5 this is a preview of the photo that can be sent. But wasn’t this a photo that was captured?? Thanks to Jared Barnhart’s help, I learned this photo is in fact saved to the device even if the user chooses to “Retake” the photo. This photo will be stored in the /private/var/mobile/Containers/Data/PluginKitPlugin/<UUID>/tmp/ folder.
- A preview then appeared (Figure 1.6) and clicked the up arrow to send the photo to its recipient without any text (Figure 1.7).
Now let’s take a look at what happens within the device when these actions occurred.
The application usage and applications in focus was recorded within the KnowledgeC database. There are several resources and published research about the KnowledgeC database and what can be found within it. I would encourage you to take the time to review the list of references and other sources at the end of this blog.
8:21:22 PM – 8:22:54 PM the application (com.apple.MobileSMS) was launched and in use.
During that time:
8:22:01 PM – the back camera was turned on
8:22:03 PM – the application (com.apple.camera.CameraMessagesApp) was launched and several cached locations were created and stored in Cache.sqlite – ZRTCLLOCLATIONMO table. These locations were accurate for where the device was located during testing. If you have additional questions about location data please see Ian Whiffin’s presentation in the list of resources.
8:22:03 PM – Miscellaneous file path locations were opened, modified and created related to \private\var\db\uuidtext\. I haven’t researched or decoded any of these but wanted to mention it.
Figure 1, Figure 2, Figure 2.1 Figure 3 and Figure 4 are examples of how different tools represented the launched applications and what was decoded.
8:22:03 PM – a property list (plist) was created:
In Figure 4 we can see this plist can be viewed within AXIOM and could also be viewed within PA. I saved, exported and opened the plist using Ian Whiffin’s Mushy plist viewer. See Figure 4.1. The plist contained the phone number and the associated UUID’s for the device the attachment message was sent to.
This plist was also in the suspects device and contained a list of phone numbers and their associated UUID’s. There appears to be different property lists for different types of messages sent:
At 8:22:20 PM a live photo of my friend Dexter was captured, which resulted in several files being created on the device to include the following:
Each tool used to examine the test data, put these files in a different order in the timeline, but they all had the same capture and created time of 8:22:20 PM. See Figure 5.
Figure 5.1 within AXIOM – Timeline, there is an event for an entry being made into the Photos.sqlite database. See Figure 5.2 and Figure 5.3 for more details.
In Figure 5.3 we are looking within PA SQLite Viewer and looking at the Photos.sqlite database ZGENERICASSET table. Notice that all of the PK values are in sequential order and there are no missing values, indicating that the list is complete. I just stated when I took a photo of Dexter there were multiple files created to include IMG_0012.MOV. Where is the information about the additional files? More on this is to come in this blog.
Notice in Figure 5.3 there’s indication that an entry was also made in Photos.sqlite ZADDITIONALASSETATTRIBUTES table. Notice AXIOM is indicating that both the Z_PK value and the ZADDITIONALASSETATTRIBUTES value are the same. More about this later in the blog. Got to give a big shout out to Ian Whiffin here. I first noticed these values when viewing the full file system dump from within his tool Artifact Examiner (ArtEx). I reached out to him with a few questions, which he answered right away. It helped so much while examining the suspect’s device…Thanks Ian!! See Figure 6.
In Figure 7 we can see the ZADDITIONALASSETATTRIBUTES table and some key information about IMG_0012.JPG.
I wanted to take a minute and discuss some of the items I discovered while examining at the ZADDITIONALASSETATTRIBUTES table. First notice, that Z_PK entries 1-6 do not have a date in the ZEXIFTIMESTAMPSTRING column. These images were not captured with the test device. They were sent to the test device as MMS from an android device. These photos were then saved to the photos application via the native iOS messenger application. Notice the ZCREATORBUNDLEID indicates com.apple.MobileSMS. See Figure 7.1.
Z_PK entries 7-11 and 17-22 were captured with the test device native camera application, launched from the springboard. Notice there isn’t an entry for ZCREATORBUNDLEID, but there is a value for ZIMPORTEDBY. Using the suspect data and the test data, I believe I’ve decoded some of the values. These are preliminary and require additional testing:
- 0 = Is related to .MOV files.
- 1 = Captured via native Back facing camera
- 2 = Captured via native Front facing camera
- 3 = Third Party Application – Snapchat
- 6 = Third Party Application – Facebook
- 8 = Captured via native Back facing Camera
- 9 = Saved from outside source (SMS, Safari)
Notice there is a binary property list located in the ZREVERSELOCATIONDATA column for each one of these entries. Location services was active for the camera application when the photos were captured. Notice that entries 21 and 22 do not have binary property lists. These photos were captured minutes before the device data was extracted. I believe data did not have time to populate this field prior to the device being acquired. I will discuss the contents of this bplist later in this blog. See Figure 7.2 and Figure 7.3.
Figure 7.2, Figure 7.2.1 and Figure 7.2.2 Screenshots of test device location services settings
Z_PK entries 12-16 were captured via the Camera Application from within iOS Messenger. See Figure 7.4.
Something to notice here…ZORIGINALFILENAME column. All of the photos captured using the CameraMessagesApp have an UUID as the original file name. If you remember when I was discussing the Photos.sqlite – ZGENERICASSET table, these files are not listed in the ZFILENAME column. Another note, only the JPG files that were created are listed. Remember there were several files created as the result of me capturing a live photo of Dexter using the CameraMessagesApp:
Date, Times and Time Zone:
Notice in the ZADDITIONALASSETATTRIBUTES table ZEXIFTIMESTAMPSSTRING column and the ZGENERICASSET table ZDATECREATED column the dates and time values are being stored in different formats. The values in the ZDATECREATED are natively stored as unix epoch and require conversion, but the values in ZEXIFTIMESTAMPSSTRING column are being stored according to the device time settings when the files were captured. In the test device, the date and time settings were set to auto and the time zone was set to Cupertino also known as Pacific Time (UTC -8 or -7…damn DST). There are other columns within the database that record the time zone setting at the time the files are created ZADDITIONALASSETATTRIBUTES table ZTIMEZONEOFFSET, ZINFERREDTIMEZONEOFFSET and ZTIMESONENAME columns.
In ZGENERICASSET table there is a column ZORIENTATION. Using the test device data, I determined 1 = Horizontal and 6 = Vertical. The photos I sent from an android to the test device had both horizontal and vertical original orientations. When they were saved to the test device, all of the photos were saved with a horizontal orientation. **Make sure to validate these are correct on each iOS version as they seem to change.
In Figure 5 from Physical Analyzer, there is no location data being parsed for the photos that were captured using the CameraMessagesApp. After closer analysis, I could not locate any location data that was recorded within the EXIF data or database metadata for any of the items that were created with com.apple.camera.CameraMessagesApp application. During testing, location services was tuned on and the location was recorded for the files captured with the native camera (com.apple.camera), just not the files captured with com.apple.camera.CameraMessagesApp. Not sure if this is a security feature to conceal the location because the files are being sent via messenger. Note: I did a brief overview of the file at the hexadecimal level and did not find any location data. Additional analysis and research might be required to for a definitive conclusion.
In Figure 8 I have highlighted the columns related to the ZREVERSELOCATIONDATA column. Notice the ZREVERSELOCATIONDATAISVALID column and the values are 1 and 0. I believe based on the test data the values indicate 1 = If available has been populated and 0 = If available has not populated. This also appears to be related to ZGENERICASSET – ZANALYSISSTATEMODIFICATIONDATE. The items with a value of 0 in the ZREVERSELOCATIONDATAISVALID were also missing an Analysis State Modification Date. Additional testing and acquisitions are required for further analysis.
Let’s take a look at ZREVERSELOCATIONDATA bplist. This plist can be viewed from both Cellebrite PA SQLite Viewer and Magnet AXIOM SQLite Viewer, but like I stated earlier I used Ian Whiffins tool Mushy Plist Viewer. The locations discovered within this bplist were accurate as to where the device was located at the time the photos were captured. See Figure 8.1 and Figure 8.2.
There is a significant amount of data being stored within the Photos.sqlite database and there has been recent research and publications about this database by researchers far more advanced than I. I would strongly encourage you to review the reference material list at the end of this blog for additional details and links to the other research.
I located a Magnet Custom Artifact for Photos.sqlite. The custom artifact was submitted by Costas Katsavounidis. The SQLite query was based on iOS 8 and up operating systems. The custom artifact can be located on Magnet Forensics Custom Artifact Exchange. After reviewing the query and its listed references, I learned of additional decoding for information being stored in the database. During the review, I learned lots of the information being decoded by Costas Katsavounidis’s query also applies to iOS 13. This information, along with the information from Jared Barnhart’s research has been included in the query I used and have shared. Please test and validate prior to using within your cases. I have also submitted the query to be added to the Magnet Custom Artifact Exchange. Here is a link to a google drive for the query and custom artifact:
Original file name & /private/var/mobile/Containers/Data/PluginKitPlugin/<UUID>/tmp/:
After running the SQLite script in Navicat (paid) and DB Browser (free), I exported the output into a CSV. For this portion, I will again focus on the live photo that was created at the start of this presentation (IMG_0012.JPG). Figure 9 is a portion of the output to show how the files are related:
Notice the original file name for IMG_0012.JPG is 61777214080__90B95980-BB3C-4A7A-B74E-82C62C923CC2.JPG.
The file 61777214080__90B95980-BB3C-4A7A-B74E-82C62C923CC2.JPG is being stored at: /private/var/mobile/Containers/Data/PluginKitPlugin/45AAD7D6-8C36-411A-B311-04EAE0B5C470/tmp/
During the examination of the suspect device, some of the files created during this testing process were located, but the more obvious files that should have been present appeared to be deleted. What does that mean?
If this photo was attached to a message, sent to another device, and has not been deleted, there should be a file being stored in the device at: /private/var/mobile/Library/SMS/Attachments/. During testing, the files stored at in the /Containers/Data/PluginKitPlugin/<UUID>/temp and /SMS/Attachments/ locations had the same file name and hash.
In the suspect’s device, I located files being stored in the /private/var/mobile/Containers/Data/PluginKitPlugin/<UUID>/tmp/ file location. Notice that I removed the UUID and replaced it with <UUID>. In the suspect’s device the UUID for this temporary file location was different than the one listed in my test device. Here are the two file paths:
When this was discovered in the test device, I believed there had to be documentation which indicated a relationship between the temporary file location and an associated application. Figure 10 is a screenshot from Physical Analyzer that shows the temporary file and a view of the file system. Notice in the file system there is a plist in the root of the “45AAD7D6-8C36-411A-B311-04EAE0B5C470 folder.
In Figure 11 I exported the plist (.com.apple.mobile_container_manager.metadata.plist) and when viewed with Mushy Plist Viewer, I discovered a “MCMMetadataIdentifier: AsciiString = com.apple.CameraMessagesApp.” This appears to be the application associated with the folder path UUID.
Based on the testing and what was observed in the suspect’s device, I came to the conclusion that if a file was stored in this file path, it was captured/created via the CameraMessagesApp.
Note: This was not the only application that was storing data at /private/var/mobile/Containers/Data/PluginKitPlugin. There are several other applications storing data at this location, to include SnapChat. I haven’t gone into detail about the other data contained here, but it would be very beneficial to analyze this file location to see if any evidence you are looking for could be found here.
Now that we have discussed what files are created during the process of capturing a photo within native iOS messenger, let’s take a look at what’s left behind after deleting one or more of those files.
Scenario #2 What happens when a photo is captured from within native iOS messenger, sent as an attachment message and the message that contained the attachment is later deleted from the conversation thread:
The summary for this scenario is that a photo (IMG_0014.JPG) was captured using Camera Messenger Application. When this photo was captured Live Photos was turned OFF. The photo was captured, attached to a message, and then sent to another device. Later, the sent message with the attachment was deleted from messenger.
On July 29, 2020 at 8:23 PM, via the test device, I disabled live photos.
At 8:26:46 PM, the messages application (com.apple.MobileSMS) was launched from the springboard. See Figure #12.
At 8:27:09 PM, the messages camera application (com.apple.camera.CameraMessagesApp) was launched. See Figure #13.
At 8:27:15 PM, A photo was captured of Dexter via com.apple.camera.CameraMessagesApp. The photo depicted Dexter laying down and his eyes could not be seen in the photo. The photo was attached to a message and sent to an android device. The message did NOT contain a text message.
Note: This message and the attachment was deleted prior to the device data extraction.
Several other photos were captured via com.apple.camera.CameraMessagesApp and sent via messages to an android device. In Figure 16, notice that all of the other photos sent during testing have the paperclip icon indicating they are attachments.
At 8:30:05 PM, the messages application, com.apple.MobileSMS, was closed.
At 8:57:08 PM, the messages application was launched.
At 8:58 PM, the message and attachment which contained the earlier discussed photo of Dexter was deleted from the message’s conversation thread.
At 9:00:04 PM, the photos application (com.apple.mobileslideshow) was launched and in focus.
At 9:01 PM, The “Recently Deleted” items were accessed and there were no photos being listed in this area. Upon examining the device data using Physical Analyzer, I located an entry for application usage. It indicated the com.apple.mobileslideshow was launched and used between 9:01:30 – 9:01:34 PM. It indicated an “Activity Type: com.apple.mobileslideshow.album.”
Note: I located data within the KnowledgeC database ZSTRUCTUREDMETADATA table that indicated there might be an expiration date for this data. The expiration date was listed in the column labeled: Z_DKAPPLICATIONACTIVITYMETADATAKEY_EXPIRATIONDATE. I have not tested this but wanted to mention it. See Figure 14.
In Figure 15, we can see the timeline for when a photo was captured and a message with the attachment was sent. This is an example of what the data might look life if the message was not deleted. Notice the outgoing MMS Message entry.
Note: This message did not have a body of text sent with the attachment. After testing, I learned if a message is sent and the message has only had an attachment it will not have an entry for KnowledgeC ZSTREAMNAME – /app/intents messages. After additional testing, I learned this also applies if a message has both a body of text and an attachment. Additionally, there will be only one entry in the sms.db – message table for the body of text and the attachment.
In Figure 16, we can see the files that were created when I captured the live photo. Notice, as previously stated, the files being stored in /SMS/Attachments and /Containers/Data/PluginKitPlugin locations have the same file name.
Let’s get back to the deleted file. In Figure 17 we can see the Artifact view – Media – Thumbnail view and notice the file missing from this process is the photo that would be stored at: /private/var/mobile/Library/SMS/Attachments/.
Figure 18 is another look at the same files but from within Artifacts – Media – Table view. Notice the bar column indicating only two files are being merged with the main record and neither of them are the attachment file.
As mentioned in the previously, the files created and saved to /Containers/Data/PluginKitPlugin/<UUID>/tmp/ will be present regardless if the message and attachment is deleted.
Note: I am not sure how long these files will be present in the device. The files that were present in the first extraction (7/31/2020) were also present in the second extraction (9/5/2020).
Let’s try and find all of the items that are stored in PluginKitPlugin location for that specific application (com.apple.camera.CameraMessagesApp). While in thumbnail view, I filtered the merged similar items based on the UUID “45AAD7D6-8C36-411A-B311-04EAE0B5C470.” See Figure 19.
In Figure 19, all of the duplicates and similar items are merged with each other, hence the layered squares icon on the bottom left of each photo. When examining the icon notifications within Physical Analyzer, I noticed two icons were missing from the highlighted photo. One was the outgoing message icon that indicates a message was sent and the other was the paperclip icon used to indicate attachment. These icons are missing because the message and the attachment were deleted from the message thread. When the message and the attachment were deleted, the associated file being stored at /private/var/mobile/Library/SMS/Attachments/ was deleted. I could not locate the file and it appeared to be removed from the device as soon as the message was deleted.
In Figure 19 the other files being displayed were created via the same method as the one highlighted.
NOTE: It was not this easy when I was examining the suspects device!! There were thousands of photos and I had no idea what I was looking for or what I was looking at once these were found.
Figure 20 is a look into the sms.db – message table and notice there is a missing entry (ROWID 18) for the deleted message.
In Figure 21, we can see the timeline for the photo related to the deleted message and attachment. Notice the outgoing MMS entry is missing from the timeline. Notice the file is being stored in the PluginKitPlugin file location with the UUID file name. Additionally, there is a file being stored in the DCIM file location. We can also see the launched applications and applications in use before and after the photo was captured. This now serves as an indication to me that a message with an attachment might have been sent and then deleted.
Scenario #3 What happens when a photo is captured within native iOS messenger, sent as an attachment message and the photo sent as an attachment is later deleted from the Photos Application:
In Figure 22 we can see within PA a file, IMG_0013.JPG, that was captured using Camera Messenger Application. When this photo was captured Live Photos was turned OFF. The photo was captured, attached to a message and then sent to another device. At a later time, the photo being stored at /private/var/mobile/Media/DCIM/100APPLE was deleted. I was able to extract the device data before the photo was permanently deleted and removed from “Recently Deleted.” In this instance the files that remained on the test device were stored at:
/private/var/mobile/Media/DCIM/100APPLE/IMG_0013.JPG – “Recently Deleted”
Let’s take a look at what this looks like in via the SQLite query that was written for the Photos.sqlite database. See Figure 23. Notice that there is a status column for File Trash State and another column File Trash Date. The highlighted file, IMG_0013.JPG, is indicating the file is in the trash and it provides a trash date or the date it was flagged as “recently deleted.” Additionally, notice that there isn’t any missing entries Z_PK 1-22.
Additional Information about the deleted files:
I restored IMG_0013.JPG from being recently deleted/removed from the trash. The only noticeable change in Photos.sqlite was the file no longer had a Trash State and File Trash Date. I then deleted IMG_0014.JPG, which was then flagged as recently deleted / in the trash. I made this change because I wanted to test if the file stored at /Containers/Data/PluginKitPlugin/<UUID>/tmp/ would remain if all of the other associated files were deleted.
After the file was no longer listed in recently deleted, a second full file system extraction was completed. In Figure 24 and Figure 25 we can see after all of the other associated files were deleted, the file being stored at /private/var/mobile/Containers/Data/PluginKitPlugin/<UUID>/tmp/ still remained.
Let’s take a look at what has changed in the Photos.sqlite via the query. See Figure 26. Notice Z_PK 14, the entire entry for IMG_0014.JPG has been deleted.
During this blog we discussed some additional file locations that should be analyzed if you have an iOS Full File System (FFS) extraction.
You should check the Photos.sqlite database and review the original file names. This could indicate the existence of additional files to analyze and if a FFS extraction would be beneficial to your investigation.
Additionally, you should check Photos.sqlite creator bundle ID which could indicate which application was being used to capture/create the photo. Using this information, you can locate the appropriate /private/var/mobile/Containers/Data/PluginKitPlugin/<UUID>/tmp file location that can be examined for additional files.
The /private/var/mobile/Containers/Data/PluginKitPlugin/<UUID>/tmp file location can hold vital data that might not be present in other types of extractions. It can also contain files that were deleted by the user and they may not exist elsewhere on the device.
I could not have completed this research without help from the other outstanding and knowledgeable professionals who have been doing this type of work long before I even acquired my first cell phone. I would like to take this time to say thanks to everyone who shares their experiences with the community and list some resources that I have used during my forensic examinations and to prepare this blog. I hope it can help you as much as it helped me.
Thanks, for going on this journey with me and please feel free to contact me if you have any questions about this research blog. As always, please test and validate your findings and take time to share!
References and Resources:
- Heather Mahalik Blog: https://smarterforensics.com Twitter @HeatherMahalik
- Sarah Edwards Blog: https://www.mac4n6.com Twitter @iamevltwin GitHub: https://github.com/mac4n6
- Alexis Brignoni Blog: https://abrignoni.blogspot.com Twitter @AlexisBrignoni GitHub: https://github.com/abrignoni
- Ian Whiffin Blog: https://www.doubleblak.com Twitter @BlakDouble
- Ed Michael Twitter @EdXlg123 GitHub: https://github.com/edmichael
- Jared Barnhart Blogs hosted at: https://www.mac4n6.com Twitter @bizzybarney
- Mike Williamson Blog: https://www.forensicmike1.com/2019/05/02/ios-photos-sqlite-forensics Twitter @forensicmike1
- “Finding Waldo: Leveraging the Apple Unified Log for Incident Response” https://www.crowdstrike.com/blog/how-to-leverage-apple-unified-log-for-incident-response/
Posted to CrowdStrike on August 25, 2020 by Jai Musunuri and Erik MartinFrom From The Front Lines
- Costas Katsavounidis email@example.com sqlite query and Magnet Forensics Custom Artifact based on iOS 8+. https://artifacts.magnetforensics.com/CommunitiesArtifactExchangeDownload?Id=a6K0b000000CrdoEAC
and many others!
4 thoughts on “Using Photos.sqlite to show the relationships between photos and the application they were created with? By Scott Koenig”
Excellent analysis. Thanks for taking the time to dive into the file structure and unravel some of Apple’s programming and naming conventions.
Thanks for this. Alot to digest.
Thanks, very helpful.
Have you decoded ZCAMERACAPTUREDEVICE?
Can the value be helpful in deciding with which phone the picture was taken?
I should add that!