Photos.Sqlite Queries – Update

I would like to start off by saying thank you to everyone who has reached out about the Photos.sqlite queries I previously posted. After chatting with some people who have used the queries, it was suggested that I update the queries to include the following:

  • Restructuring the query output
  • Renaming and clarifying the column names for easier validation
  • Update some of the data decoding
  • Creating some queries that could be used for specific artifacts

To do this, I used data from multiple devices and multiple versions of iOS, including 12, 13, 14, and 15. I had limited data to use for iOS 12 and 13 queries, but based on the database similarities to iOS 14, I believe the queries will work and adequately give you the data you are interested in analyzing. I also restructured the queries that should allow for easier validation.

Most of the database table names have an alias. The table aliases have been used to shorten the table names and were needed to create the multiple joins to parse the data for a particular asset.

Example of table name alias:

  • Table Name
    • ZADDITIONALASSETATTRIBUTES
  • Table Name Alias
    • zAddAssetAttr

In these updated queries, you will notice all the column headers have a table alias and a column name.  This should allow you to determine the table and original column name the data is being parsed and decoded from.

Example: In the query, the column name is zCldMast-Org Filename

  • Table Name Alias
    • zCldMast = ZCLOUDMASTER
  • Column Name
    • Org Filename = ZORIGINALFILENAME

Almost all the decoded data will be accompanied with the original integer value from the database table. This should allow you to easily validate the value I have decoded and what the original value was in the database.

Example:

  • Decoded Text-Original Integer
    • Visible-Photo-Library-0
    • 2-Not-Visible-Photo-Library-2

The original integer might be listed before or after the decoded text. If the decoded text is “Unknown”, I was not able to decode the integer during testing. As I have stated in the past, there is no way to create every scenario to decode and validate every column integer in the photos.sqlite database. Please use these queries as a tool, but you should always test and validate your findings.

To use the queries across different SQLite tools I had to limit the number of columns in the query results. To do this, I had to ignore some of the query statements by using hyphens (- -) before the query statement. If you wish to include the ignored statements in your query results, simply delete the hyphens and then run the query.

