Photos.sqlite, the iOS elephant in the room of mobile forensic vendors that is absolutely chock full of interesting information yet is completely ignored. Why is this? One possible reason is that the database can be hard to work with due to table names that vary from device to device.

To answer this question, I’m bringing out the big guns – colleague and friend Shafik G. Punja – who has been working in the digital forensics field for the last 15 years and counting. Shafik’s meticulous nature and extensive knowledge and experience is unmatched and so is the perfect person to ask about this subject.

Forensicmike: What prompted you to investigate Photos.sqlite?

Shafik: I was working on an already acquired iPhone 8 on iOS 12.1.2 where the device owner had placed the images of interest in an album called “Evidence”. The guy who did the acquisition, a blogger who shall not be directly named but rhymes with forensicbike, did not check before releasing the device itself. For most if not all major forensic tools, there is no way to tell which camera roll items belong to an album. This means there is a possibility that for every single instance of a video or photo on the file system there may be membership to one or more albums. Some albums are user created while others are automatically created by iOS such as ‘Screenshots’ or even third party applications.

Forensicmike: I don’t know who you could be referring to! So… could you summarize the purpose of Photos.sqlite?

Shafik: This SQLite database file contains information about videos and photos stored on an iOS device. Photos.sqlite contains everything from album ownership, to internal photo analysis and GPS and much more. At the time of writing, this database must be manually examined in order to identify the existence of albums which can contain photos or videos.

Forensicmike: Shaf… there’s nearly 70 tables in this beast. Which of them are actually needed?

Shafik: All of them! Just kidding. The primary ones you need to focus on for the purpose of album identification and media correlation are ZGENERICALBUM, the mystery Z_##ASSETS table, and the ZGENERICASSET table. There are a handful of other interesting ones that I’ll provide a list for at the end but those are the main ones. The mystery number is the oddest and seems to vary from device to device. It is also probably the number one reason the forensic tools are staying far away from this otherwise easy target of a database. Keep in mind this is for iOS 12.1 and could be different in future/previous versions.

Forensicmike: Okay, so where do we start?

Shafik: If you know the album you’re interested in as I did in my case, start with ZGENERICALBUM. The ZTITLE column is where you’re headed. You can also note the ZCACHEDPHOTOSCOUNT and ZCACHEDVIDEOSCOUNT for a photo/video count – handy!

So in this case we can see there are 4 items anticipated all of which are photos. At this point you should also note the Z_PK — in this case it was 47 — as this is our primary key that we will cross reference in table number 2 – the mystery number table. On this device, it was Z23_ASSETS. So, step 2 is to check Z23_ASSETS (or Z##_ASSETS) for your Z_PK of interest.

So we can see there are four items of interest in the Z_23ASSETS table if we filter by Z_23ALBUMS=47 (the Z_PK from ZGENERICALBUM), specifically 4490, 4491, 4492, 4493.

Now we have one final table to bring into the mix… ZGENERICASSET. Take your values of interest and search for them as either RowID or Z_PK:

Now all that’s left is to scroll to the right…keep scrolling…. never give up scrolling…. and….victory!

Forensicmike: Interesting… all PNG files – probably suggesting these could be screenshots if I recall from FOR585?

Shafik: Yes! They were screenshots – but that’s not the only thing you can understand from the querying we’ve done so far. Take a look at the ZDATECREATED for a UTC based creation time, and ZADDEDDATE for a ‘added to album time’ – something that would be utterly missing from an analysis within an industry tool. And we’re barely scratching the surface here.

Forensicmike: Just confirming, you actually found these images in DCIM/104APPLE right?

Shafik: Yup – exactly where the database said they would be!

Forensicmike: Awesome.. any other data points you’d like to draw attention to?

Shafik: Of course. Here is a quick non-exhaustive list of some highlights I found. Keep in mind these are specific to iOS 12.1.2.

Forensicmike: What’s the best way for people to reach you if they have questions?

Via Twitter (@qubytelogic) is fine – my direct messages are open.

Thanks a lot Shafik! And thank you readers. If there is interest, perhaps we can move forward on this subject by investigating the use of fuzzy SQL querying to programmatically go….