Como consultar dados de arquivos SQLite e Plist de backups do iPhone

Alguns usuários podem querer manipular diretamente os arquivos brutos guardados nos backups usando o modo especialista. Eles costumam ser bancos de dados SQLite (.sqlite3, .sqllitedb e .db) ou arquivos Plist (geralmente .plist).

O iPhone Backup Extractor inclui um editor de Plist integrado, e seus usuários podem exportar os dados automaticamente para os formatos PDF, HTML, VCard, ICAL, VCF ou CSV para facilitar o acesso. No entanto, quem quiser extrair dados desses arquivos precisará de um cliente de banco de dados SQLite.

Como posso trabalhar com bancos de dados SQLite?

Você pode experimentar qualquer um destes três aplicativos:

• SQLiteFlow (pago, macOS e iOS). O SQLiteFlow é a nossa ferramenta SQLite preferida para macOS.
• Base 2 (pago, macOS). O Base é uma das nossas segundas ferramentas SQLite favoritas para macOS e está disponível como download direto e como app da App Store.
• DB Browser for SQLite (gratuito, macOS e Windows). O DB Browser se chamava SQLite Database Browser. É a ferramenta que recomendamos para Windows, e é gratuita. Há também uma versão para macOS.

Como posso trabalhar diretamente com os arquivos de banco de dados da Apple?

Como exemplo, para acessar manualmente os dados de contatos, os usuários devem extrair Libray/AddressBook/AddressBook.sqllitedb e abrir o arquivo extraído com um visualizador de SQLite. Escolhendo a guia “Executar SQL”, cole isto no campo “String SQL”:

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

Ao pressionar o botão Execute Query, a lista completa de contatos deve aparecer no campo “Dados retornados”.

Para acessar os dados de SMS, os usuários devem extrair Libray/SMS/SMS.db, abrir o arquivo extraído com o visualizador de SQLite, escolher a guia Browse data e selecionar message no menu suspenso Table. Ao clicar no botão com a lupa, os usuários devem ver todo o histórico de SMS no campo “Dados retornados”.

Um dos nossos usuários achou a consulta abaixo útil, então a incluímos aqui para os demais. Lembre-se de que os formatos de banco de dados mudam entre as versões do iOS, então essas consultas muitas vezes precisam mudar com o tempo.

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

Mantemos aqui uma lista de arquivos importantes de vários apps.

Os campos de data e hora só aparecem como números. Como posso interpretá-los?

Pode ser difícil interpretar vários campos dos bancos de dados. Não se esqueça de que o iPhone Backup Extractor pode extrair os dados automaticamente para você! No entanto, se você estiver fazendo isso por conta própria, as datas são representadas como o número de segundos desde 2001-01-01 ou 1970-01-01, dependendo da posição do campo de data. No Excel, você pode usar uma fórmula como =1/1/2001 + A1/60/60/24, em que A1 são os seus dados de hora. Talvez também seja necessário ajustar o valor para levar em conta o seu fuso horário local.