SQLite Databases and Internet HIstories

The first step in this post is to mount the evidence file for access to the file system and the contents of the drive.  I have covered this topic with two different tools already.  I have covered this using xmount and I have covered this using ewfmount tool.  Please reference one of these posts before continuing.

Once we have completed the final steps of mounting:

$ xmount --in ewf nps-2008-jean.E?? /E01Mnt/RAW/
$ ls /E01Mnt/RAW/
nps-2008-jean.dd  nps-2008-jean.info
$ sudo mount -o ro,loop,offset=32256 /E01Mnt/RAW/ewf1 /E01Mnt/V1

We can access the information contained in this evidence file.  Before continuing you will need to install the sqliteman application.

$ sudo apt-get install sqliteman

$ which sqliteman
/usr/bin/sqliteman

Sqliteman is a tool used to view and edit sqlite databases.  Please take the time to review the tool and its functionality.

These databases are important to us as examiners because a large amount of the everyday data that we need is stored in this format.  These files commonly have a .db or sqlite file extensions.  Internet histories, chat histories, address books, and many, many other items that may contain evidence are stored in this format.  I recently was working on a case where I needed to locate the usernames of all the users the operator of an Android based phone was communicating with on Kik Messenger.  I pulled all of the databases associated with Kik.  I located chat database and a separate user account database.

This is one of many graphical tools that can be used to parse out databases so if you find a better tool for this please post it in the comments section.  In a later post I will be discussing using the command line to navigate databases.  Now lets get back to it.

We are going to be looking at the browser internet histories for the evidence file nps-2008-jean.E01.  This evidence file has been our backbone for researching these tools up to this point.  In a future post I am also going to be parsing databases for current browsers, chat applications, and any other applications that might contain important information that I encounter.

The first thing we need to do is find our databases.  One simple way would be to browse the file structure and see what databases are stored on this machine.  Based off of the current mounting procedures we know the files are stored in /E01Mnt/V1/ like we see in the image below.

Browsing these files manually will take forever so lets try something different while back at the command line.

$ fiwalk nps-2008-jean.E01 > fiwalkoutput.data

We've seen this command before.  It's simply creating a file called fiwalkoutput.data that will store a multitude of data about each of the files in this volume.  A shortened version of the output may look something like this:

parent_inode: 11342
filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/content-prefs.sqlite
partition: 1
id: 217
name_type: r
filesize: 7168
alloc: 1
used: 1
inode: 11383
meta_type: 1
mode: 511
nlink: 2
uid: 0
gid: 0
mtime: 1210743530
mtime_txt: 2008-05-14T05:38:50Z
ctime: 1210743530
ctime_txt: 2008-05-14T05:38:50Z
atime: 1216603847
atime_txt: 2008-07-21T01:30:47Z
crtime: 1210743530
crtime_txt: 2008-05-14T05:38:50Z
seq: 10
md5: 698620dc14bd2b952f1556b7bdefd638
sha1: c81bcde4bd704f1b106df1affaa12ec895e1b917

Here we can see the file name and path and other pertinent information about this file.

$ grep sqlite fiwalkoutput.data 
filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/content-prefs.sqlite
filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/cookies.sqlite
filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/downloads.sqlite
filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/formhistory.sqlite
filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/permissions.sqlite
filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/places.sqlite
filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/search.sqlite
filename: Documents and Settings/Administrator/Local Settings/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/urlclassifier3.sqlite
filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/content-prefs.sqlite
filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/cookies.sqlite
filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/downloads.sqlite
filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/formhistory.sqlite
filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/permissions.sqlite
filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/places.sqlite
filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/places.sqlite-journal
filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/search.sqlite
filename: Documents and Settings/Jean/Local Settings/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/OfflineCache/index.sqlite
filename: Documents and Settings/Jean/Local Settings/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/urlclassifier3.sqlite
filename: Documents and Settings/Jean/Local Settings/Temp/sqlite_7Mhy8N5FkPkwwQd
filename: Documents and Settings/Jean/Local Settings/Temp/sqlite_ooepoG0zgOotsEE
filename: Program Files/Mozilla Firefox 3 Beta 5/sqlite3.dll
filename: Program Files/Mozilla Firefox 3 Beta 5/sqlite3.dll.moz-backup
filename: Program Files/Mozilla Firefox 3 Beta 5/sqlite3.dll

