5 ms·

Recovering deleted records from an SQLite database

38 points by 2510c39011c5 12y ago

ncza 12y ago
Does a simple VACUUM prevent this?
- delinka 12y ago
  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 https://sqlite.org/lang_vacuum.html
mox1 12y ago
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?"
- shabble 12y ago
  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 http://www.forensicswiki.org/wiki/Mozilla_Firefox#History works for me on OSX 10.9/FF37
  - deleted 12y ago
    [deleted]
  - asutherland 12y ago
    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/src/moz.build#37 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#6653 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/unit/test_sqlite_secure_delete.js 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.
gtrubetskoy 12y ago
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 https://www.sqlite.org/fileformat.html or https://github.com/mackyle/sqlite/blob/master/src/pager.c#L26 https://github.com/mackyle/sqlite/blob/master/src/pager.c#L2...
killnine 12y ago
http://www.cyanline.com/blog.php?entryT=Extracting%20Deleted%20Messages%20From%20An%20iPhone http://www.cyanline.com/blog.php?entryT=Extracting%20Deleted...
mayoff 12y ago
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 https://www.sqlite.org/pragma.html#pragma_secure_delete

38 points by 2510c39011c5 12y ago

ncza 12y ago

Does a simple VACUUM prevent this?

delinka 12y ago
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 https://sqlite.org/lang_vacuum.html

mox1 12y ago

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?"

shabble 12y ago
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 http://www.forensicswiki.org/wiki/Mozilla_Firefox#History works for me on OSX 10.9/FF37
- deleted 12y ago
  [deleted]
- asutherland 12y ago
  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/src/moz.build#37 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#6653 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/unit/test_sqlite_secure_delete.js 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.

gtrubetskoy 12y ago

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 https://www.sqlite.org/fileformat.html or https://github.com/mackyle/sqlite/blob/master/src/pager.c#L26 https://github.com/mackyle/sqlite/blob/master/src/pager.c#L2...

killnine 12y ago

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

mayoff 12y ago

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 https://www.sqlite.org/pragma.html#pragma_secure_delete