A quick post about dealing with super old iphone backups easily, mostly so that I have this available whenever I need it in the future.

Whilst commercial tools are available to assist in the process of iPhone backup analysis and are especially helpful when analysing corrupt or partially overwritten data, not everyone has access to these. There are also many free tools that can automate the recovery of data from iPhone backups such as iLEAPP which are awesome, and some that are slightly less reliable in their output.

However, the older the iphone backup the less reliable these tools becomes, with many being completely incompatible. Other tools that work with older versions lack the level of control and granularity that I prefer to ensure that my findings are transparent, accurate, reliable, and most importantly, easily reproducible.

For these reasons, I have sometimes found it necessary to perform manual analysis by querying and exporting data directly from the individual databases using SQLite.

The book Practical Mobile Forensics (Tamma, Skulkin, Mahalik, & Bommisetty, 2020) is a fantastic resource for getting acquainted with the interior workings of an iPhone backup directory. When examining an iPhone backup manually you’ll find that the the backup directory contains four standard files, along with individual files (up to iOS 9) or folders (iOS 10 and newer).

Of these standard files, Info.plist and Manifest.db contain the information needed to determine hardware and software versions, as well as the 40 digit fileID of each individual database.

The fileID is a SHA1 hash of the domain concatenated with a - symbol and the file or folder relative path. For example, ae94e0607ca39a88c18aca095cb5b4f8471291a0 is the SHA1 hash for CameraRollDomain-Media/PhotoData/Thumbnails/V2/DCIM/102APPLE. Although on newer iPhone models, you can query Manifest.db to find the fileID of databases, this is not supported on iPhone 4 or 4s!

Some of the databases that you might want to manually query for analysis are: a. HomeDomain-Library/SMS/sms.db (Messages in all iOS versions) b. WirelessDomain-Library/CallHistory/call_history.db (Call Log prior to iOS 8.0) c. HomeDomain-Library/CallHistoryDB/CallHistory.storedata (Call log introduced with iOS 8.0).

Timestamps found on iOS devices are presented either in the Unix Timestamp or Mac absolute time format. A Unix timestamp is the number of seconds that have elapsed since Unix epoch time, which started at midnight on January 1, 1970. iOS devices adopted the use of Mac absolute time with iOS 5. Mac absolute time is the number of seconds that have elapsed since Mac epoch time, which started at midnight on January 1, 2001. The difference between the Unix epoch time and the Mac time is exactly 978,307,200 seconds.

Online tools like EpochConverter Cocoa Core can do this for you - look for “Cocoa Core” or “Core Data” conversions as well. You can also do this manually with the date command which will automatically be able to determine if daylight savings was enabled during the epoch time stamp.

#!/bin/bash

read -p "Enter the Mac absolute timestamp: " input_timestamp
offset=978307200
adjusted_time=$((input_timestamp + offset))

echo ""
echo "Unix Timestamp: $adjusted_time"
echo "UTC Time: $(date -ud @$adjusted_time +'%Y-%m-%dT%H:%M:%SZ')"
echo "(NSW/VIC/ACT):     $(TZ=Australia/Sydney date -d @$adjusted_time +'%Y-%m-%dT%H:%M:%S%:z')"

You can browse the databases manually using sqlite3. Depending in iOS version, the fields and database names change drastically.

iOS 12 / iPhone 6 calls:

sqlite3 ./5a/5a4935c78a5255723f707230a451d79c540d2741 \n
 "SELECT zdate, zoriginated, zaddress, zduration from zcallrecord" -header -csv > calls.csv

iOS 12 / iPhone 6 SMS:

sqlite3 ./3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28 \n
"SELECT account, text, is_from_me, date from message" -header -csv > SMS.csv

iOS 7.1.2 / iphone 4 calls:

sqlite3 ./2b2b0084a1bc3a5ac8c27afdf14afb42c61a19ca \n
"SELECT address, date, duration, flags FROM call" -header -csv > calls.csv

iOS 7.1.2 / iphone 4 SMS:

sqlite3 ./3d0d7e5fb2ce288813306e4d4636395e047a3d28 \n
"SELECT account, text, is_from_me, date from message" -header -csv > SMS.csv

iOS 9.3.5 / iPhone 4s calls:

sqlite3 ./5a4935c78a5255723f707230a451d79c540d2741 \n
"SELECT zdate, zoriginated, zaddress, zduration from zcallrecord" -header -csv > calls.csv

iOS 9.3.5 / iPhone 4s SMS:

sqlite3 ./3d0d7e5fb2ce288813306e4d4636395e047a3d28 \n
"SELECT account, text, is_from_me, date from message" -header -csv > SMS.csv