Mobile Forensics · Where the Evidence Lives · Lesson 1 of 5

SQLite: The Phone's Favorite Database

Open an app's database and see why deleted rows sometimes come back.

Tables, rows and a missing message

Open almost any app on a phone and the data behind it lives in a database: an organized file that stores information so the app can find it quickly. On both iPhone and Android, the most common choice is SQLite, a small database engine that keeps a whole database in a single ordinary file. The SQLite project says it runs on every Android device and every iPhone, and that a typical smartphone holds hundreds of SQLite database files.

Why do app makers like it? It's free, it's built into both operating systems, and it doesn't need a separate server. The app just opens a file, often ending in .db or .sqlite, and reads or writes it.

Inside, the data is arranged like a spreadsheet:

• A table holds one kind of thing, such as messages, contacts or call history.
• Each row is one item, like a single message.
• Each column is one detail about that item, like who sent it, the text, and when.

Many tables give every row an id number that counts up as new rows are added. For an examiner, that simple counter is useful. If the ids run 41, 43, 44, something that used to be number 42 may have been deleted. A gap isn't proof on its own (apps can skip numbers for other reasons), but it's a good reason to look closer.

Examiners usually work on a copy of the database, opened in a read-only viewer, so nothing in the evidence changes. That matters for SQLite in particular, because just opening a database the normal way can tidy up its files, as you'll see in the next task.

On the simulated phone, look at the messages table and note which id is missing. Then turn on Show records recovered from the WAL and free pages and read the row that comes back.

The WAL, free pages and why luck matters

To understand why a deleted message can come back, it helps to know how SQLite stores things on disk. A database file is split into fixed-size blocks called pages. Rows live inside pages.

When an app deletes a row, SQLite usually doesn't wipe it. It marks the space as free. If a whole page becomes empty, the page goes on the freelist, a list of free pages that SQLite will reuse for new data later. Until that reuse happens, the old bytes can still be sitting there. SQLite's own documentation calls these leftovers forensic traces.

Many apps also use write-ahead logging, or WAL. In WAL mode, changes aren't written straight into the main file. They're added to a separate file next to it with -wal on the end of its name. Each entry in the WAL is a full copy of a changed page. Every so often SQLite runs a checkpoint that copies those pages back into the main file. By default this happens when the WAL reaches about 1,000 pages. Afterward, SQLite starts overwriting the WAL from the beginning. So for a while, the WAL and the main file can hold older and newer versions of the same page, and an old version may still contain a deleted row.

That's also why an examiner collects the -wal file along with the database. Leave it behind and recent changes, or recoverable old ones, may be missed. It's also why they use read-only tools on copies: when the last program using a database closes it normally, SQLite usually checkpoints and deletes the -wal file.

Recovery is never guaranteed, though. Deleted data disappears when:

• New data reuses the free space, or the WAL is overwritten after a checkpoint.
• The app runs VACUUM, a command that rebuilds the whole database file. SQLite says this cleans out all traces of deleted content.
• The app turns on secure delete, a setting that makes SQLite overwrite deleted content with zeros. It's normally off, because it costs speed.

So a recovered row is a bonus, and an absent one proves nothing either way.