Using Perl and SQLite in digital forensics: Firefox Download Manager as an example

Hi,

In computer forensics there are some quite relevant artifacts used to extract information: those related to Web browsers. In modern IT, most of the activity in user desktops happens in the browsers, so it becomes crucial to have a good insight of the operations carried on them in order to be able to use the information in our processes.

In this article we will discuss the feasibility of using Perl to perform scriptable forensics in browsers. We are going to use as an example Mozilla Firefox under Linux, as it uses SQLite databases to store browser activity related information. That will give us the opportunity to use Perl’s DBI module to perform console database queries. Please note that the described process and the code can be easily ported to Windows, so feel free to play around with it, reporting back your experience if you so wished. My recommendation, if you plan to perform testing in Windows, is to use ActivePerl from Activestate.

A little bit of background

Once installed, Firefox creates a set of folders and files under /home/username/.mozilla/firefox. One of them is the profiles.ini, which contains some start up information such as the profile to be used, stored in the Path variable:

Inside the profile directory we can easily locate several SQLite files we can use in our investigative process. As an example, we will use the downloads.sqlite file. Since the introduction of Firefox 3, this file stores information regarding download operations performed in Firefox. The Download Manager uses this SQLite table to store and track downloads in progress, queued and finished downloads. However, any of the SQLite files could be used modifying the statements in the example code according to the documentation for each schema. Note that Firefox is an open source project, therefore, apart from available code to inspect, almost everything is documented in their development resources, including but not limited to database schema layouts, naming conventions and variable formats.

Using the sqlite3 toolset we can easily inspect the tables in the schema, enabling the headers print for the table queries :

This file does only have one table, called moz_downloads with the following columns: id, name, source, target, tempPath, startTime, endTime, state, referrer, entityID, currBytes, maxBytes, mimeType, preferredApplication, preferredAction and autoResume. We will use this information for our script in a little while. As explained before, those are documented in Mozilla’s dev sites, so feel free to browse the documentation and find out the purpose of each element.

Using Perl to retrieve the contents

One of the main advantages of using Perl in the forensic practice is that it allows the investigators to script operations with excellent capabilities when dealing with regular expressions, making languages such as Python and Perl very attractive for these purposes. The code we are going to use is the following:

#!/usr/bin/perl

use DBI;

    my $dbh = DBI->connect("dbi:SQLite:dbname=/home/shernando/.mozilla/firefox/fczslsx1.default/downloads.sqlite","","");
    if ($dbh->err()) { die "$DBI::errstr\n"; }

    $sth = $dbh->prepare("select * from moz_downloads");
    $sth->execute();
    
    while (@row = $sth->fetchrow) {

    print "Download ID: "."@row[0]\n";
    print "File name: "."@row[1]\n";
    print "Download URL: "."@row[2]\n";
    print "Local file name: "."@row[3]\n";  
    print "Temp path (if available): "."@row[4]\n";

    # Firefox uses PRTime time formats, which are 64-bit integer number of microseconds since Midnight 1 January 1970 UTC#
    # We can invoke Unix / POSIX dividing by 1,000,000 #

        $starttime = localtime(@row[5]/1000000);
        $endtime = localtime(@row[6]/1000000);

    print "Start time: "."@row[5]"." "."$starttime\n";
    print "End time: "."@row[5]"." "."$endtime\n";
    print "State: "."@row[7]\n"; 
    print "Referer: "."@row[8]\n";  
    print "Entity ID: "."@row[9]\n";
    print "Current bytes: "."@row[10]\n"; 
    print "Max. bytes: "."@row[11]\n";
    print "Mime type: "."@row[12]\n";     
    print "Preferred application: "."@row[13]\n";  
    print "Preferred action: "."@row[14]\n";  
    print "Auto Resume: "."@row[15]\n"; 
    print "-------------------------------\n";
    }

    $sth->finish;

    $dbh->disconnect();

Please note the transformation required to bring PRTtime to Unix/POSIX in order to make human readable the start and end dates. Needless to say, make sure you declare an adequate path for your SQLite file, otherwise the script will not render any results.

The script in action

As seen in the previous sqlite3 query, there are 3 downloads in the inspected system. First two are completed regular downloads:

Third download is a slightly different case:

The State: 4 indicates this is a paused download, and this obviously generates a temporary file and an entity ID that is used to resume the download after it’s been paused. Of course the current bytes count differs from the max. bytes, as the download is not completed.

Hope this information is useful for your investigative processes. In my particular case, I find that writing these quick and dirty scripts makes my life much easier :)

Comments and questions are welcome.

With regards,