At the end of the queries, you will notice I have used an ORDER statement to sort the query results by the asset created date (ORDER BY zAsset.ZDATECREATED) and I have included a WHERE statement that will allow you to narrow the query results based on a date range (WHERE zAsset.ZDATECREATED BETWEEN ‘####’ AND ‘####’). The following is an example of what these ORDER and WHERE statements might look like after you have added the dates into the WHERE statement.

WHERE zAsset.ZDATECREATED BETWEEN ‘603499468’ AND ‘640742342’ 

ORDER BY zAsset.ZDATECREATED

These queries are for everyone to use, that includes those of you who code open-source tools and commercial tools. If you believe you have sufficiently validated the data via these queries and you wish to use that data in your tools, please feel free to do so.

Update 03/26/2022: Several of the queries have been removed from GitHub, because of recent decoding updates. I decided to only update one query for each version of iOS. The All Tables query for each version of iOS will have the WHERE statements listed at the end of the query which will allow you to narrow your results if you wish. Please feel free to contact me if you need any assistance with narrowing the queries to meet your need and I will be happy to assist.  

The following is a list of queries I have made for iOS 12 and iOS 13.  

iOS 12:

The following queries can be used for iOS 12 Photos.sqlite database.

  • iOS_12_#0_All-Tables_Photossqlite
  • iOS_12_#1_Basic-Data_Photossqlite
  • iOS_12_#2_Albums-Shared-Invites_Photossqlite
  • iOS_12_#3_Persons-DetectedFace_Photossqlite
  • iOS_12_#4_Mom-Mem-PHighlight_Photossqlite
  • iOS_12_#5_ScenePrint-Classification_Photossqlite
  • iOS_12_#6_Change-Transactions_Photossqlite
  • iOS_12_#7_Artifact_Filter_Query_Photossqlite

iOS 13:

The following queries can be used for iOS 13 Photos.sqlite database.

  • iOS_13_#0_All-Tables_Photossqlite
  • iOS_13_#1_Basic-Data_Photossqlite
  • iOS_13_#2_Albums-Shared-Invites_Photossqlite
  • iOS_13_#3_Persons-DetectedFace_Photossqlite
  • iOS_13_#4_Mom-Mem-PHighlight_Photossqlite
  • iOS_13_#5_ScenePrint-Classification_Photossqlite
  • iOS_13_#6_Change-Transactions_Photossqlite

The following are iOS 14 and iOS 15 queries and brief description of data that will be included in the query results.

iOS 14:

iOS_14_#0_BigSur_All-Tables_Photossqlite – Includes all of tables I was able to join. There are additional tables in the database, but they either did not contain data during testing, or I could not determine a join to the ZASSET table. Lots of the statements in this query are being ignored by using hyphens (- -) before the line of text in the query. To include the data in the query, delete the hyphens (- -) before the query statement and/or the join statement.

iOS_14_#1_BigSur_Basic-Data_Photossqlite – This query can be used as a basic query to review data associated with the assets listed in the ZASSETS table.

iOS_14_#2_BigSur_Albums-Shared-Invites_Photossqlite – This query can be used to view the basic data for the assets, the album information, any cloud shared likes or comments and if there are any invites to shared albums.

iOS_14_#3_BigSur_Persons-DetectedFace_Photossqlite – This query can be used to view the basic data for the assets, if a face has been detected in the asset, if a person has been detected in the asset and if a person/detected face has been identified and a face crop has been created.

iOS_14_#4_BigSur_Mom-Mem-PHighlight_Photossqlite – This query can be used to view the basic data for the assets and if the asset is listed as a Memory, Photos Highlight, Moment and/or Suggestion. As you can see by the query, there are several types of Memories, Photos Highlights, Moments and Suggestions that an asset can be associated with.

iOS_14_#5_BigSur_ScenePrint-Classification_Photossqlite – This query can be used to view the basic data for the assets, the Screen Print data, and Scene Classification data.

iOS_14_#6_BigSur_Change-Transactions_Photossqlite – This query can be used to view the basic data for the assets with the Change data, Transaction and Transaction String data.

iOS_14_#7_BigSur_MigrationHistory_Photossqlite – This is a bonus! This table details if/when a device iOS update occurred. During testing, I performed a factory reset and it removed all the update history except for the entry for the factory rest. I thought this was going to be a valuable artifact to determine when the factory reset occurred, but the timestamp entry for the factory reset was invalid. If you are interested to know when a device iOS update occurred, this query might help you out. Additional testing needed to determine the forensic value of the data stored in this table.

iOS 15:

iOS_15_#0_Monterey_All-Tables_Photossqlite – Includes all of tables I was able to join. There are additional tables in the database, but they either did not contain data during testing, or I could not determine a join to the ZASSET table. Lots of the statements in this query are being ignored by using hyphens (- -) before the line of text in the query. To include the data in the query, delete the hyphens (- -) before the query statement and/or the join statement.

iOS_15_#1_Monterey_Basic-Data_Photossqlite – This query can be used as a basic query to review data associated with the assets listed in the ZASSETS table.

iOS_15_#2_Monterey_Albums-Shared-Invites_Photossqlite – This query can be used to view the basic data for the assets, the album information, any cloud shared likes or comments and if there are any invites to shared albums.

iOS_15_#3_Monterey_Persons-DetectedFace_Photossqlite – This query can be used to view the basic data for the assets, if a face has been detected in the asset, if a person has been detected in the asset and if a person/detected face has been identified and a face crop has been created.

iOS_15_#4_Monterey_Mom-Mem-PHighlight_Photossqlite – This query can be used to view the basic data for the assets and if the asset is listed as a Memory, Photos Highlight, Moment and/or Suggestion. As you can see by the query there are different types of Memories, Photos Highlights, Moments and/or Suggestions that an asset can be associated with.

iOS_15_#5_Monterey_ScenePrint-Classification_Photossqlite – This query can be used to view the basic data for the assets, the Screen Print data, and Scene Classification data.

iOS_15_#6_Monterey_Change-Transactions_Photossqlite – This query can be used to view the basic data for the assets with the Change data, Transaction and Transaction String data.

iOS_15_#7_Monterey_MigrationHistory_Photossqlite – This is a bonus! This table details if/when a device iOS update occurred. If you are interested to know when a device iOS update occurred, this query might help you out. Additional testing needed to determine the forensic value of the data stored in this table.

iOS 14 and 15 Additional Artifact Filter Queries:

iOS_14_#8_BigSur_Artifact_Filter_Query_Photossqlite

iOS_15_#8_Monterey_Artifact_Filter_Query_Photossqlite

In addition to the standard queries, I have created a query for both iOS 14 and iOS 15, that can be used to query the data based on several WHERE statements, which query some artifacts of interest. By default, the WHERE statements are being ignored by using hyphens (–) before each WHERE statement. To include a statement, first determine which artifact you want to query, then delete the hyphens (–) before that specific WHERE statement, copy the entire query into your SQLite tool of choice and run the query.

The following is a list of the WHERE statements that I have included and the types of artifacts you will get because of the statement.

  • Date Created:
  • WHERE zAsset.ZDATECREATED BETWEEN ‘#####’ AND ‘#####’
  • This statement will allow you to target the assets that have a created date (UTC) within a targeted date range. The following is an example of what the completed WHERE statement might look like:
  • WHERE zAsset.ZDATECREATED BETWEEN ‘603499468’ AND ‘640742342’
  • Asset Type – Digital Camera Image (DCIM) Asset:
  • WHERE zAsset.ZSAVEDASSETTYPE = 3
  • This statement will allow you to target the assets that have a saved asset type of Digital Camera Image (DCIM). Assets typically with this saved asset type will be saved within a DCIM/***APPLE file path. This could include assets captured with the device camera, native applications, and third-party applications.
  • Asset Type – Photo Cloud Sharing Data Asset:
  • WHERE zAsset.ZSAVEDASSETTYPE = 4
  • This statement will allow you to target the assets that have a saved asset type of Photo Cloud Sharing Data. These assets are normally within a Shared Album. Assets typically with this saved asset type will be saved within a PhotoData/PhotoCloudSharingData file path. Typically, these assets will not be visible in the Photo Library because they are in a Cloud Sharing Album.
  • Note: If a user shares an asset from the Photo Library / DCIM file path the zAddAssetAttr-Original Filename column data will contain the assets original filename. You can use this original filename and match it to the zAsset-Filename, in an effort to locate the original asset that was shared. The original asset will have a last shared date.
  • Asset Type – Cloud Photo Library Asset:
  • WHERE zAsset.ZSAVEDASSETTYPE = 6
  • This statement will allow you to target the assets that have a saved asset type of Cloud Photo Library Asset. Assets typically with this saved asset type will be saved within a PhotoData/CPLAssets/group file path. Typically, these assets will be visible in the Photo Library. These assets will have a zInternalResource-Cloud Last Prefetch Date and a prefetch count, which will indicate the last time the device fetched/synced the asset from iCloud.
  • Asset Type – Cloud Master Moment Asset:
  • WHERE zAsset.ZSAVEDASSETTYPE = 8
  • These assets will have a zInternalResource-Cloud Last Prefetch Date and a prefetch count, which will indicate the last time the device fetched/synced the asset from iCloud. These assets will be in a Shared Album and will have additional data via the zShare table.
  • Asset Last Shared Date:
  • WHERE zAsset.ZLASTSHAREDDATE > 0
  • This statement will allow you to target the assets that have been shared.
  • Asset Has Location:
  • WHERE zAsset.ZLATITUDE > 0 or zExtAttr.ZLATITUDE > 0
  • This statement will allow you to target the assets which have location data.
  • Asset has a copy:
  • WHERE zAddAssetAttr.ZDESTINATIONASSETCOPYSTATE = 2
  • This statement will allow you to target the assets which have a copy.
  • Asset Imported to device via Cloud/Other:
  • WHERE zAddAssetAttr.ZIMPORTEDBY = 0
  • This statement will allow you to target the assets which have been imported from the Cloud or other source. These include asset types like Cloud Photo Library Assets, Photo Cloud Sharing Data, and possibly other unknown asset types.
  • Asset Imported / Captured via Native Back Camera:
  • WHERE zAddAssetAttr.ZIMPORTEDBY = 1
  • This statement will allow you to target the assets which have been imported to the device from the native back camera. In some cases, this can be verified by reviewing the zExternalAttributes table or the zCloudMasterMediaMetadata-Data column.
  • Asset Imported to device via Native Front Camera:
  • WHERE zAddAssetAttr.ZIMPORTEDBY = 2
  • This statement will allow you to target the assets which have been imported to the device from the native front camera. In some cases, this can be verified by reviewing the zExternalAttributes table or the zCloudMasterMediaMetadata-Data column.
  • Asset Imported to device via Third Party Application:
  • WHERE zAddAssetAttr.ZIMPORTEDBY IN (3, 6)
  • This statement will allow you to target the assets which have been imported to the device from the third-party applications.
  • Asset Imported to device via Native Application:
  • WHERE zAddAssetAttr.ZIMPORTEDBY IN (8, 9)
  • This statement will allow you to target the assets which have been imported to the device from the native applications.
  • Asset Imported to device via a Specific Application:
  • WHERE zAddAssetAttr.ZIMPORTEDBYBUNDLEIDENTIFIER = ‘BundleIdentifier’
  • This Statement will allow you to target the assets which have been imported to the device from a specific native or third-party application. An example of this would be if you wanted to target all the assets that were imported from Snapchat, see the below example:
  • WHERE zAddAssetAttr.ZIMPORTEDBYBUNDLEIDENTIFIER ‘com.toyopagroup.picaboo’
  • Asset has Pending View/View Count:
  • WHERE  zAddAssetAttr.ZPENDINGVIEWCOUNT > 0 or zAddAssetAttr.ZVIEWCOUNT > 0
  • This statement will allow you to target the assets which have viewed count or have pending view counts.
  • Asset has Pending Play/Play Count:
  • WHERE  zAddAssetAttr.ZPENDINGPLAYCOUNT > 0 or zAddAssetAttr.ZPLAYCOUNT > 0
  • This statement will allow you to target the assets which have play count or have pending play counts.
  • Asset has Pending Share/Share Count:
  • WHERE  zAddAssetAttr.ZPENDINGSHARECOUNT > 0 or zAddAssetAttr.ZSHARECOUNT > 0
  • This statement will allow you to target the assets which have share count or have pending share counts.
  • Asset Has Adjustments:
  • WHERE zAsset.ZHASADJUSTMENTS = 1
  • This statement will allow you to target the assets which have adjusted. Based on testing, an asset will have an adjustment change listed if assets have been adjusted before, during and after the asset has been captured. I have been able to replicate some of these adjustments but believe there are several more out there that I have not been able to replicate. Below are some examples of these adjustments:
  • Markups – writing or drawing on an asset
  • Delayed Timer – Using a delayed timer prior to capturing an asset UPDATE: this was being decoded incorrectly
  • Filter – Changing the color or filter on an asset before during or after an asset is captured
  • Adjust – entering the “Edit” menu of an asset then making changes and saving those changes
  • Video-Trim – Editing the length of a video after capture
  • Screenshot Services – After a screenshot is created and clicking on item to open the edit menu and making changes to the screenshot
  • Asset is Favorite:
  • WHERE zAsset.ZFAVORITE = 1
  • This statement will allow you to target the assets which have been marked as favorite.
  • Asset is Hidden:
  • WHERE zAsset.ZHIDDEN = 1
  • This statement will allow you to target the assets which have been hidden.
  • Asset is in Trash/Recently Deleted:
  • WHERE zAsset.ZTRASHEDSTATE = 1
  • This statement will allow you to target the assets which have been marked as trash and are listed in the recently deleted area.
  • Asset has Description & Comments:
  • WHERE zAssetDes.ZLONGDESCRIPTION > 0 or zAddAssetAttr.ZTITLE > 0 or zCldSharedComment.ZCOMMENTTEXT > 0
  • This statement will allow you to target the assets which have descriptions and comments.
  • Asset liked:
  • WHERE zCldSharedCommentLiked.ZISLIKE = 1
  • This statement will allow you to target the assets which have been liked.
  • Asset in Generic Album:
  • WHERE zGenAlbum.ZCLOUDLOCALSTATE = 1
  • This statement will allow you to target the assets which are within a Generic Album.
  • Asset in Shared Album:
  • WHERE zGenAlbum.ZCLOUDLOCALSTATE = 0
  • This statement will allow you to target the assets which are within a Shared Album.
  • Generic Album in Trash/Recently Deleted:
  • WHERE zGenAlbum.ZTRASHEDSTATE = 1
  • This statement will allow you to target assets which are within an album that is in the trash or recently deleted.
  • Cloud Shared Album Invite Record sent to Device Apple ID:
  • WHERE zCldShareAlbumInvRec.ZISMINE = 1
  • This statement will allow you to target the assets which are within a Shared Album and have a Cloud Shared Album Invite Record for the Apple ID being used on the examined device.  
  • Shared Asset is Owned by Device Apple ID:
  • WHERE zAsset.ZCLOUDISMYASSET = 1
  • This statement will allow you to target the assets which are within a Shared Album and have a Cloud Shared Album Invite Record for a different Apple ID other than the one being used on the examined device.
  • Asset last synced between Device and iCloud account:
  • WHERE zIntResou.ZCLOUDPREFETCHCOUNT > 0
  • This statement will allow you to target the assets that have an Internal Resource Cloud Prefetch Count. This count is related to the Internal Resource Cloud Last Prefetch Date. As discussed in the Cloud Photo Library Asset Type artifact, these values will indicate the last time the asset was synced/prefetched between the device and iCloud Photos. In iOS 14 and 15 you can examine the properties list (cloudServiceEnableLog.plist) located at: \private\var\mobile\Media\PhotoData\private\com.apple.accountsd\ which contains data that indicates when iCloud Photo’s sync was enabled and disabled. The date and times listed in this property list are in UTC.
  • Asset with Detected Face Area Points:
  • WHERE zAsset.ZFACEAREAPOINTS > 0
  • This statement will allow you to target the assets where Face Area Points have been detected. This may not mean that a human face was detected, during testing assets with dogs also had face area points. It should be noted, this data will only be populated after the asset has been analyzed. You can check to see if the asset was analyzed by reviewing the data stored in the zAddAssetAttr table Scene Analysis Version, Scene Analysis Timestamp columns and zMedia Analysis Asset Attributes table Media Analysis Timestamp column.
  • Person Detected has Face Crop:
  • WHERE zFaceCrop.ZRESOURCEDATA > 0
  • This statement will allow you to target the assets where Face Crop data has been populated. The Resource Data column data will be a cropped photo of the person that has been identified.

How to edit the query if you receive an error:

During testing, I noticed Photos.Sqlite database may have changes after an iOS minor version update. These changes could cause the queries to have an error and might require changes. The following is an example of I encountered during testing:

Most of my iOS 14 testing was preformed using data from an iOS 14.7 device, thus this is what I used to build the queries. When testing the queries on Josh Hickman’s public iOS 14.3 data, I received an error stating there was no such column as ZADDITIONALASSETATTRIBUTES.ZIMPORTEDBYDISPLAYNAME. After reviewing the database in both versions, Apple added this column at some point between these two version releases. To have the query function properly, I needed to remove the statement that included that column from the query.

The following is a video of how these changes can be made to allow the query to function properly:

My work on the Photos.sqlite is far from over and I will continue to publish my findings. A detailed blog is going to follow this update that should answer some of the questions you might have about how I was able to decode some of the data. I’m just having to find the time and a new show to start watching while I’m working on the blog, currently half-way through Shameless.

Please feel free to contact me if you have any questions about any of the queries and I hope they can help you protect the innocent!

2 thoughts on “Photos.Sqlite Queries – Update

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: