The Photos.sqlite database tracks photos, videos, screenshots, and so on that were taken with the iOS device and the interactions with each multimedia file. For example, if the user uploads the data to cloud, this is stored here. If the user backs up her data to cloud, including multimedia, then the device must retrieve it from cloud to view the files on her device. These cloud-based multimedia files are also tracked in this database. Most importantly, multimedia files that are deleted are tracked in this database. Some of the most valuable information you will obtain pertaining to photos and videos will be found in Photos.sqlite. There are many queries for you in www.for585.com/notebook. Don’t use them this week until the Section 6 challenge. Take the time to learn the hard way during the labs and try it manually!

Let’s consider image files for this example. A photo is taken with the iPhone. The creation data is stored in the Photos.sqlite database. The user deletes this image. When this occurs, the date of deletion is tracked in the ZTRASHEDDATE, as highlighted in the slide. The state is changed from 0 (not deleted) to 1 (deleted). This is how Apple counts down the time you have remaining to recover the deleted photo.

What doesn’t make sense is what happens when that image is recovered by the user. If the user recovers the image from the deleted images directory, the ZTRASHEDDATE is cleared and the ZTRASHEDSTATE reverts back to 0. Thus, it is very difficult to state that the photo was deleted and recovered. Clear as mud, right? This is why you need to test and validate what you are reporting because the artifacts may appear one way and not be telling the full story.

The query below will make examining this database easier. It is also available in your course notebook.

Select

z_pk,

zfilename AS "File Name",

zduration AS "Duration in Seconds",

case

when ztrashedstate = 1 then "Deleted"

else "N/A"

end AS "Is Deleted",

datetime(ztrasheddate+978307200,'unixepoch','localtime') AS "Date Deleted",

datetime(zaddeddate+978307200,'unixepoch','localtime') AS "Date Added",

datetime(zdatecreated+978307200,'unixepoch','localtime') AS "Date Created",

datetime(zmodificationdate+978307200,'unixepoch','localtime') AS "Date Modified",

zdirectory AS "File Path"

from zgenericasset

The table of interest in iOS 14 changed from zgenericasset to zasset. This will be a part of Lab 4.2 in section 4.