Tool for extracting deleted data from unvacuumed SQLite files

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Tool for extracting deleted data from unvacuumed SQLite files

Paul L Daniels
Hello everyone,

        Sincerely hoping this is the appropriate place to post this
        announcement.

        A while ago a client needed some important SMSs retrieved from
        their iPhone that had been deleted, anyhow, long story short,
        I've since created a tool which I now call "Undark" to pull all
        the data it can find in a SQLite3 file and dump it to stdout in
        a CSV type format.

        I looked at several forensics & sleuthing websites during the
        writing process and at this point I couldn't find anything else
        to do the job.  Initially I wrote it so that it was
        specifically structured to look for iPhone iOS6 SMSs, but by
        the time the next revision came around I had made it work with
        all data.

        I'm offering "Undark" to the community under a BSD Revised
        licence.

        "Undark" is only at v0.2 at this time, it's still quite coarse
        around some corners and it does not differentiate between
        deleted and undeleted rows.  It also has a limitation of only
        decoding what it finds within the SQLite page (ie, 4K in most
        cases), if the row extends beyond the page the data won't be
        retrieved ( I am working on this for v0.3 ), finally, it
        doesn't decode or dump BLOB data yet, simply not sure how to
        represent that sanely on a CSV dump.

        Obviously if the DB has been vacuumed then there's simply no
        chance to get it back; but fortunately I've found that a lot of
        them aren't vacuumed.

        Available at - http://pldaniels.com/undark


--
Computer Repairs for Charters towers - http://ctpc.biz
A.B.N. 19 500 721 806
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tool for extracting deleted data from unvacuumed SQLite files

Simon Slavin-3

On 6 Oct 2013, at 1:58pm, Paul L Daniels <[hidden email]> wrote:

> finally, it
> doesn't decode or dump BLOB data yet, simply not sure how to
> represent that sanely on a CSV dump.

Quick summary, most of which you probably do right:

Numeric fields should go unquoted and string fields in a CSV files must be quoted:

384.2, "Frank", 23, "",, "2"

Fields may start and/or end in whitespace which must be ignored.
If the first non-whitespace character is a quote, you have a text field.
The fourth field contains a zero-length string which is not the same as NULL.
The fifth field contains a NULL, or any other convention which means 'value missing'.
A NULL must not be interpreted as the numeric value zero or a zero-length string.
That last field is a one-character string, not the number 2.

If a double-quote appears in a text field it must be doubled:

"Here is a quote character:"".", 384.2, "Frank", 23, "",, "2"

Tabs and return characters in a text field can be used as is.  Yes, the result looks messy to the naked eye, but the parser is looking for a double-quote character and doesn't care.

BLOBs
-----
The convention for BLOB fields is to dump them in hex surrounded like this:

X'deadbeef'

(you can use upper- or lower-case x).  So inside a CSV one might look like this:

x'462F82A4D7', "Here is a quote character:"".", 384.2, "Frank", 23, "",, "2"

If the first non-whitespace character is 'x' or 'X', you have a BLOB.  Hex letters can be upper- or lower-case.



Simon.

PS: "Be conservative in what you generate and liberal in what you accept." -- Jon Postel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tool for extracting deleted data from unvacuumed SQLite files

Paul L Daniels
On Sun, 6 Oct 2013 15:07:06 +0100
Simon Slavin <[hidden email]> wrote:

> Quick summary, most of which you probably do right:
>
> Numeric fields should go unquoted and string fields in a CSV files
> must be quoted:

        Thanks for the notes, there's a few things in there I'm not
        doing right, so I do sincerely appreciate the input and
        that'll be folded in to the next release ( fairly quick cycle
        at the moment as the bugs/features are tacked on ).

        The original purpose of the whole Undark to retrieve a client's
        SMSs was satisfied simply from a visual dump ( as opposed to
        interpreted ) and as such I didn't put a lot of fine tuning in
        to the CSV format. I greatly appreciate the tips
        there, thank you.

        The blob details were something I wasn't having any real
        success in finding during my short searches around the net, so
        again, another very good bit of info.

        After my original post, I realised that Undark could also be
        used to pull data from corrupted db's ( missing tables or
        various B-tree indexes ), so I'll probably need some willing
        victims to test that later.

