Bring the shine back into your database with SQBrite!
SQBrite is a data recovery/forensics tool for SQLite databases. It uses a Python 3 implementation of the SQLite on-disk file format to recover deleted table rows.
SQBrite's name is inspired by PL Daniels' undark, but is a completely separate implementation.
Installing SQBrite
SQBrite requires Python 3. To install, simply run:
$ pip3 install --user sqbrite
$ sqbrite --helpBackground
SQLite uses a paginated data model in which each database is a collection of same-size pages. There are several kinds of pages, of which one type (B-Tree Table Leaf pages) contains the starting point for actual data belonging to individual table rows.
When a row is deleted by means of a DELETE FROM table (...) statement, the space occupied by that row's data (a record) within the relevant B-Tree Table Leaf page is marked as free and may subsequently be used to store new records or update existing records. However, it is common to see freed space within a page (a freeblock, in SQLite parlance) left alone after rows are deleted. In that case, it *may* be possible to retrieve deleted row data from within the freeblock.
Heuristics
The SQLite file format doesn't keep track of where deleted records start and end within a leaf page's freeblocks. This means that SQBrite needs a mechanism to find out where record headers start. This is achieved through the use of byte-wise regular expressions specific to tables in known databases. These regular expressions and the offset that separates matches from the first byte in a well-formed header are stored in a user-editable YAML file.
SQBrite aims to ship with heuristics for popular SQLite databases, so do send pull requests if you've got good results with your heuristics.
Features
- Export all records to CSV or reinject "undeleted" records into a copy of the database
- Extensible heuristics - just add entries to
~/.local/share/sqbrite/sqbrite.yaml! - SQBrite can recover records from within active B-tree table leaf pages or from former table-leaf Freelist pages.
- Heuristics for iOS and Firefox databases
Limitations
- SQBrite works better when
ptrmappages are present - The
undeletesubcommand may fail when re-inserting deleted rows into a table causes a constraint violation - SQBrite cannot recover records deleted with the SQLite secure_delete pragma enabled
- Recovering data from overflow pages that have become Freelist leaf pages is not currently supported
Acknowledgments
Big thanks to @tobraha for contributing bugfixes in 2022.