Warning: This is one of my longer
According to the SQLite website, SQLite is an open source, cross-platform database library that is used in software applications (eg DropBox and Skype clients), gadgets (eg Nokia phones, MP3 players, PDAs) and website databases. As SQLite becomes more prevalent, it seems prudent for forensicators to be able to retrieve/view the contents of these databases. Thus, Enter the Perl Monkey!
Special mention/unashamed name drop must go to Detective Cindy Murphy who mentioned the increasing prevalence of SQLite in the mobile phone arena which in turn, inspired this post.
In brief, SQLite files are divided into equal sized pages. The minimum file size is one page (the minimum page size is 512 bytes), the maximum file size is ~140 TB. While page types can vary, each page size within a single database file is constant. Page sizes can/will differ between different database files. For those interested, the SQLite file format is defined here.
Richard Drinkwater's blog has some excellent information on carving SQLite databases. Andrew Hoog has also released a set of carving slides here.
Firefox and SQLite
In 2011, Alex blogged about Firefox 4 forensics here and Tsurany also wrote about Firefox's Private Browsing Mode here.
For more information on Firefox and SQLite, I highly recommend "Digital Forensics With Open Source Tools" by Altheide and Carvey (p 147 onwards).
In 2009, Kristinn Gudjonsson wrote an awesome Firefox History Perl script which he described in further detail here.
To improve my overall understanding (of both Perl and SQLite), I have written a stripped down version of Kristinn's script. It's kinda re-inventing the wheel but I think I will improve my understanding/retention by doing rather than just reading. And given the stripped down nature of my script, it will also take less time for me to explain.
But before monkeying around with the code, we need to find out about the relevant Firefox database schema (eg table names, keys, fields). Here is the schema for "places.sqlite" as defined by ForensicsWiki and also via my own observations:
 |
| Firefox's "places.sqlite"schema |
Note: One side effect of having "moz_bookmarks" linked with "moz_places" is that "places.sqlite" will always contain any bookmarked site information regardless of CCleaner use or "Private Browsing".
Here is the schema for "downloads.sqlite":
 |
