Recovering deleted records from an SQLite database

sandersonforensics.com

38 points·2510c39011c5··8 comments

Incase anyone is wondering why this is useful, lots and lots of android and iOS applications (including system apps , OS functionality) use SQLite for storing all kinds of things.

Mobile devices forensics quickly turns into "What data is in this SQLite table?"

Doesn't (Desktop) Firefox use it for history and bookmarks and various things as well[1]?

I wonder if 'delete my history' GUI action actually removes the file (securely) or just deletes entries from it, exposing it to this sort of recovery?

[1] The file location and sample query against places.sqlite from http://www.forensicswiki.org/wiki/Mozilla_Firefox#History works for me on OSX 10.9/FF37

Firefox's Places subsystem does use SQLite. Firefox's built-in SQLite implementation is compiled with the SQLITE_SECURE_DELETE mechanism referenced elsewhere in this thread (https://dxr.mozilla.org/mozilla-central/source/db/sqlite3/sr...) and a configure check is performed when Firefox is built with system SQLite (https://dxr.mozilla.org/mozilla-central/source/configure.in#...).

Additionally, there's a unit test that verifies that the feature is really working at https://dxr.mozilla.org/mozilla-central/source/storage/test/... that checks that a string shows up in the database and then disappears from the database when the row is removed from the database.

Consider using PRAGMA SECURE_DELETE = 1 if you want to make these forensics more difficult/impossible.

https://www.sqlite.org/pragma.html#pragma_secure_delete

http://www.cyanline.com/blog.php?entryT=Extracting%20Deleted...

The nice thing about SQLite which makes this kind of thing possible is that the file format is very well documented, for example: https://www.sqlite.org/fileformat.html or https://github.com/mackyle/sqlite/blob/master/src/pager.c#L2...

Does a simple VACUUM prevent this?

Based on the description at [1] it does indeed seem logical that a VACUUM would prevent this type of recovery on primary databases.

1 - https://sqlite.org/lang_vacuum.html

Reply on news.ycombinator.com