Wednesday, November 6, 2013

Python Parser to Recover Deleted SQLite Database Data

Soooo.... last week I was listening to the Forenisc Lunch  and the topic of parsing deleted
records from SQLite databases came up. These Forensic Lunches are every Friday and cover a wide range of topics relevant to the Forensics Community and are hosted by David Cowen. I highly recommend participating in one if you get the chance. It's actually at 10am my time, so it's more like a Forensic Doughnut for me.

Anyways, back to the SQLite databases....I see a lot of these databases in my mobile phone exams. They can contain emails, text messages, app data and more. It's also not uncommon to run into them on Windows (and Mac) exams as well - think Google Chrome History which is stored in an SQLite database.

SQLite databases can store deleted data within the database itself. There are a couple of commercial tools that can parse this deleted data such as Oxygen Forensics SQLite Viewer.

While a commerical tool is good, its always nice to have an open source alternative. After hearing David mention in the webcast he was not aware of any open source tools that did this, my ears perked and I decided to try my hand at writing a Python script to parse SQLite databases for deleted data.

Luckily, the SQLite file format is nicely documented on the SQLite.org website. I won't go into much detail here as it's laid out very nicely on their website.

Basically the database consists of Pages. Some of these Pages are "leaf table b-trees" which contain the data. In turn, these leaf table b-trees contain cells. According to SQLite.org, SQLite "strives" to place the cell towards the end of the b-tree page (how does a program strive I wonder?).  Because the cells 'strives' to be towards the end  (I keep thinking of Happy Gilmore - Go home ball! Don't you want to be in your home?) the unallocated space is, in essence, the space before the first cell starts. This unallocated space can contain deleted data.

The leaf table b-tree page can also contain freeblocks. Freeblocks are areas of unallocated space tracked by the leaf table b-trees.  So there are two areas within a page that can contain deleted data: unalloacted and freeblocks.

In this example I am going to use the script to parse the Google Chrome History database.  In case you want to play along you can find this file under C:\Users\%USERNAME%\AppData\Local\Google\Chrome\User Data\Default (if you have Chrome installed).

Using the SIFT workstation I ran the script over the History file (by default the Chrome History file does not have a file extension):

sqlparse.py -f  /home/sanforensics/History -o report.tsv

 The output includes the Type (Allocated or Freeblock), Offset, Length and Data:

  


Now, an important note about the deleted data. In order to make the data readable, I have stripped tabs,white spaces and non-printable characters in the output.  As much as I love like looking at hex, it was drowning out the strings I was looking for.

You can also run the script in raw mode, which will dump the data field as is:

sqlparse.py -f mmssms.db -r -o report.txt

This can be helpful if you are looking for timestamps, flags or other data that may be in Hex.

Download the sqlparse.py script here. Tested on Python 2.6.4.

***Update 9/2/2014***
Windows GUI and Windows CLI added. Use the same link as above to download any of these versions.

24 comments:

  1. Hey! This looks really cool... Question though: I know sqlite processing very frequently makes temp copies of databases, so bits of them can be scattered over an entire volume in unallocated space. What would it take to mod this so it could process extracted unallocated space and properly recognize & extract sqlite database fragments?
    Thanks
    John McCash

    ReplyDelete
  2. Thanks John.

    There is a great post by Cheeky4n6Monkey (http://cheeky4n6monkey.blogspot.com/2013/02/creating-perl-script-to-retrieve.html) that discusses how to carve for deleted SMS message that once existed in SQLite databases. I believe the principles talked about here could be applied to your idea:

    http://cheeky4n6monkey.blogspot.com/2013/02/creating-perl-script-to-retrieve.html

    ReplyDelete
  3. Thanks a lot. It really help me to recover deleted messages from Whatsapp sqllite database.

    ReplyDelete
  4. Thanks for the feedback! It's nice to hear how it's being used....

    ReplyDelete
  5. Nice. Wouldn't mind seeing it in py 3 :)

    ReplyDelete
    Replies
    1. Hint taken :-) I'll add it to my to-do list and will hopefully have one soon.

      Delete
    2. Thanks! And thanks for sharing with the community. Not many take the time to do that. Awesome job!

      Delete
  6. Hi Mari ,
    The codes and none of the techniques like finding website url using winhex shows only zero value . i have also tried oxygen forensic sqlite viewer to scan deleted info of sqlite database but it also shows zero . I think chrome and firefox are overwriting the data to be deleted with zeros.

    ReplyDelete
  7. 1) Would be very useful if it could run via ADB on Android phones and collect all possible .db instances with deleted fields. 2) To have an option to reintroduce all deleted info back into the .db would be very appreciated.

    ReplyDelete
  8. nice post.. trying to run the script but getting the following errors: Syntax Error and when try to fix get the NameError on the py file.

    IDLE 2.6.4
    >>> sqlparse.py -f mmssms.db -r -o report.txt
    SyntaxError: invalid syntax


    >>> sqlparse.py -f /mmssms.db -r -o /report.txt
    Traceback (most recent call last):
    File "", line 1, in
    sqlparse.py -f /mmssms.db -r -o /report.txt
    NameError: name 'sqlparse' is not defined
    >>>

    ReplyDelete
  9. Hmmm... I can't seem to replicate the error. ... maybe try the full file path to the the database and the report file?

    ReplyDelete
  10. NVM i got it working. just ran it within the cmd prompt as admin rights.

    ReplyDelete
  11. Thanks for sharing your issue and solution - it's sure to help others if they run into the same problem :-)

    ReplyDelete
  12. Thanks for sharing :) But I am unable to get it work and get the same errors as above. Could any one please let me know how to run this.

    In Ubuntu work station I run this on a terminal.
    > python
    >>> sqlparse.py -f /home/xyz/mmssms.db -r -o /home/xyz/report.txt

    Many thanks.

    ReplyDelete
  13. Thanks for sharing :) But I am unable to get it work and get the same errors as above. Could any one please let me know how to run this.

    In Ubuntu work station I run this on a terminal.
    > python
    >>> sqlparse.py -f /home/xyz/mmssms.db -r -o /home/xyz/report.txt

    Many thanks.

    ReplyDelete
    Replies
    1. Try to run it outside of the Idle. IE, don't hit enter after python. Also, the if you download the current program, the file name is not sqlparse, but rather sqlparse_v1.1.py

      Delete
    2. thank it worked :)

      Delete
  14. Thanks Maria for the script,

    I wonder if do you think it´s possible some similar for and Android stats app that fails me sometimes and that I have both files .db and .db-journal

    Thanks,

    Teresa

    ReplyDelete
  15. hi mari .i have so many queries.... can u give me contact info

    ReplyDelete
  16. Hi Mari,
    For a very large file doing filesize=len(f.read()) at line 83 of your script might not work so well. You can get the file's logical size directly from os.stat like this: filesize=os.stat(filename).st_size
    Cheers,
    Adam

    ReplyDelete
  17. I have written my own python script to recover deleted records from SQlite database:

    https://github.com/Vasyap99/ksqlite2csv

    ReplyDelete
  18. Thanks the script has been very useful

    ReplyDelete