Lion Cipher Lab
Recover deleted rows from a SQLite WAL and freelist (forensic carving)
- Digital Forensics & IR (DFIR)
- Intermediate
- ~60 min
- Free · no account required
Why it matters
SQLite is the most deployed database on earth: it is inside your web browser's history, your phone's text and chat apps, and countless desktop programs. When a suspect 'deletes' messages, SQLite usually does not actually erase them -- the bytes linger in the file's unused space, in freed pages, and in the -wal journal. Knowing how to carve those rows back out, by reading the file format directly instead of trusting a SELECT, is a core digital-forensics skill. It also teaches the discipline that keeps evidence admissible: work on a copy, and never let the database quietly checkpoint or VACUUM…
What you'll learn
- reading a SQLite database as raw bytes from the OUTSIDE -- parsing the 100-byte file header for the page size, the freelist trunk page, and the freelist page count -- without ever running a SQL query
- understanding why a DELETE in SQLite erases almost nothing: it unlinks a b-tree cell (or writes a fresh page image) but leaves the old row bytes sitting in freeblocks, freelist pages, and -wal frames until something overwrites them
- parsing the -wal (write-ahead log) sidecar by hand: the 32-byte WAL header, then each 24-byte frame header plus one page image, so you can pull the pre-delete copy of a page straight out of the log
- walking the freelist: following the trunk page's next-pointer and leaf-page array to list exactly which pages are free, then carving the deleted records those pages still hold
- the measured reality that WAL recovery can be total (3 of 3 here) while freelist recovery is often PARTIAL (75 of 195 here) because page reuse and rebalancing overwrite some deleted content
- the named failure mode that beats everyone: a VACUUM (or a checkpoint-and-truncate) rewrites the file and drops recovery to 0 -- which is precisely why you image first, work on a copy, and never let SQLite touch the original
- the forensic judgment: hash the evidence before and after, carry the -wal and -shm sidecars alongside the main .sqlite, and never open the original read-write
Steps in this lab
- When you reach for this -- and when it will not work
- How to run Python from a cold start (zero assumptions)
- The SQLite file format you actually need
- The -wal sidecar, and why it is a goldmine
- Build the evidence database (a fake deleted chat)
- Carve the deleted rows out of the -wal by hand
- The freelist path: carving whole recycled pages
- The honest negative: watch VACUUM destroy the evidence
- How a pro does it differently