The biggest one is the ZSCENECLASSIFICATION table. There are several other examples of this throughout the query. There could be data indicating that when the live photo was captured that both a photo and a video were captured. Another example of this would be when a live photo is captured. This is because the OS is analyzing the assets for people and detected faces. 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 due to the amount of data being stored in the database for each asset. For example, I moved important dates, file paths and filenames from different tables to one area of the query for easier review.Īfter 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. 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. Some of the data from those tables were omitted from the query. 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.
When constructing the queries, I took an iOS 14 Photos.Sqlite database and started by documenting every column from every table. 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. It would be my pleasure to incorporate your research into the queries. 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. 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. Its purpose is a reference rather than a step-by-step blog on how the information gets populated. To get these queries out as fast as possible, I decided to keep the blog short and to the point.
Links to the other published material can be found in the resources section at the end of this blog. To put these queries together, I conducted several tests and incorporated other published material. Forensic Browser for SQLite version 3.3.0.Users//Pictures/PhotosLibrary.photoslibrary/database/Photos.sqlite private/var/mobile/media/PhotoData/Photos.Sqlite Just as a reminder, the Photos.Sqlite database can be found using the following file paths: 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.” These queries serve as an additional resource and a starting point for a deep dive into a file/asset. These queries do not include everything contained in the PhotoData folder nor everything in the Photos.Sqlite database. This is a follow-up to the aforementioned blog and will provide some updated SQLite queries. The writeup was eventually sent to DFIR Review ( ) and published on their website.
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.