The Swipe the Delete Left Behind: SQLite Freeblock Forensics

Digital Forensics & IR Level 2/4 ~3 min November 21, 2026

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

Database forensicsHex analysisIncident response

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 DENIED at the server room looks the most suspicious, but both of her rows are live: the pointers 0x11B and 0xE3 lead 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 A6 is offset 0xA6; 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 second 00 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 DELETE then 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 -wal and -journal files before opening it, since opening and checkpointing can overwrite recoverable space.
  • PRAGMA secure_delete zeroes freed space: useful for privacy, but it is not an audit control, and it would also have destroyed this evidence.

Full solution

Pro and Max members unlock the complete step-by-step walkthrough.

Go Pro

Community stats

106 completions
77% success rate
waltermoar First blood

Related Daily Hacks

40,000+ Hackers Real labs Free
Start Hacking Free or solve today's hack, no account needed