The Swipe the Delete Left Behind: SQLite Freeblock Forensics
The challenge
A row vanished from Brackwater's door log. Find where SQLite left the deleted row. Name the person whose badge it logged.
What you'll learn
- Read an SQLite b-tree page header: page type, first freeblock, cell count, content start
- Follow a big-endian offset to a freeblock in a hex dump
- Tell live rows from deleted ones with the cell pointer array
- Explain why DELETE leaves data behind and what finally removes it
- Protect an audit log against local tampering
Skills tested
Prerequisites
- Reading hexadecimal
- What a row in a database table is
How it works
An SQLite file is a sequence of fixed-size pages, and a table's rows live in leaf pages (type 0D). Each leaf page starts with an 8-byte header, followed by the cell pointer array: one 2-byte offset per row. The rows themselves (cells) are packed from the end of the page backwards.
A DELETE is bookkeeping, not erasure. SQLite removes the row's pointer, lowers the cell count, and hands the row's bytes to the page's freeblock list, a chain that starts at header offset 1. To link the block it overwrites only the first 4 bytes of the old row (next freeblock, size). Everything after that, the values included, stays on disk until a later insert reuses the space or a VACUUM rewrites the file. With the default secure_delete off, nothing is zeroed.
That is why deleted text messages, browser entries and app logs are routinely recovered by carving freeblocks, freelist pages and the write-ahead log (WAL), by hand or with tools such as FQLite and undark. The same works against anyone who edits a log to hide their own entry.
Common mistakes
- Answering Kata Imre. Her
DENIEDat the server room looks the most suspicious, but both of her rows are live: the pointers0x11Band0xE3lead to them. Nobody deleted them. - Answering Tomas Brandt. He was in the server room that night, and it is on the log for everyone to see: he is the on-call engineer who arrived after the fact. The stale pointer after the array leads to his row too, which proves nothing about a deletion.
- Picking by elimination. Every name on the page appears twice, Halloway included: his 19:12 lobby swipe is a live row. Only the freeblock tells you which record was removed.
- Reading the pointer little-endian.
00 A6is offset0xA6; read backwards it would point far past the end of a 512-byte page. - Counting the extra
00 30. After the 7 live pointers sits a second00 30: a stale copy left when the array shifted down. The cell count says 7, so stop there. - Expecting a clean record. The deleted row's first 4 bytes now hold the freeblock header, so its length and row id are gone. The values that follow are still readable.
How to defend against it
- Forward every access event off the controller as it happens (syslog or a SIEM): a local
DELETEthen cannot remove the copy that matters. - Separate duties: whoever administers the door system should not be able to edit its event table, and every admin action should itself be logged elsewhere.
- Alert on gaps in event ids or sequence numbers, and on database writes that are not inserts.
- For investigators: image the database together with its
-waland-journalfiles before opening it, since opening and checkpointing can overwrite recoverable space. PRAGMA secure_deletezeroes freed space: useful for privacy, but it is not an audit control, and it would also have destroyed this evidence.