The SQLite file format and data recovery
You examine the SQLite file header, pages, B-trees, freelist and WAL file at hex level and recover deleted records and corrupted files.
Level 401 · 15 lessons · 12 practice labs
Lessons
The file format
- The database header
- Page types and B-tree structure
- Cell and record format: varints and serial types
- Overflow pages
- Short task: find a row at hex level
Deleted data
- The freelist and free blocks
- secure_delete and the effect of VACUUM
- The WAL file format and frames
- Recovering deleted rows
- Short task: recover deleted chat messages
Corruption
- Causes of corruption
- integrity_check and dbstat
- Recovery with .recover
- A small SQLite parser in Python
- Capstone: recover data from a corrupted app database
Practice labs
- Read the page size from the file header: Inspect the first 100 bytes of a database in hex and find the page size, page count and text encoding.
- Identify a database with a changed extension: Find SQLite databases among extensionless files in a folder by their magic string and read their versions.
- Follow a row page by page: Starting from the root page, walk the B-tree in hex down to the leaf page holding a given rowid.
- Decode the varints of a record: Decode the varints in given cell bytes by hand to find the record length and rowid.
- Extract column values from serial types: Read the serial type codes in a record header and rebuild every column of a customer row from hex.
- Reassemble long text split over overflow pages: Follow the page chain to recover an e-mail body spread over overflow pages in full.
- Find a deleted row in a free block: After a note is deleted, show with strings and hex that its text is still in a free block on the page.
- Recover an older version from the WAL file: Recover the old value of a record updated in the main file by reading the WAL frames one by one.
- Recover deleted chat messages: Scan freelist pages and free blocks of a chat app database to recover deleted messages with their times.
- Diagnose corruption with integrity_check: Read integrity_check output on a failing inventory database and find which table is damaged.
- Salvage data with .recover: Use .recover to move salvageable rows from a database with damaged pages into a new file and count what was lost.
- Write a small page parser in Python: Write a Python script that reads leaf-page records without the sqlite3 module and pulls rows out of a corrupt file.
About this course
You examine the SQLite file header, pages, B-trees, freelist and WAL file at hex level and recover deleted records and corrupted files.
What you will learn
- The file format
- Deleted data
- Corruption
Prerequisites
You should have practised the topics of the same path up to level 301.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at level 401; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.