SQBrite is a data recovery tool for SQLite databases
SQBrite requires Python 3. To install, simply run:
$ pip3 install --user sqbrite $ sqbrite --help
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.
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.
- 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
- SQBrite works better when ptrmap pages are present
- The undelete subcommand 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