Hello everyone! Back in August 2020, I wrote a blog “Using Photos.Sqlite to show the relationships between photos and the application they were created with?” which was posted on Heather Mahaliks’ blog, https://smarterforensics.com/. The writeup was eventually sent to DFIR Review (https://dfir.pubpub.org/pub/v19rksyf/release/1) and published on their website.
This is a follow-up to the aforementioned blog and will provide some updated SQLite queries. These queries do not include everything contained in the PhotoData folder nor everything in the Photos.Sqlite database. These queries serve as an additional resource and a starting point for a deep dive into a file/asset. As Jared Barnhart stated in his blog, “this singular file is nearly a full-time job if someone wanted to parse every inch of it and maintain that support.”
Just as a reminder, the Photos.Sqlite database can be found using the following file paths:
iOS:
/private/var/mobile/media/PhotoData/Photos.Sqlite
Mac OS:
/Users/<USER>/Pictures/PhotosLibrary.photoslibrary/database/Photos.sqlite
SQLite Tools Used:
- Forensic Browser for SQLite version 3.3.0
- DB Browser version 3.12.0
- ArtEx
Test Devices and iOS:
- iPhone X with iOS 14.7 (18G69)
- Big Sur 11.14
- iPhone 6s Plus with iOS 15.1 (19B74)
- Monterey 12.0.1
To put these queries together, I conducted several tests and incorporated other published material. Links to the other published material can be found in the resources section at the end of this blog.
To get these queries out as fast as possible, I decided to keep the blog short and to the point. Its purpose is a reference rather than a step-by-step blog on how the information gets populated. Within this reference guide, I’ll point out some areas of the query which you may have questions about, and other areas which need additional research.
On that note, if you or anyone you know has decoded areas of the database and would like to contribute to the queries, please don’t hesitate to contact me. It would be my pleasure to incorporate your research into the queries. Shortly after posting a little hint for the query, Josh Hickman contributed some information he had linking the face crop photo to an identified person. Thanks Josh!!
OK, let’s dive in…
When constructing the queries, I took an iOS 14 Photos.Sqlite database and started by documenting every column from every table. Some of the tables did not have data and others had data but I could not figure out how they joined to the main tables. Some of the data from those tables were omitted from the query.
Once I had the tables joined and a working query, I organized the data, as best I could, so it might be easier to locate related material about the files/assets. For example, I moved important dates, file paths and filenames from different tables to one area of the query for easier review.
After you use the query, you will notice a lot more rows of data compared to the number of assets you have in the photos library. This is due to the amount of data being stored in the database for each asset. For example, if an asset (photo, live photo, or video) has five people in the photo, you may have 5 rows of data for that one asset. This is because the OS is analyzing the assets for people and detected faces. Another example of this would be when a live photo is captured. There could be data indicating that when the live photo was captured that both a photo and a video were captured. There are several other examples of this throughout the query. The biggest one is the ZSCENECLASSIFICATION table. Using the query, which includes scene classification (SceneClass), will cause a query that has a result of 1188 rows jump to a result with 12067 rows. If you are going to use the query with scene classification, I would recommend DB Browser, it seemed to handle the large number of blobs the easiest. I have included three different queries:
iOS <14 or 15> Photos.sqlite Query with Scene Classification
The first one has everything possible that could join and includes Scene Classification. This one will have the largest result and will have large number of blobs.
iOS <14 or 15> Photos.sqlite Query without Scene Classification
The second has everything possible that I could join except for Scene Classification. This one will also have a large result due to the number of Memories, Moments and Photo Highlights each asset will have.
iOS <14 or 15> Photos.sqlite Query without Scene Classification Memories Photo Highlights Moments Suggestions
The third query will be the one most of you will want to use on a regular basis. This query will give you all the important information for an asset, but will not include Scene Classification, Memories, Photo Highlights, Moments or Suggestions.
Throughout the query, you will encounter results which resemble the following:
- “0-Unknown”
- “3-Live Photo”
When you encounter an example of “3-Live Photo” as a query result, it simply means I was not able to definitively decode the value and I’m making an educated guess on the meaning. If it states “0-Unknown”, it means I have no idea what these values mean.
There are several blobs included in the query results. These blobs include photos, plists and other raw values that I did not spend a lot of time decoding. My focus was getting the data out of the database. All three tools used had advantages and disadvantages.
Below is some information about some of the Photos.Sqlite tables:
ACHANGE – Table contained data during testing but was not included in the query.
ATRANSACTION- Table contained data during testing but was not included in the query.
ATRANSACTIONSTRING – Table contained data during testing but was not included in the query.
ZADDITIONALASSETATTRIBUTES – Data was included in the query and has an alias of ZAddAssetAttr.
ZALBUMLIST – Data was included in the query and does not have an alias.
ZASSET – Data was included in the query and does not have an alias.
ZASSETANALYSISSTATE – Table did not contain data during testing and was not included in the query.
ZASSETDESCRIPTION – Data was included in the query and has an alias of AssetDes.
ZCHARACTERRECOGNITIONATTRIBUTES – Data was included in the query and has an alias of CRA. This table is new with iOS 15.
ZCLOUDFEEDENTRY – Data was included in the query and has an alias of CFE.
ZCLOUDMASTER – Data was included in the query and has an alias of CM.
ZCLOUDMASTERMEDIAMETADATA – Data was included in the query and has an alias of CMMMD.
ZCLOUDSHAREDALBUMINVITATIONRECORD – Data was included in the query and has an alias of CSAIR. This table contains the data needed to review when a shared album invitation was sent to and from others to view and join the shared album. It also includes the information if the invitation was accepted or denied. There were some values that I could not replicate during testing and are labeled as unknown, but most of the data is decoded.
ZCLOUDSHAREDCOMMENT – Data was included in the query and has an alias of CSC. This table contains the data needed to review if someone likes and comments an asset in a shared folder. I had difficulties with this table as there are two columns (ZCOMMENTEDASSET and ZLIKEDASSET) which both contain the key need to join this table to the ZASSET table. I was not able to get them both to join and populate all the data. The column used to join the tables in the queries is the ZCOMMENTEDASSET, thus you should get the comments if there are any, but if you want to see which asset is liked you will need to change the join statement at the end of the query. Here are the join statements if you wish to swap them out and see the differences:
To see comments:
- LEFT JOIN ZCLOUDSHAREDCOMMENT CSC ON CSC.ZCOMMENTEDASSET = ZASSET.Z_PK
To see likes:
- LEFT JOIN ZCLOUDSHAREDCOMMENT CSC ON CSC.ZLIKEDASSET = ZASSET.Z_PK
ZCODEC – Data was included in the query and has an alias of CAA.
ZCOMPUTEDATTRIBUTES – Data was included in the query and has an alias of CAA.
ZDEFFERREDREBUILDFACE – Table did not contain data during testing and was not included in the query.
ZDETECTEDFACE – Data was included in the query and has an alias of DF. This table and others will allow you to analyze if an asset has a detected face and other possible information about a person. During testing, I found this information to be semi-reliable, meaning sometimes it would detect the same person, but other times the same person in different photos had different person identifiers. The age, hair color, eye open or closed and smile types didn’t always match up with what was occurring in the asset. This made it difficult to decode all the different categories. I did my best, but further testing will be needed.
- Jared Barnhart wrote up some of his research on this topic and can be located at: http://www.mac4n6.com/blog/2020/7/19/follow-on-to-dfir-summit-talk-lucky-ios-13-time-to-press-your-bets-via-bizzybarney
ZDETECTEDFACEGROUP – Table contained data during testing but was not included in the query.
ZDETECTEDFACEPRINT – Data was included in the query and has an alias of DFP.
ZDETECTIONTRAIT – Data was included in the query and has an alias of DFP. This table is new with iOS 15.
ZEDITEDPTCATTRIBUTES – Table did not contain data during testing and was not included in the query.
ZEXTENDEDATTRIBUTES – Table listed in both iOS 14 and iOS 15. iOS 14 did not contain any data, but iOS 15 contained data. This table has an alias of ExtAttr. In iOS 15, this table contains some specific information about camera, including camera make, model and lens model that was used to create the asset. This table includes other important information like location data and if the flash was used.
ZFACECROP – Data was included in the query and has an alias of FC. This table contains information that can be analyzed if the system or a user identifies a person in an asset and adds a name to the person. This will create a face crop jpeg blob and will be stored in the table.
ZFILESYSTEMBOOKMARK – Table did not contain data during testing and was not included in the query.
ZFILESYSTEMVOLUME – Table did not contain data during testing and was not included in the query.
ZGENERICALBUM – Data was included in the query and has an alias of GenAlbum. This table contains information that can be analyzed when an album is created by the system or by a user.
- Shafik Punja spoke about his research a few years ago which assisted me with including the album portion of the query.
ZGLOBALKEYVALUE – Table did not contain data during testing and was not included in the query.
ZINTERNALRESOURCE – Data was included in the query and has an alias of IR. This table contains lots of metadata for the asset. There are still lots of data in this table that I was not able to decode. This table will require further testing.
ZKEYWORD – Table did not contain data during testing and was not included in the query.
ZLEGACYFACE – Table did not contain data during testing and was not included in the query.
ZLIMITEDLIBARYFETCHFILTER – Table did not contain data during testing and was not included in the query.
ZMEDIAANALYSISATTRIBUTES – Data was included in the query and has an alias of MediaAnalyAsset.
ZMEMORY – Data was included in the query and has an alias of MEM. This table and others contain data related to Memories, Moments and Highlights that are created based on other data from assets. If you are encountering many rows for with what appears to be the same file, pay special attention to the MEM.ZGRAPHMEMORYIDENTIFIER as ‘Graph Memory ID MEM’ column. One asset can be used in multiple memories.
ZMIGRATIONHISTORY – Table contained data during testing but was not included in the query. This table is worth a look if you are trying to determine when the device software was upgraded.
ZMOMENT – Data was included in the query and has an alias of MOM. This table and others contain data related to Memories, Moments and Highlights that are created based on other data from assets.
ZMOMENTLIST – Data was included in the query and has an alias of MOMList. This table and others contain data related to Memories, Moments and Highlights that are created based on other data from assets. This table was removed in iOS 15.
ZPERSON – Data was included in the query and has an alias of PER. This table contains the data needed to analyze if a person was created or detected in an asset. There is still a lot of research needed to decode all the values, but this should get you started.
ZPERSONREFERENCE – Data was included in the query and has an alias of PERREF. This table did not contain data during testing but was in both iOS 14 and iOS 15, so it’s included.
ZPHOTOSHIGHLIGHT – Data was included in the query and has an alias of PH. This table and others contain data related to Memories, Moments and Highlights that are created based on other data from assets.
ZQUESTION – Table did not contain data during testing and was not included in the query.
ZSCENECLASSIFICATON – Data was included in the query and has an alias of SceneClass. This is the big one!! Just to give you an example, during testing my ZASSET table contained 762 unique items and the ZSCREENCLASSIFICATION table contained 18,119 unique items. I believe some of the commercial and open-source tools are already decoding and presenting some of this data within their tools, but still wanted to include it in the query.
ZSCENEPRINT – Data was included in the query and has an alias of SceneP.
ZSEARHDATA – Table did not contain data during testing and was not included in the query. This table was removed from iOS 15.
ZSHARE – Data was included in the query and has an alias of SHA. This was an interesting discovery for me. This table contains data for a shared album which was created via the iCloud Website. There might be other instances where a ZSHARE entry is made into this table, but I could not create another instance other than creating a shared album via iCloud website.
ZSHAREPARTICIPANT – Table did not contain data during testing and but has been included in the query.
ZSUGGESTION – Data was included in the query and has an alias of SUGG.
ZUNIFORMTYPEIDENTIFIER – Data was included in the query and has an alias of UniID.
ZUNMANAGEDADJUSTMENT – Data was included in the query and has an alias of UA.
ZUSERFEEDBACK – Data was included in the query and has an alias of UFB.
ZVISUALSEARCHATTRIBUTES – Data was included in the query and has an alias of VSAttr.
If you are overwhelmed by the number of rows for each asset when you use the queries, you can minimize the number of rows by removing certain tables from the query.
For example: If you do not want to analyze the information regarding Memories (MEM), Moments (MOM), Photos Highlights (PH) and Suggestions (SUGG), follow these steps:
- Open the query in a text editor
- Search the query for the tables (MEM, MOM, PH, SUGG) you want to omit from the results
- Delete the column names and aliases for each table in your search results
- Delete the join statement for each table you want to omit
- Then re-run the query
This can also be done for Detected Face (DF), Person (PER) Detected Face Print (DFP) and Face Crop (FC).
Eliminating these tables/categories will drastically reduce the number of rows in your results.
iOS 15 Notes:
Review the blog published by Ian Whiffin (https://www.doubleblak.com/blogPosts.php?id=23) for additional details about the new feature that allows a user to adjust the date, time, and location of an asset.
The work on Photos.sqlite is far from over. There are several values that still need to be tested and decoded, but I wanted to publish what I have done so far so it could be used by the community. As I stated in the beginning, please feel free to contact me if you have any questions or would like to contribute to the query.
iOS 14 and Big Sur queries
iOS 15 and Monterey queries
References
Ian Whiffin – iOS Media Adjustments
Shafik G. Punja – iOS Photos.Sqlite Albums
- https://www.forensicmike1.com/2019/05/02/iosphotossqliteforensics/
- https://www.cellebrite.com/en/identifyingfiletoalbumcorrelationusingiosphotossqlite/
Jared Barnhart – Facial Recognition in Photos
Trey Amick – MacOS & iOS Photos Support with Magnet AXIOM
Rhet Turnbull
Recognizing people in photos through private on-device machine learning – Apple. Thanks to Chewing the FAT podcast for sharing the link.
https://machinelearning.apple.com/research/recognizing-people-photos
The Forensic Scooter 
2 thoughts on “Photos.sqlite Queries – Original Blog Posting”