Parsing iOS Camera Roll using Python

I was asked how some images of interest came to be on an iOS camera roll which started me off doing some research into the camera roll. I came across this great blog by forensicmike1 which contained lots of useful info. There is so much information in the Photos.sqlite database!

The database can be found here:

\private\var\mobile\Media\PhotoData\Photos.sqlite

I decided to try to write a parser to extract some of my fields of interest from Photos.sqlite. I'm a beginner with Python and SQL so I thought this would be a good lock-down project as it would undoubtedly take me ages...

For this project I used iOS 13 test data from The Binary Hick

My python script is available here.

The info I decided I wanted to extract for each item on the camera roll was:

• Z_PK
• ZDIRECTORY
• ZFILENAME
• ZORIGINALFILENAME
• ZDURATION
• ZFAVORITE
• ZCREATORBUNDLEID (very useful for my original question!)
  
• ZEDITORBUNDLEID
• ZADDEDDATE
• ZDATECREATED
• ZMODIFICATIONDATE
• ZEXIFTIMESTAMPSTRING
• ZTRASHEDSTATE
• ZTRASHEDDATE
• ZTIMEZONENAME
• ZTIMEZONEOFFSET
• ZLATITUDE
• ZLONGITUDE

This info comes from within the following tables:

• ZGENERICALBUM
• ZGENERICASSET
• ZADDITIONALASSETATTRIBUTES
• with information needed to link the other tables in Z_26ASSETS

The table Z_26ASSETS (and the required fields within it: Z_34ASSETS and Z_26ALBUMS) pose a bit of a problem because the digits in the names differ between devices and, as far as I know, no one knows why...

In the end getting all the info I wanted required 5 SQL queries:

1- A query to identify how the Z_##ASSETS table is named in the chosen database:

"select name from sqlite_master where name like 'Z___ASSETS'"

2- A query to identify how the field Z_##ASSETS field is named within the Z_##ASSETS table :

"select name from PRAGMA_table_info(%s) where name like 'Z___ASSETS'"

3- A query to identify how the field Z_##ALBUMS is named within the Z_##ASSETS table:

"select name from PRAGMA_table_info(%s) where name like 'Z___ALBUMS'"

4- A query to find out which album, if any, an item is in:

'select A1.ZTITLE,\
                    A1.ZCREATIONDATE as CreationDate,\
                    A1.ZCACHEDPHOTOSCOUNT as PhotoCount, \
                    A1.ZCACHEDVIDEOSCOUNT as VideoCount, \
                    A1.ZSTARTDATE as StartDate,\
                    A1.ZENDDATE as EndDate,\
                    A1.ZPARENTFOLDER as ParentFolderID, \
                    A2.ZTITLE as ParentFolderName \
                    FROM ZGENERICALBUM A1 \
                    LEFT OUTER JOIN ZGENERICALBUM A2 on A1.ZPARENTFOLDER = A2.Z_PK'

5- A query which combines everything:

'SELECT \
            ZADDITIONALASSETATTRIBUTES.Z_PK,\
            ZGENERICASSET.ZDIRECTORY,\
            ZGENERICASSET.ZFILENAME,\
            ZADDITIONALASSETATTRIBUTES.ZORIGINALFILENAME,\
            ZGENERICASSET.ZDURATION,\
            ZGENERICASSET.ZFAVORITE,\
            ZADDITIONALASSETATTRIBUTES.ZCREATORBUNDLEID,\
            ZADDITIONALASSETATTRIBUTES.ZEDITORBUNDLEID,\
            ZGENERICASSET.ZADDEDDATE,\
            ZGENERICASSET.ZDATECREATED,\
            ZGENERICASSET.ZMODIFICATIONDATE,\
            ZADDITIONALASSETATTRIBUTES.ZEXIFTIMESTAMPSTRING,\
            ZGENERICASSET.ZTRASHEDSTATE,\
            ZGENERICASSET.ZTRASHEDDATE,\
            ZADDITIONALASSETATTRIBUTES.ZTIMEZONENAME,\
            ZADDITIONALASSETATTRIBUTES.ZTIMEZONEOFFSET,\
            ZGENERICASSET.ZLATITUDE,\
            ZGENERICASSET.ZLONGITUDE,\
            Album_Membership.Album_Name as "Album Name"\
        FROM ZADDITIONALASSETATTRIBUTES INNER JOIN ZGENERICASSET\
        ON ZADDITIONALASSETATTRIBUTES.Z_PK=ZGENERICASSET.Z_PK\
            LEFT JOIN (SELECT\
                ZGENERICALBUM.ZTITLE as Album_Name,\
                %s.%s as Z_PK\
                FROM ZGENERICALBUM \
                JOIN %s \
                ON ZGENERICALBUM.Z_PK=%s.%s \
                JOIN ZGENERICASSET \
                ON %s.%s=ZGENERICASSET.Z_PK) AS Album_Membership \
                ON Album_Membership.Z_PK = ZADDITIONALASSETATTRIBUTES.Z_PK'

For my input I decided to try to build a GUI because I've never done one before and this is what I ended up with:

It's not very snazzy but it does the job. When I have a bit more time I'd like to go back and tweak this a bit further- maybe widen the text boxes, change the window name and give it a nice icon?

The results write out to csv- here's what I got out of my test data:

Camera Roll Details:

Additional Album Info (if requested with the radio button)

The timestamps you see in this output use the Mac Cocoa timestamp format. I've previously written a converter for these in python so, when I get access to my work machine back, I plan to use that to convert the timestamps.

I noticed that AXIOM recently started including "Photo Albums" and "Photos Media Information" artifacts for iOS camera roll.

The following examples show how some of this data looks in AXIOM 4.0.0. The data is taken from the iOS full file system provided for the Magnet Virtual Summit CTF:

There are quite a few fields that I find useful that are not yet parsed (such as original file name, creator bundle ID, editor bundle ID, trashed state, trashed date, which items are favourited and which album a picture is in) so hopefully I will continue to get some good use out of my script!