Local Photo Library Photos.sqlite Query Variations & WHERE statements

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, 15, and 16. 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 zAsset.ZADDEDDATE) 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 zAsset.ZADDEDDATE

Local Photo Library Photos.sqlite queries:

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 09/17/2022: Several of the queries have been added and removed from GitHub, due to recent research and decoding updates. Please check back frequently to get the most recent query prior to using it for your investigation. The iOS<**>_LPL_Photossqlite_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 am happy to assist.    

iOS 12:

iOS 13:

iOS 14:

iOS 15:

iOS 16:

The following is a list of queries that have been posted to my GitHub and the types of assets they will target. If you are interested in a more specific Photos.sqlite query, please don’t hesitate to contact me and ill do my best to assist you with building the query.

iOS<**>_LPL_Photossqlite_Query – Includes most 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<**>_LPL_Photosqlite_Query_Basic – This query can be used as a basic query to review data associated with the assets listed in the ZASSETS table. Please use this query first for faster results, but please keep in mind, this query will omit data that the full query listed above might include.

iOS<**>_LPL_Assets_in_Albums_Photossqlite_Query – 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<**>_LPL_Assets_Adjusted-Mutation_Photossqlite_Query – This query will target those assets that have zAsset.ZHASADJUSTMENTS value of 1, indicating the assets have been adjusted by either the OS or by a user. These adjustments tracked by this column are only those made within Apple applications. Changes, adjustments, or mutations by third party apps are not tracked by this column.

iOS<**>_LPL_Assets_CloudMasterMoment-iCloudShareLink_Photossqlite_Query – This query will target those assets that have zAddAssetAttr.ZSHARETYPE value of 1, indicating the assets are a Cloud Master Moment Share Asset, also known as an iCloud Share Link.

iOS<**>_LPL_Assets_Hidden_Photossqlite_Query – This query will target those assets that have zAsset.ZHIDDEN value of 1, indicating the assets are hidden from view and can be found in the Hidden Utility on the device.

iOS<**>_LPL_Assets_RecentlyDeleted_Photossqlite_Query – This query will target those assets that have zAsset.ZTRASHEDSTATE value of 1, indicating the assets have been recently deleted from view and can be found in the Recently Deleted Utility on the device.

iOS<**>_LPL_Assets_with_Locations_Photossqlite_Query – This query will target those assets that have zAsset.ZLATITUDE > 0 or zExtAttr.ZLATITUDE > 0, indicating the assets have location coordinates.

iOS<**>_LPL_Photossqlite_MigrationHistory_Query – 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.

PENDING iOS<**>_LPL_Assets_Persons-DetectedFace_Photossqlite_Query – 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.

PENDING iOS<**>_LPL_Assets_Mom-Mem-PHighlight_Photossqlite_Query – 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.

PENDING iOS<**>_LPL_Assets_ScenePrint-Classification_Photossqlite_Query – This query can be used to view the basic data for the assets, the Screen Print data, and Scene Classification data.

PENDING iOS<**>_LPL_Assets_Change-Transactions_Photossqlite_Query – This query can be used to view the basic data for the assets with the Change data, Transaction and Transaction String data.

SQLite Query WHERE statements

These main/large queries, can be used to query the data based on several WHERE statements. Using these WHERE statements allow you to narrow the results to an artifact 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 3, which indicates the asset is a Digital Camera Image (DCIM) stored asset Local Photo Library asset. Assets typically with this saved asset type will be saved within a DCIM/***APPLE file path, but it’s possible to be stored in a different file system location. 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. NOTE: Use CAUTION with this artifact. If an asset has a shared date, it does not mean that the asset was in fact shared. If a user prepares to share an asset, then changes their mind, it possible the asset could have a shared date, but it was never 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 System Package Application:

WHERE zAddAssetAttr.ZIMPORTEDBY IN (8) – This statement will allow you to target the assets which have been imported to the device from system package applications.

Asset Imported to device via Native Application:

WHERE zAddAssetAttr.ZIMPORTEDBY IN (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 a Shared with You Syndication PL Syndication Identifier:

WHERE zAddAssetAttr.ZSYNDICATIONIDENTIFIER IS NOT NULL – This statement will allow you to target all assets that have a syndication identifier and could be related to Apple messages application as an attachment.

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!