$ grep -F .db fiwalkoutput.data 

filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/key3.db

filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/cert8.db

filename: Documents and Settings/Administrator/Application Data/Mozilla/Firefox/Profiles/towjib3x.default/secmod.db

filename: Documents and Settings/Administrator/Local Settings/Application Data/IconCache.db

filename: Documents and Settings/All Users/Application Data/VMware/Compatibility/native/wpa.dbl

filename: Documents and Settings/All Users/Application Data/VMware/Compatibility/virtual/wpa.dbl

filename: Documents and Settings/All Users/Documents/My Pictures/Sample Pictures/Thumbs.db

filename: Documents and Settings/Devon/Local Settings/Application Data/IconCache.db

filename: Documents and Settings/Jean/Application Data/acccore/nss/cert8.db

filename: Documents and Settings/Jean/Application Data/acccore/nss/key3.db

filename: Documents and Settings/Jean/Application Data/acccore/nss/secmod.db

filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/key3.db

filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/cert8.db

filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/secmod.db

filename: Documents and Settings/Jean/Local Settings/Application Data/IconCache.db

filename: Documents and Settings/Jean/Local Settings/Temp/QQGames/inst/images/Thumbs.db

filename: Documents and Settings/Jean/My Documents/My Pictures/Thumbs.db

filename: Program Files/Tencent/QQ Games/LocalizationRes/en-us/DailyTip/images/Thumbs.db

filename: Program Files/Tencent/QQ Games/LocalizationRes/en-us/DailyTip/images/tips/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/AD/inst/images/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/CAAddins/AvatarRoom/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/CAAddins/Chat/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/CAAddins/GraRom/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/CAAddins/Match/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/CAAddins/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/ChannAdi/AdMiniGa/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/ComplainRoom/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/Download/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/FrameDlg/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/MainWin/Border/Thumbs.db.keep

filename: Program Files/Tencent/QQ Games/Res/MainWin/Button/Thumbs.db.keep

filename: Program Files/Tencent/QQ Games/Res/MainWin/Thumbs.db.keep

filename: Program Files/Tencent/QQ Games/Res/MainWin/Tray/Thumbs.db.keep

filename: Program Files/Tencent/QQ Games/Res/MainWin/web/Thumbs.db.keep

filename: Program Files/Tencent/QQ Games/Res/playerinfopanel/Thumbs.db

filename: Program Files/Tencent/QQ Games/Res/qqshow/Thumbs.db

filename: WINDOWS/system32/wpa.dbl

With a quick grep command we can see every file with "sqlite" or ".db" in the name (NOTE:  The -F in this command forces grep to see the .db literally.  The period is commonly associated with a variable.  The command fgrep could also have been used with the same results).  We could have been much more specific with the command here but I will post about grep and similar commands at another time.

We can see that the majority of our internet history information is stored in the users Application Data folders on Microsoft based operating systems.  This is consistent even with modern versions Windows including Windows 7 and 8.  For this example lets look at the following files:

filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/formhistory.sqlite

In our application we can see along the left side a navagation bar listing the main database.  This database has a "Tables" section and a "System Catalogue" section.  The tables will always hold the data where the System Catalogue will show the layout of the tables.

The "moz_dummy_table" and "moz_formhistory" portion of this database our each they're own table of information.    With this application you will need to double click on the table before the Full View tab will be populated.

Here we can see that there is data stored in this database.  Including some internet search terms.  Further down we can see stored data like Jean's email address, birth year, and ZIP code.  Once again, other databases may contain complete URL histories and search terms.  We may see some of those in our next database.

In the next database we have multiple tables with important information.

filename: Documents and Settings/Jean/Application Data/Mozilla/Firefox/Profiles/c3xj7bxx.default/places.sqlite

In this database we can see there are multiple tables including some interesting tables like places and bookmarks.

This bookmarks table is exactly what it looks like.  It's a database containing the users bookmarks.  Places is more interest.  It is the complete history of URL.  This history includes dates and times and number of visits:

Using this tool it is not possible to export the database information which would be quite handy for reporting but it does allow you to familiarize yourself with databases.  In an upcoming post we will talk about creating databases for use with forensic tools like fiwalk.  It is important to remember that these database tables may be laid out differently than one another but actually contain great data if you are will to look for it.  Look at the databases in this evidence file and see if you can't put some of the pieces of the puzzle together for yourself (hint - favicons).