Regards,
Paul.


--
Computer Repairs for Charters towers - http://ctpc.biz
A.B.N. 19 500 721 806
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tool for extracting deleted data from unvacuumed SQLite files

James K. Lowden
In reply to this post by Paul L Daniels
On Sun, 6 Oct 2013 22:58:28 +1000
Paul L Daniels <[hidden email]> wrote:

> "Undark" is only at v0.2 at this time, it's still quite coarse
> around some corners and it does not differentiate between
> deleted and undeleted rows.  It also has a limitation of only
> decoding what it finds within the SQLite page (ie, 4K in most
> cases), if the row extends beyond the page the data won't be
> retrieved ( I am working on this for v0.3 ), finally, it
> doesn't decode or dump BLOB data yet, simply not sure how to
> represent that sanely on a CSV dump.

If Undark decodes the metadata, it might be more useful to create a new
database instead of a CSV file.  The new database would be a mirror of
the input, except that its tables would hold deleted rows (or both
deleted and extant, with a column signifying which).  

That way no information is lost, re-insertion is simple, and
representation questions are mooted.  

--jkl


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tool for extracting deleted data from unvacuumed SQLite files

Paul L Daniels


> If Undark decodes the metadata, it might be more useful to create a
> new database instead of a CSV file.  The new database would be a
> mirror of the input, except that its tables would hold deleted rows
> (or both deleted and extant, with a column signifying which).  

I may look in to adding such a feature in the future, though it'd
require a few more layers of funtionality within Undark to be able to
facilitate it.

It should be cautioned that due to the statistical probabilities dumped
data should always still be vetted by a human for now to weed out false
rows that arise due to the limited size of the probability space
( the fewer columns in a table the higher the chance of a false hit ).

Regards,
Paul




--
Computer Repairs for Charters towers - http://ctpc.biz
A.B.N. 19 500 721 806
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tool for extracting deleted data from unvacuumed SQLite files

Paul L Daniels
In reply to this post by Paul L Daniels
Hello all,

        Version 0.3 of the SQLite undelete/recovery tool has been
        released, still very much in beta phase, no doubt a lot of bugs
        and core dumps on some files.

        http://pldaniels.com/undark/undark-0.3.tar.gz 
        (MD5SUM:19d3183e7a0b782d658bcb631cc4146b )

Regards, Paul


--
Computer Repairs for Charters towers - http://ctpc.biz
A.B.N. 19 500 721 806
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tool for extracting deleted data from unvacuumed SQLite files

Paul L Daniels
In reply to this post by Paul L Daniels
Hello everyone,

        I've released v0.4 of Undark last night.  Improves on the
        extraction of data out of the freeblocks as well as now
        allowing for blob dumps that extend beyond a single page.

        Also added an option to let you specify the DB page size for
        when trying to recover data from corrupted SQLite databases.

        At the moment you still have to run Undark in two passes if you
        want to extract both the normal payload data and the freespace
        data, hoping to merge this in 0.5.

        http://pldaniels.com/undark

Regards, Paul.

--
Computer Repairs for Charters towers - http://ctpc.biz
A.B.N. 19 500 721 806
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tool for extracting deleted data from unvacuumed SQLite files

LincolnBurrows
In reply to this post by Paul L Daniels
If you are not a developer you can avoid having the same problem again using
Sqlite Recovery tool and  recover deleted records from Sqlite database
<http://www.sqlrecoverytool.com/recover-sqlite-database.html>  . It adds
single-master replication to SQLite.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tool for extracting deleted data from unvacuumed SQLite files

LincolnBurrows
In reply to this post by Paul L Daniels
If you are not a developer you can avoid having the same problem again using
Sqlite Recovery tool and  recover deleted records from Sqlite database
<http://www.sqlrecoverytool.com/recover-sqlite-database.html>  . It adds
single-master replication to SQLite.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users