SQLite forensic analysis is the examination of SQLite database files and their transaction files to recover deleted records, interpret how data was stored, and validate what a forensic tool reported. It covers the database file, the write-ahead log, the shared memory file and rollback journals.
Browsing tables tells you what an application currently shows. Forensic analysis tells you what the file holds, which is usually more, and lets you say where a value came from.
Because there are millions of applications and a finite number of people writing parsers. A new app becomes popular before support exists, or a supported app updates its schema and the parser stops matching what it now writes.
This is documented by the vendors themselves.
"With the millions of applications available to users on Android devices, it becomes impossible for commercial tools to be able to parse and support them all. However, analyzing unsupported applications can sometimes be critical to your digital forensics investigations."
Magnet Forensics, Supporting the UnsupportedThere is a second and quieter case. An application is supported, the parser runs, and the result is subtly wrong. A field read with the wrong epoch, a status flag interpreted from an older app version, one table decoded and its companion left alone. Nothing announces the error.
Related: What to Look for in a SQLite Forensic Tool and SQLite Forensics Explained
SQLite removes the pointer to the record and adds its space to the page's freeblock chain. It does not overwrite the content. Four bytes of bookkeeping are written at the start of the freed space, and those four bytes are the ones that matter.
Here is a real row. Table messages, rowid 7, sender Anna, body meet me at nine.
17 07 04 00 15 2B 41 6E 6E 61 6D 65 65 74 20 6D
65 20 61 74 20 6E 69 6E 65 | Bytes | Meaning |
|---|---|
17 | Payload length, 23 bytes |
07 | Rowid 7 |
04 | Record header is 4 bytes long |
00 | Column 1 is NULL, the rowid alias |
15 | 0x15 is 21, so (21 - 13) / 2 gives a 4 character TEXT value |
2B | 0x2B is 43, so (43 - 13) / 2 gives a 15 character TEXT value |
41 6E 6E 61 | Anna |
6D 65 ... 65 | meet me at nine |
Now delete it. There is no next freeblock, so the first two bytes are 00 00. The block is 25 bytes, so the next two are 00 19.
00 00 00 19 15 2B 41 6E 6E 61 6D 65 65 74 20 6D
65 20 61 74 20 6E 69 6E 65 The text is untouched. Anna is still there. meet me at nine is still there. What is gone is the payload length, the rowid, the header size and the first serial type.
You recover the message and read it perfectly, and you cannot say which row it was. Without the rowid you cannot join it back to a conversation table, order it against its neighbours, or connect it to a reference elsewhere in the schema. You have content without identity.
It also explains a result that looks like a bug the first time you meet it. A carved record whose field count does not match the schema, or whose first column has vanished. That is the first serial type sitting underneath a freeblock header.
An emptied page leaves the b-tree and joins the freelist. SQLite records the page number and does not clear the page. Records survive complete, headers and rowids intact, until the page number is allocated again.
Large values spill into overflow pages. Deleting the record frees the chain and the content remains until reuse, so a long message can survive in full when its parent record is damaged.
The gap between the end of the cell pointer array and the start of the cell content area is supposed to be empty. After a page reorganises, it frequently is not.
The mechanism above. Content survives with the first four bytes replaced, which is why in-page recovery returns records with the front removed.
Four places, and the mechanism tells you how much of the record to expect. This is why freelist recovery and in-page recovery feel so different. One returns whole records. The other returns records with the front removed. Same data, different mechanism.
VACUUM rebuilds the database into a new file and copies only live content. Freeblocks, freelist pages, fragments and slack are not overwritten. They are never copied. auto_vacuum does the same job incrementally, and secure_delete zeroes cell content as it frees it.
PRAGMA journal_mode; PRAGMA auto_vacuum; PRAGMA freelist_count;
Nothing found in a database with no vacuum, a healthy freelist and a WAL full of frames is weak evidence of absence. Nothing found in a database vacuumed last Tuesday is a much stronger statement, because you can name the mechanism that removed it.
Complete earlier versions of pages, in the order they were written. In WAL mode the database file is deliberately not kept current. Each modified page is appended to the -wal file as a full page image, and the database catches up at a checkpoint. A page changed ten times therefore exists on disk as ten complete versions.
Each frame carries a 24 byte header.
| Offset | Size | Meaning |
|---|---|---|
0 | 4 | Page number |
4 | 4 | Database size in pages, if this frame commits. Otherwise zero. |
8 | 4 | Salt-1, copied from the file header |
12 | 4 | Salt-2, copied from the file header |
16 | 8 | Two running checksums |
The field at offset 4 is zero on almost every frame. When it is not, that frame ends a transaction, and it is the only structural marker of a transaction boundary in the file.
The salts are the recovery mechanism. When a WAL is checkpointed and restarts, both change. Frames further down the file keep the previous pair, which makes them invisible to SQLite and perfectly readable to an examiner. Those are page images from before the last checkpoint.
There is no timestamp anywhere in the format. Frame order is the order SQLite flushed pages, and one user action can write four frames while a background task writes frames in between. Read it as a sequence of states, not as a clock.
A normal read-write connection can checkpoint the WAL when it closes, and depending on journal mode it can remove the -wal and -shm files entirely. Copy all three together and work on the copy. Nothing warns you when this goes wrong.
Related: SQLite Forensic Validation: When a Viewer Is Not Enough
By checking it against the source rather than accepting the conversion. Timestamps are the clearest case, and the trap is precise.
Unix time counts from 1 January 1970. Mac Absolute Time, used across Core Data and NSDate, counts from 1 January 2001. The gap is 978,307,200 seconds, which is exactly 11,323 days, or exactly 31 years including the eight leap days between them.
Because the offset is a whole number of days, a Mac Absolute value read as Unix lands on the same month, the same day of month and the same clock time, 31 years earlier.
| Format | Epoch | Unit | Shape today |
|---|---|---|---|
| Unix seconds | 1970-01-01 | s | 10 digits, starts 17 |
| Unix milliseconds | 1970-01-01 | ms | 13 digits |
| Mac Absolute / Cocoa | 2001-01-01 | s | 9 digits, starts 7 |
| WebKit / Chrome | 1601-01-01 | µs | 17 digits, starts 13 |
| Windows FILETIME | 1601-01-01 | 100 ns | 18 digits |
Counting digits narrows the field before any conversion. A 9 digit integer in an iOS app is very unlikely to be Unix seconds, because Unix seconds has not been 9 digits since 2001.
Then decode the whole column, not the one row you care about. One interpretation spreads the values across the period the device was in use. The other puts half of them somewhere impossible. Bound the result against the install date, the acquisition date, a file modification time or another table recording the same event. Two independent sources agreeing under one epoch is corroboration. One value decoded alone is an assumption.
Protobuf varints are not SQLite varints. They share a name and reverse the byte order, so applying the wrong rules produces a plausible wrong number rather than an obvious failure. More in The Varint Trap.
It depends on the quality of the search that produced it. Research published by the Center for Statistics and Applications in Forensic Evidence concludes that a failure to find a trace counts as evidence of absence in proportion to how likely the search was to have detected it had it been present.
Two sentences that read identically in a report are not the same finding. "Nothing further was found." and "I examined the freeblock chain, the freelist and the write-ahead log, and this database was vacuumed on the fourteenth." Only the second one can be reviewed by anyone else.
Firefly is a forensic workspace for the analysis that starts after extraction. It opens SQLite, encrypted SQLite, RealmDB, LevelDB, IndexedDB and Apple Biome / SEGB in one place, so moving between formats does not mean leaving the case.
Recovery is a first-class view rather than a side feature. Records from WAL frames, rollback journals, freelist pages and freeblocks appear alongside live records, each labelled with the mechanism that surfaced it. Timestamps are ranked by confidence across 17 formats instead of one being selected quietly. Nested content unwraps recursively, so a base64 plist inside a gzipped Protocol Buffer does not need an export at every layer.
Where the data supports it, a finding keeps its source location, the file, the page and the byte offset, and that context carries into an interactive report that opens without Firefly. Evidence handling is read-only throughout, with SHA-256 evidence-set validation, and everything runs locally.
Firefly is not an extraction tool. It starts where decoded output stops and assumes you keep the platform you already use. SQLite Visualizer is now part of Firefly.
Every recovered record is labelled with the structure it came from.
Ranked by confidence rather than silently converted.
Nested plists, gzip and Protocol Buffers unwrapped in place.
SHA-256 evidence-set validation, local processing throughout.
More on the reasoning in Why Provenance Matters After Extraction and SQLite, RealmDB, LevelDB and IndexedDB in Modern App Investigations.
The techniques on this page are format knowledge rather than tool knowledge, and they transfer to whatever platform is on your desk.
Certified SQLite Forensics covers them across 24 hours: file headers, page structure, records, serial types and varints, B-tree navigation, freelists, freeblocks and overflow pages, WAL and SHM analysis, deleted record recovery, and validation of incomplete or unsupported output. No coding required, and it is written for investigators rather than developers.
The on demand version is guided rather than passive. You download real SQLite, WAL and SHM training files and work on them in your own tools, complete byte-level exercises after each concept, and test the technique through CTF style challenges. 24 CPE credits, certificate of completion, three months of access.
Last updated 5 September 2026
Continue through related SQLite investigation workflows and connect this topic back to the main forensic tool.