

Recovering deleted records from an SQLite database - 2510c39011c5
http://sandersonforensics.com/forum/content.php?222-Recovering-deleted-records-from-an-SQLite-database

======
mox1
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
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

~~~
asutherland
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...](https://dxr.mozilla.org/mozilla-
central/source/db/sqlite3/src/moz.build#37)) and a configure check is
performed when Firefox is built with system SQLite
([https://dxr.mozilla.org/mozilla-
central/source/configure.in#...](https://dxr.mozilla.org/mozilla-
central/source/configure.in#6653)).

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

------
mayoff
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)

------
killnine
[http://www.cyanline.com/blog.php?entryT=Extracting%20Deleted...](http://www.cyanline.com/blog.php?entryT=Extracting%20Deleted%20Messages%20From%20An%20iPhone)

------
gtrubetskoy
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#L2...](https://github.com/mackyle/sqlite/blob/master/src/pager.c#L26)

------
ncza
Does a simple VACUUM prevent this?

~~~
delinka
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)