| Firefox's "downloads.sqlite" schema |
According to ForensicsWiki, Firefox stores its SQLite files in the following places:
- Windows XP: C:\Documents and Settings\<username>\Application Data\Mozilla\Firefox\Profiles\<profile folder>\places.sqlite
- Windows Vista: C:\Users\<user>\AppData\Roaming\Mozilla\Firefox\Profiles\<profile folder>\places.sqlite
- Linux: /home/<user>/.mozilla/firefox/<profile folder>/places.sqlite
- MacOS: /Users/<user>/Library/Application Support/Firefox/Profiles/default.lov/places.sqlite
While each of these ".sqlite" files contains a SQLite database, some of them (eg "places.sqlite") contain multiple tables (eg moz_places, moz_historyvisits, moz_bookmarks). When running, Firefox also creates temporary ".sqlite" files with the extensions ".sqlite-shm" for shared memory (eg "places.sqlite-shm") and also ".sqlite-wal" for write ahead logs (eg "places.sqlite-wal"). As you can observe these at run time with Windows Explorer, theres a good chance they are carvable/recoverable.
Firefox 11 has the following files: addons.sqlite, chromeappsstore.sqlite, content-prefs.sqlite, cookies.sqlite, downloads.sqlite, extensions.sqlite, formhistory.sqlite, permissions.sqlite, places.sqlite, search.sqlite, signons.sqlite, webappsstore.sqlite.
Firefox 3.6.17 has the following sqlite files:
content-prefs.sqlite, cookies.sqlite, downloads.sqlite, formhistory.sqlite, permissions.sqlite, places.sqlite, search.sqlite, signons.sqlite, webappsstore.sqlite, urlclassifier3.sqlite.
Note: The bold entries denote the differing files between Firefox versions. Its also easy to miss files when switching between browsing modes / using CCleaner so take this list with a grain of salt.
For our script, we will be printing the Downloads, Bookmarks, and History information only. Unlike Kristinn's script we won't offer html output nor detect time-skewing.
As we saw in the schema diagram above, the Bookmarks and History information is stored in the "places.sqlite" file. This file contains the "moz_places", "moz_bookmarks" and "moz_historyvisits" tables.
The Downloads information is stored in the "moz_downloads" table from the "downloads.sqlite" file.
Viewing SQLite Tables
There are several SQLite Database browsers around but not all of them will be able to open the later releases of SQLite (the latest is 3.7.10).
The easiest one to use is probably the Firefox Add-on GUI called SQLite Manager. I have used it to view the contents of Firefox 11 (uses SQLite V3.7.7) ".sqlite" files. The SQLite Manager installed on SIFT appears to use SQLite V3.7.4.
On SIFT, there is also an sqlite3 command line exe installed (in "/usr/local/bin/sqlite3"). Unfortunately, it seems that the current installed version (V3.6.16) cannot read Firefox 11 ".sqlite" files. It complains that the file is encrypted or not a database file. It will read SIFT's own Firefox 3.5.17 ".sqlite" files however.
Consequently, I have downloaded the latest sqlite3 exe (V3.7.10) from the official SQLite website and then used it to successfully read Firefox 11 ".sqlite" files. For more information on using the sqlite3 command line interface I highly recommend "Digital Forensics With Open Source Tools" (Altheide and Carvey) p.150.
For Perl scripts, we need to ensure that our SIFT's DBI (Database Interface?) package is up to date by typing: "sudo cpan DBI" (Warning: it takes a while to update!).
Incidentally, there are other Perl CPAN packages available (ie DBD::SQLite and SQLite::DB) which are supposed to make it easier to interact with SQLite databases (eg make it more Object Oriented) but whilst I found they were able to read Firefox 3.5.17 ".sqlite" files, they were not able to read Firefox 11 ".sqlite" files.
So that's when I decided to re-write my initial script using both Kristinn's script and this article by Mark-Jason Dominus as guides.
Code
OK so here's my script code:
# CODE BEGINS AFTER THIS LINE
#!/usr/bin/perl -w
# Perl script to parse Firefox places.sqlite and downloads.sqlite
# Based on Kristinn Gudjonsson's "ff3histview" Perl script (http://blog.kiddaland.net/dw/ff3histview) and
# Mark-Jason Dominus's "A Short Guide to DBI" article (http://www.perl.com/pub/1999/10/DBI.html)
# Works with SIFT's Firefox V3.6.17 and WinXP's Firefox V11.0
# Be sure to run "sudo cpan DBI" to update the DBI Perl package before running!
use strict;
use Getopt::Long;
use DBI;
my $version = "ffparser.pl v2012-03-19";
my $help = 0;
my $bk = 0;
my $dload = 0;
my $hist = 0;
my $path = "";
GetOptions('help|h' => \$help,
'bk' => \$bk,
'dload' => \$dload,
'hist' => \$hist,
'path=s' => \$path);
if ($help || $path eq "" || (!$bk and !$dload and !$hist))
{
print("\nHelp for $version\n\n");
print("Perl script to parse Firefox places.sqlite and downloads.sqlite\n");
print("\nUsage: ffparser.pl [-h|help] [-path pathname] [-bk] [-dload] [-hist]\n");
print("-h|help .......... Help (print this information). Does not run anything else.\n");
print("-path pathname ... Path to folder containing places.sqlite and downloads.sqlite.\n");
print("-bk .............. Parse for Bookmarks (Date Added, Title, URL, Count).\n");
print("-dload ........... Parse for Downloaded items (Download Ended, Source, Target, Current No. Bytes).\n");
print("-hist ............ Parse for History (Date Visited, Title, URL, Count).\n");
print("\nExample: ffparser.pl -path /cases/firefox/ -bk -dload -hist");
print("\nNote: Trailing / at end of path\n");
exit;
}
# For now, ass-ume downloads.sqlite, places.sqlite are in the path provided
# Also, ass-ume that the path has a trailing "/" eg TAB autocompletion used
print "Running $version\n";
# Try read-only opening "places.sqlite" to extract the Big Endian 4 byte SQLite Version number at bytes 96-100
# The version number will be in the form (X*1000000 + Y*1000 + Z)
# where X is the major version number (3 for SQLite3), Y is the minor version number and Z is the release number
# eg 3007004 for 3.7.4
my $placesver=0;
open(my $placesfile, "<".$path."places.sqlite") || die("Unable to open places.sqlite for version retrieval\n");
#binmode($placesfile);
seek ($placesfile, 96, 0);
sysread ($placesfile, $placesver, 4)|| die("Unable to read places.sqlite for version retrieval\n");;
# Treat the 4 bytes as a Big Endian Integer
my $placesversion = unpack("N", $placesver);
print("\nplaces.sqlite SQLite Version is: $placesversion\n");
close($placesfile);
# Extract/Print the SQLite version number for downloads.sqlite as well
my $dloadsever=0;
open(my $dloadsfile, "<".$path."downloads.sqlite") || die("Unable to open downloads.sqlite for version retrieval\n");
#binmode($dloadsfile);
seek ($dloadsfile, 96, 0);
sysread ($dloadsfile, $dloadsever, 4)|| die("Unable to read downloads.sqlite for version retrieval\n");;
# Treat the 4 bytes as a Big Endian Integer
my $dloadsversion = unpack("N", $dloadsever);
print("downloads.sqlite SQLite Version is: $dloadsversion\n");
close($dloadsfile);
# Open the places.sqlite database file first
if ($bk or $hist)
{
my $db = DBI->connect("dbi:SQLite:dbname=$path"."places.sqlite","","") || die( "Unable to connect to database\n" );
# Checks if this is a valid Firefox places.sqlite
$db->prepare("SELECT id FROM moz_places LIMIT 1") || die("The database is not a correct Firefox database".$db->errstr);
if ($bk)
{
print "\nNow Retrieving Bookmarks ...\n";
my $sth = $db->prepare("SELECT datetime(moz_bookmarks.dateAdded/1000000, 'unixepoch') AS \'Date Added\', moz_bookmarks.title AS Title, moz_places.url AS URL, moz_places.visit_count AS Count FROM moz_bookmarks, moz_places WHERE moz_places.id = moz_bookmarks.fk ORDER BY moz_bookmarks.dateAdded ASC");
$sth->execute();
print $sth->{NUM_OF_FIELDS}." fields will be returned\n";
PrintHeadings($sth);
PrintResults($sth);
# We print out the no. rows now because apparently $sth->rows isn't set until AFTER
# $sth->fetchrow_array() has completed in PrintResults
if ($sth->rows == 0)
{
print "No Bookmarks found!\n\n";
}
else
{
print $sth->rows." Rows returned\n";
}
$sth->finish;
}
if ($hist)
{
print "\nNow Retrieving History ...\n";
my $sth = $db->prepare("SELECT datetime(moz_historyvisits.visit_date/1000000, 'unixepoch') AS \'Date Visited\', moz_places.title AS Title, moz_places.url AS URL, moz_places.visit_count AS Count FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id ORDER BY moz_historyvisits.visit_date ASC");
$sth->execute();
print $sth->{NUM_OF_FIELDS}." fields will be returned\n";
PrintHeadings($sth);
PrintResults($sth);
if ($sth->rows == 0)
{
print "No History found!\n\n";
}
else
{
print $sth->rows." Rows returned\n";
}
$sth->finish;
}
$db->disconnect;
}
if ($dload)
{
# Now we open the downloads.sqlite database file
print "\nNow Retrieving Downloads ...\n";
my $db = DBI->connect("dbi:SQLite:dbname=$path"."downloads.sqlite","","") || die( "Unable to connect to database\n" );
# No further checks, we go straight into our query because it IS possible to have an empty moz_downloads table.
my $sth = $db->prepare("SELECT datetime(endTime/1000000, 'unixepoch') AS \'Download Ended\', source AS Source, target AS Target, currBytes as \'Current No. Bytes\' FROM moz_downloads ORDER BY moz_downloads.endTime ASC");
$sth->execute();
print $sth->{NUM_OF_FIELDS}." fields will be returned\n";
PrintHeadings($sth);
PrintResults($sth);
if ($sth->rows == 0)
{
print "No Downloads found!\n\n";
}
else
{
print $sth->rows." Rows returned\n";
}
$sth->finish;
$db->disconnect;
}
# end main
sub PrintHeadings
{
my $sth = shift;
# Print field headings
for (my $i = 0; $i <= $sth->{NUM_OF_FIELDS}-1; $i++)
{
if ($i == $sth->{NUM_OF_FIELDS} - 1)
{
print $sth->{NAME}->[$i]."\n"; #last item adds a newline char
}
else
{
print $sth->{NAME}->[$i]." | ";
}
}
}
sub PrintResults
{
my $sth = shift;
my @rowarray;
# Prints row by row / field by field
while (@rowarray = $sth->fetchrow_array() )
{
for (my $i = 0; $i <= $sth->{NUM_OF_FIELDS}-1; $i++)
{
if ($i == $sth->{NUM_OF_FIELDS} - 1 )
{
print $rowarray[$i]."\n"; #last field in row adds newline
}
else
{
if ($rowarray[$i])
{
print $rowarray[$i]." | ";
}
else
{
print " | "; # field returned could be UNDEFINED, just print separator
}
}
}
}
}
#END CODE
Coding Summary
So after starting off with the usual GetOptions command line parsing and the Help message sections, we try to extract the SQLite version number from "places.sqlite" file in the user nominated path. After I had trouble using sqlite3 (V3.6.16) to read Firefox 11 ".sqlite" files, I decided to print out the SQLite version in this script. It can't hurt to know the version of the SQLite database file you're investigating eh?
According to the file format described here, this version number will be at bytes 96 to 100 inclusive. It will be in the form X00Y00Z as described here eg 3007004 represents 3.7.4.
So we "open" the file for reading only (using the "<" argument), we "seek" to byte 96, then call "sysread" to store the 4 bytes in local variable called "$placesver". Our extraction isn't quite complete because Perl isn't sure how to interpret "$placesver". So we need to call "unpack" with the parameter "N" to tell Perl to interpret this variable as a 32 bit (or 4 byte) Big Endian Integer. See the section marked "How to Eat an Egg on a Net" here for further details. Once we have unpacked our number into a separate local variable ("$placesversion"), we print it to the command line and close the "places.sqlite" file.
We repeat this process for the "downloads.sqlite" file.
The next 3 sections are based on the following theme:
- Call "DBI->connect" to open/connect to the relevant ".sqlite" file
- Call "prepare" to prepare an SQL SELECT statement
- Call "execute" for that statement
- Print out the number of fields returned by the statement
- Print out the Title Headers for each field/column (via our "PrintHeaders" function)
- Print out each row of results returned by the statement (via our "PrintResults" function)
- Call "finish" to free up any statement allocated resources
- Call "disconnect" to hang up the connection with the ".sqlite" file
The hearts of the whole program are the 3 SQL queries. The first 2 are run against the "places.sqlite" file.
This is the Bookmark retrieval query:
"SELECT datetime(moz_bookmarks.dateAdded/1000000, 'unixepoch') AS \'Date Added\', moz_bookmarks.title AS Title, moz_places.url AS URL, moz_places.visit_count AS Count FROM moz_bookmarks, moz_places WHERE moz_places.id = moz_bookmarks.fk ORDER BY moz_bookmarks.dateAdded ASC"
Its basically saying: "Match up the table entries where the moz_places.id equals the moz_bookmarks.fk field and return a human readable version of the moz_bookmarks.dateAdded field, the moz_bookmarks.title field, the moz_places.url and moz_places.visit_count".
The use of the keyword AS indicates that we wish to return result names under an alias. For example, instead of returning the name "moz.place.url" and its value, the query returns "URL" and the value.
"Digital Forensics With Open Source Tools" (Altheide and Carvey) p. 150 has more details on how we determine the human readable time format.
This is the History retrieval query:
"SELECT datetime(moz_historyvisits.visit_date/1000000, 'unixepoch') AS \'Date Visited\', moz_places.title AS Title, moz_places.url AS URL, moz_places.visit_count AS Count FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id ORDER BY moz_historyvisits.visit_date ASC"
This statement is *very* similar to the one explained in "Digital Forensics With Open Source Tools" (Altheide and Carvey) p.150 except it also returns the "moz_places.visit_count" field as well.
Finally, the Downloads query is run against the "downloads.sqlite" file:
"SELECT datetime(endTime/1000000, 'unixepoch') AS \'Download Ended\', source AS Source, target AS Target, currBytes as \'Current No. Bytes\' FROM moz_downloads ORDER BY moz_downloads.endTime ASC"
All 3 of these statements are run depending on the user's command line flags (eg -bk -hist -dload).
Testing
OK lets see how we went reading both Firefox 3.5.17 and 11 ".sqlite" files shall we?
I've been running SIFT's Firefox 3.5.17 in non-"Private Browsing" mode for a while now. So I copied the ".sqlite" files into "/home/sansforensics/ffexamples/" and then ran our script against the copied examples.
First we try analysing the Firefox 3.5.17 History:
sansforensics@SIFT-Workstation:~$ ffparser.pl -path ffexamples/ -hist
Running ffparser.pl v2012-03-19
places.sqlite SQLite Version is: 3007004
downloads.sqlite SQLite Version is: 3007004
Now Retrieving History ...
4 fields will be returned
Date Visited | Title | URL | Count
2011-11-16 20:37:31 | SANS Computer Forensics Training, Incident Response | http://computer-forensics.sans.org/ | 27
2011-11-16 20:57:56 | SANS Computer Forensics Training, Incident Response | http://computer-forensics.sans.org/ | 27
2011-11-16 21:02:08 | SANS Computer Forensics Training, Incident Response | http://computer-forensics.sans.org/ | 27
2011-12-05 20:06:26 | SANS Computer Forensics Training, Incident Response | http://computer-forensics.sans.org/ | 27
2011-12-05 20:06:37 | blog | http://computer-forensics.sans.org/blog | 4
2011-12-11 05:56:16 | SANS Computer Forensics Training, Incident Response | http://computer-forensics.sans.org/ | 27
... (Edited out)
2012-03-17 06:13:16 | Google | http://www.google.com.au/ | 75
2012-03-17 06:13:22 | SQLite::DB - search.cpan.org | http://search.cpan.org/~vxx/SQLite-DB-0.04/lib/SQLite/DB.pm | 2
2012-03-17 06:18:19 | ff3histview | http://blog.kiddaland.net/dw/ff3histview | 3
1472 Rows returned
sansforensics@SIFT-Workstation:~$
Phew! Nothing too embarrassing there ;) 1472 rows though - I gotta clear that history more often!
Hint: You can also run "ffparser.pl -path ffexamples/ -hist | more" so you can actually see the results before they scroll off the screen.
Also note, the SQLite version info for Firefox 3.5.17 is 3.7.4.
Next, we try looking at the Firefox 3.5.17 Bookmarks:
sansforensics@SIFT-Workstation:~$ ffparser.pl -path ffexamples/ -bk
Running ffparser.pl v2012-03-19
places.sqlite SQLite Version is: 3007004
downloads.sqlite SQLite Version is: 3007004
Now Retrieving Bookmarks ...
4 fields will be returned
Date Added | Title | URL | Count
2007-06-06 11:38:27 | Ubuntu | http://www.ubuntulinux.org/ | 0
2007-06-06 11:38:27 | Ubuntu Wiki (community-edited website) | http://www.ubuntulinux.org/wiki/FrontPage | 1
2007-06-06 11:38:27 | Make a Support Request to the Ubuntu Community | https://launchpad.net/distros/ubuntu/+addticket
... (Edited out)
2012-02-02 01:48:44 | Perl - Wikipedia, the free encyclopedia | http://en.wikipedia.org/wiki/Perl | 15
2012-02-14 06:26:14 | ExifTool by Phil Harvey | http://www.sno.phy.queensu.ca/~phil/exiftool/ | 4
2012-02-14 11:10:51 | The Perl Programming Language - www.perl.org | http://www.perl.org/ | 18
2012-02-15 12:59:30 | perlintro - perldoc.perl.org | http://perldoc.perl.org/perlintro.html | 11
2012-02-15 23:23:41 | perlstyle - perldoc.perl.org | http://perldoc.perl.org/perlstyle.html | 2
2012-02-29 05:06:19 | The CPAN Search Site - search.cpan.org | http://search.cpan.org/ | 18
2012-03-04 09:05:41 | Ubuntu Forums | http://ubuntuforums.org/ | 1
2012-03-16 11:37:26 | PerlMonks - The Monastery Gates | http://perlmonks.org/? | 1
2012-03-16 11:40:23 | Stack Overflow | http://stackoverflow.com/ | 1
33 Rows returned
sansforensics@SIFT-Workstation:~$
OK, now let's check the SIFT's Firefox 3.5.17 Downloads history.
sansforensics@SIFT-Workstation:~$ ffparser.pl -path ffexamples/ -dload
Running ffparser.pl v2012-03-19
places.sqlite SQLite Version is: 3007004
downloads.sqlite SQLite Version is: 3007004
Now Retrieving Downloads ...
4 fields will be returned
Download Ended | Source | Target | Current No. Bytes
2012-02-14 06:15:02 | http://blob.perl.org/books/impatient-perl/iperl.pdf | file:///root/Desktop/iperl.pdf | 480850
2012-02-14 06:39:42 | http://owl.phy.queensu.ca/~phil/exiftool/Vodafone.tar.gz | file:///root/Desktop/Vodafone.tar.gz | 61366
2012-02-14 06:40:43 | http://owl.phy.queensu.ca/~phil/exiftool/Vodafone.tar.gz | file:///root/Documents/Vodafone.tar.gz | 61366
... (Edited out)
2012-03-16 12:06:29 | http://www.forensicswiki.org/w/images/d/d0/Firefox3_places_relationship_schema.png | file:///cases/Firefox3_places_relationship_schema.png | 24481
21 Rows returned
sansforensics@SIFT-Workstation:~$
Everything looks cool so far eh? As a check, I used the SQLite Manager plugin to view these same files and saw that randomly selected entries matched the script.
Now let's try using our script with Firefox 11 files in "Private Browsing" mode. For this part, I browsed using WinXP Firefox 11 and downloaded a copy of ProcMon. I then copied the ".sqlite" files over to SIFT's "/cases/firefox11-prv/" directory.
First, lets see if we can find any Firefox 11 "Private Browsing" History info:
sansforensics@SIFT-Workstation:~$ ffparser.pl -path /cases/firefox11-prv/ -hist
Running ffparser.pl v2012-03-19
places.sqlite SQLite Version is: 3007007
downloads.sqlite SQLite Version is: 3007007
Now Retrieving History ...
4 fields will be returned
Date Visited | Title | URL | Count
No History found!
sansforensics@SIFT-Workstation:~$
No real surprise there - the history appears to be empty.
Also note, the SQLite version info for Firefox 11 is 3.7.7 (ie a later version than Firefox 3.5.17's).
Lets look at the Firefox 11 "Private Browsing" Bookmarks info:
sansforensics@SIFT-Workstation:~$ ffparser.pl -path /cases/firefox11-prv/ -bk | more
Running ffparser.pl v2012-03-19
places.sqlite SQLite Version is: 3007007
downloads.sqlite SQLite Version is: 3007007
Now Retrieving Bookmarks ...
4 fields will be returned
Date Added | Title | URL | Count
2009-03-08 16:26:34 | NFL.com - Official Site of the National Football League | http://www.nfl.com/ | 0
2009-03-08 16:29:28 | The National Football Post | http://www.nationalfootballpost.com/ | 0
2009-03-08 16:32:05 | Breaking news, real-time scores and daily analysis from Sports Illustrated – SI.com | http://sportsillustrated.cnn.com/ |
0
... (edited)
2012-02-13 18:09:28 | ExifTool by Phil Harvey | http://www.sno.phy.queensu.ca/~phil/exiftool/index.html | 0
2012-02-20 09:29:53 | The Perl Programming Language - www.perl.org | http://www.perl.org/ | 0
2012-02-20 09:30:10 | The CPAN Search Site - search.cpan.org | http://search.cpan.org/ | 0
473 Rows returned
sansforensics@SIFT-Workstation:~$
As expected, the Bookmark information is retained regardless of browser mode.
Now, lets see whats in the Firefox 11 "Private Browsing" Downloads info:
sansforensics@SIFT-Workstation:~$ ffparser.pl -path /cases/firefox11-prv/ -dload
Running ffparser.pl v2012-03-19
places.sqlite SQLite Version is: 3007007
downloads.sqlite SQLite Version is: 3007007
Now Retrieving Downloads ...
4 fields will be returned
Download Ended | Source | Target | Current No. Bytes
No Downloads found!
sansforensics@SIFT-Workstation:~$
No real surprises there either - the Downloads history also appears empty in "Private Browsing" mode.
And finally(!!!), here's the results of our script with Firefox 11 in non-"Private Browsing" mode. For this part, I browsed using WinXP Firefox 11 and downloaded a copy of ProcMon. I then copied the ".sqlite" files over to SIFT's "/cases/firefox11/" directory.
Here's the Firefox 11 in non-"Private Browsing" History info:
sansforensics@SIFT-Workstation:~$ ffparser.pl -path /cases/firefox11/ -hist
Running ffparser.pl v2012-03-19
places.sqlite SQLite Version is: 3007007
downloads.sqlite SQLite Version is: 3007007
Now Retrieving History ...
4 fields will be returned
Date Visited | Title | URL | Count
2012-03-19 11:54:15 | Google | http://www.google.com.au/ | 1
2012-03-19 11:55:05 | Windows Sysinternals: Documentation, downloads and additional resources | http://technet.microsoft.com/en-us/sysinternals/default.aspx | 1
2012-03-19 11:56:00 | Sysinternals Process Utilities | http://technet.microsoft.com/en-us/sysinternals/bb795533 | 1
2012-03-19 11:56:33 | Process Monitor | http://technet.microsoft.com/en-us/sysinternals/bb896645 | 1
2012-03-19 11:56:39 | ProcessMonitor.zip | http://download.sysinternals.com/files/ProcessMonitor.zip | 0
5 Rows returned
sansforensics@SIFT-Workstation:~$
And the Firefox 11 in non-"Private Browsing" Bookmark info:
sansforensics@SIFT-Workstation:~$ ffparser.pl -path /cases/firefox11/ -bk | more
Running ffparser.pl v2012-03-19
places.sqlite SQLite Version is: 3007007
downloads.sqlite SQLite Version is: 3007007
Now Retrieving Bookmarks ...
4 fields will be returned
Date Added | Title | URL | Count
2009-03-08 16:26:34 | NFL.com - Official Site of the National Football League | http://www.nfl.com/ | 0
2009-03-08 16:29:28 | The National Football Post | http://www.nationalfootballpost.com/ | 0
2009-03-08 16:32:05 | Breaking news, real-time scores and daily analysis from Sports Illustrated – SI.com | http://sportsillustrated.cnn.com/ |
0
... (edited)
2012-02-13 18:09:28 | ExifTool by Phil Harvey | http://www.sno.phy.queensu.ca/~phil/exiftool/index.html | 0
2012-02-20 09:29:53 | The Perl Programming Language - www.perl.org | http://www.perl.org/ | 0
2012-02-20 09:30:10 | The CPAN Search Site - search.cpan.org | http://search.cpan.org/ | 0
473 Rows returned
sansforensics@SIFT-Workstation:~$
Which unsurprisingly is the same results as for the "Private Browsing" Bookmarks section detailed above.
And finally, the Firefox 11 in non-"Private Browsing" Downloads output:
sansforensics@SIFT-Workstation:~$ ffparser.pl -path /cases/firefox11/ -dload
Running ffparser.pl v2012-03-19
places.sqlite SQLite Version is: 3007007
downloads.sqlite SQLite Version is: 3007007
Now Retrieving Downloads ...
4 fields will be returned
Download Ended | Source | Target | Current No. Bytes
2012-03-19 11:56:57 | http://download.sysinternals.com/files/ProcessMonitor.zip | file:///C:/ProcessMonitor.zip | 1324628
1 Rows returned
sansforensics@SIFT-Workstation:~$
Pretty cool - we can see where we went to the SysInternals website and downloaded ProcessMonitor.zip.
Some Interesting(?) Side Notes:
If you type:
about:cache
into the Firefox address bar (both 3.6 and 11), Firefox displays the current cache contents via clickable html-like interface. Note: "Private Browsing" automatically clears the cache when Firefox is closed.
With Firefox 11 in "Private Browsing"/"Forget History" mode, CCleaner v3.14.1616 will detect and remove "downloads.sqlite", "content-prefs.sqlite", "formhistory.sqlite".
With Firefox 11 in non-"Private Browsing"/"Remember History" mode, CCleaner v3.14.1616 will detect and remove the cookies (but not the "cookies.sqlite" file), the user profile cache and the following sqlite files - "downloads.sqlite", "content-prefs.sqlite", "formhistory.sqlite", "signons.sqlite". Depending on what sites are visited, some of the ".sqlite" files may not be created (eg signons.sqlite).
If you (masochistically) want to view the Firefox source code, its available here.
And we're done!
If you're still reading, it's time to grab a beverage ;)
Or if you skipped ahead to get the summary (you missed out on the fun!) - we have successfully used Perl::DBI to read Firefox 3.5.17 and 11 ".sqlite" files for history, bookmark and download information. Reading other software's ".sqlite" files will follow a similar process.
Thanks must go to Kristinn Gudjonsson for providing his original "ff3histview" script upon which this particular house of cards is based.
Hopefully, you've gotten something out of this uber long post - there is a lot of information out there and if in doubt, I added it in here.
Comments/Suggestions are welcome! I think I'll leave any SQLite carving attempts for now though - it seems a little daunting for this monkey.
