How to query data from iPhone backup SQLite and Plist files

Some users may wish to directly manipulate the raw files stored in their backups using expert mode. These are often comprised of SQLite database (.sqlite3, .sqllitedb and .db) or Plist (usually .plist) files.

iPhone Backup Extractor includes a built-in Plist editor, and its users are able to automatically export their data to PDF, HTML, VCard, ICAL, VCF or CSV formats for easy access. However, users wishing to extract data from these files will need a SQLite database client.

How can I work with SQLite databases?

Users might want to try any of these three applications:

• SQLiteFlow (paid, macOS & iOS). SQLiteFlow is our macOS SQLite tool of choice.
• Base 2 (paid, macOS). Base is one of our second-favourite SQLite tools for macOS, and is available as a direct download and App Store app.
• DB Browser for SQLite (free, macOS & Windows). DB Browser used to be named the SQLite Database Browser. It's our recommended tool for Windows, and it's free. They also provide a version for macOS.

How can I work with Apple's database files directly?

As an example, to manually access contact data, users should extract Libray/AddressBook/AddressBook.sqllitedb, and then open the extracted file with a SQLite viewer. Choosing the "Execute SQL" tab, paste this into the "SQL string" field:

SELECT ABPerson.first, ABPerson.last, ABMultiValue.value
FROM ABPerson, ABMultiValue
WHERE ABMultiValue.record_id = ABPerson.ROWID

Pressing the Execute Query button should return the full contact list shown in the "Data returned" field.

To access SMS data, users should extract Libray/SMS/SMS.db, open the extracted file with the SQLite viewer, choose the Browse data tab, and then select message from the Table drop-down. On clicking the magnifying glass button, users should see their full SMS history shown in the "Data returned" field.

One of our users found the query below helpful, so we're including it here for our users. Bear in mind that database formats change between iOS versions, so these queries often need to change over time.

SELECT ROWID,
  ABPerson.first,
  ABPerson.last,
  ABPerson.Organization AS organization,
  ABPerson.Department AS department,
  DATETIME(ABPerson.Birthday + STRFTIME('%s', '2001-01-01 00:00:00'), 'unixepoch', 'localtime') AS Birthday,
  ABPerson.JobTitle as jobtitle,
  ABPerson.Organization,
  ABPerson.Department,
  ABPerson.Note,
  ABPerson.Nickname,
  DATETIME(ABPerson.CreationDate + STRFTIME('%s', '2001-01-01 00:00:00'), 'unixepoch', 'localtime') AS Created,
  DATETIME(ABPerson.ModificationDate + STRFTIME('%s', '2001-01-01 00:00:00'), 'unixepoch', 'localtime') AS Modified,
  ( SELECT value FROM ABMultiValue
    WHERE property = 3 AND record_id = ABPerson.ROWID AND label = (
      SELECT ROWID FROM ABMultiValueLabel
      WHERE value = '_$!<Work>!$_'
    )
  ) AS phone_work,
  ( SELECT value FROM ABMultiValue
    WHERE property = 3 AND record_id = ABPerson.ROWID AND label = (
      SELECT ROWID FROM ABMultiValueLabel
      WHERE value = '_$!<Mobile>!$_'
    )
  ) AS phone_mobile,
  ( SELECT value FROM ABMultiValue
    WHERE property = 3 AND record_id = ABPerson.ROWID AND label = (
      SELECT ROWID FROM ABMultiValueLabel
      WHERE value = '_$!<Home>!$_'
    )
  ) AS phone_home,
  ( SELECT value FROM ABMultiValue
    WHERE property = 4 AND record_id = ABPerson.ROWID AND label IS null
  ) AS email,
  ( SELECT value FROM ABMultiValueEntry
    WHERE parent_id IN (
      SELECT ROWID FROM ABMultiValue WHERE record_id = ABPerson.ROWID
    ) AND key = (
      SELECT ROWID FROM ABMultiValueEntryKey WHERE lower(value) = 'street'
    )
  ) AS address,
  ( SELECT value FROM ABMultiValueEntry
    WHERE parent_id IN (
      SELECT ROWID FROM ABMultiValue WHERE record_id = ABPerson.ROWID
    ) AND key = (
      SELECT ROWID FROM ABMultiValueEntryKey WHERE lower(value) = 'city'
    )
  ) AS city
FROM ABPerson
ORDER BY ABPerson.first

We maintain a list of key files for various apps here.

The date and time fields just come back as numbers, how can I interpret them?

You may struggle to interpret a number of fields in the databases. Don't forget, iPhone Backup Extractor can extract the data automatically for you! However, if you're going it alone, you should find the dates are represented as either the number of seconds since 2001-01-01, or 1970-01-01, depending on the position of the date field. In Excel, you could use a formula such as =1/1/2001 + A1/60/60/24 where A1 is your time data. You may also need to adjust the value to account for your local timezone.