Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 36 ยท Mobile and Cloud Forensics

Analyzing App Databases with SQLite

Learn to examine SQLite databases from phones and browsers: schema discovery, safe read-only querying, timestamp conversion, WAL files and recovering deleted rows.

Most phone apps, browsers and even operating systems store their data in SQLite, a single-file database. Chat messages, browsing history, contacts and location caches all live in .db, .sqlite or extension-less files. If you can read SQLite, you can read a large share of the evidence on a phone or computer.

The key discipline is to never query the original. Opening a database can modify it (SQLite may roll back journals or checkpoint the write-ahead log), so you analyze a hashed copy, together with its companion files.

The file and its companions

File Purpose
app.db Main database
app.db-wal Write-ahead log: recent committed changes not yet merged into the main file
app.db-shm Shared-memory index for the WAL
app.db-journal Rollback journal (older mode)

Always copy the main file and its -wal and -shm together. The WAL can hold messages that are not in the main file yet, and deleted data may still sit in it.

Every SQLite file begins with the 16-byte header SQLite format 3\0, which is how you confirm a file type even when the name is misleading.

Discover the schema

A database is self-describing. Start with the tables and columns before you read any rows. In the program below we create a small in-memory messaging database that stands in for a real app file, and then explore it exactly as you would a copy.

Python Explore the schema

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In a real case the schema also teaches you meaning: a column named direction or deleted hints at how the app marks sent versus received and soft-deleted rows.

Query and convert time

Joins and timestamp conversion do most of the work. SQLite can convert Unix time directly.

Python Join messages to contacts and decode time

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Row 3 is flagged "deleted" by the app but still present: many apps only set a flag. That is a common and valuable finding. Always report the original value and your interpretation separately.

Open copies read-only

When working with real files, open a copy with SQLite's read-only mode so nothing is written:

Terminal
# Hash first
sha256sum msgstore.db msgstore.db-wal msgstore.db-shm

# Work on a copy, read-only (URI mode)
cp msgstore.db* /cases/42/work/
sqlite3 "file:/cases/42/work/msgstore.db?mode=ro" ".tables"

Example commands; run them only on evidence you are authorized to examine.

Deleted data and free space

When a row is deleted, SQLite usually does not erase the bytes. They remain in freelist pages or unused space inside pages until reused, and sometimes in the WAL or journal. Specialized tools scan the raw pages for record structures and recover rows. Signs of possible recovered data:

  • Row IDs missing from an otherwise continuous sequence (gaps).
  • A VACUUM run by the app, which rewrites the file and removes free pages (a reason recovery fails).
  • secure_delete pragma on, which zeroes deleted content.

Gaps in an auto-increment ID are easy to find with a query.

Python Find gaps in row IDs

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A gap does not prove deletion (rows might never have been written, or lived in another table), but it tells you where to look.

Practical checklist

  1. Identify the file by header, hash it, copy it with its -wal and -shm.
  2. Dump the schema; note column meanings and timestamp formats (epochs).
  3. Query on a copy with read-only access; export results to a report table.
  4. Look for flags (deleted, is_read), gaps and orphaned rows.
  5. Record the tool, version and queries you used so another examiner can repeat them.

Use the same technique in the timeline lesson to merge message times with other sources.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why copy the -wal file together with the database?

    Write-ahead log entries are committed changes; without them you can miss recent messages or deleted data.
  2. 2. Which header identifies a SQLite 3 file?

    Every SQLite 3 database starts with the string SQLite format 3 followed by a NUL byte.
  3. 3. A message row has deleted = 1 but is still readable. What is the likely reason?

    Many apps use soft delete: they set a flag rather than removing the record.
  4. 4. What can prevent recovery of deleted rows?

    VACUUM rewrites the database without free pages and secure_delete overwrites deleted content with zeros.

Summary

  • SQLite stores most phone and browser artifacts; analyze copies only.
  • Copy the main file with its -wal and -shm, and hash everything.
  • Read the schema first, then join tables and convert timestamps.
  • Soft-delete flags, ID gaps, freelist pages and the WAL can reveal deleted data.
  • Document queries and tool versions for repeatability.

Next: Cloud Forensics and SaaS Logs