Wednesday, 4 July 2012

Browser Forensics using TLN timelines

I've posted previously about a number of methods to create forensic timelines and depending on the incident you are handling it can be critical in the investigation to understand the browser history. Log2Timeline already has this functionality and it can automatically search your forensic image and import any browser history files it finds. Unfortunately an analyst may not always have a forensic image and may need to conduct analysis in a live response scenerio as I previously discussed in Timelines-for-live-response.

One of the reasons I lean towards Harlan Carvey's TLN timeline format is because of the flexibility it provides to my investigation. As shown in my previous blog I can easily convert required perl scripts to executables and copy the timeline tools I require to the target machine. I can also simply run the tools from a USB drive. The TLN timeline provides me with the flexibility to add required sources to an investigation or if required add information at a later stage should an artifact of interest be found. Due to the reasons above I was keen to be able to parse the Firefox places.sqlite history database as well as the Chrome History sqlite database into the TLN format. Although there are a simple executable and tools that can alreadyparse to csv or various formats it means having multiple files and referencing the two. The main advantage of the timeline is having everything you need in the one place so that you can see what occurs between different applications at points of interest.

So in saying that I decided to take this task on board and create two files that will parse the Firefox and Chrome history files into the TLN format. Please note these are in BETA and are provided with no warranty. I'm yet to have the time to effectively test these files and while i'm able to put together a script by no means would I consider myself an experienced programmer. Hopefully any of the code guru's out there can see if and where I might have failed and shoot a comment my way to assist with making this code useful to the community.

To create the script copy the following file into a text file and save it as firefox.pl. Use perl2exe to convert to an executable if required.

firefox.pl

 #! c:\perl\bin\perl.exe  
 #---------------------------------------------------------------------  
 # firefox.pl  
 # Parse the firefox places.sqlite database to TLN format  
 #  
 # Ref: http://davidkoepi.wordpress.com/2010/11/27/firefoxforensics/  
 #  
 # Version: 0.1 (BETA)  
 #---------------------------------------------------------------------  
 use DBI;  
 use strict;  
 use Getopt::Long;  
 my %config = ();  
 Getopt::Long::Configure("prefix_pattern=(-|\/)");  
 GetOptions(\%config, qw(path|p=s system|s=s help|?|h));  
 if ($config{help} || ! %config) {  
     _syntax();  
     exit 1;  
 }  
 die "You must enter a path.\n" unless ($config{path});  
 #die "File not found.\n" unless (-e $config{path} && -p $config{path});  
 my $path =$config{path};  
 my $db = DBI->connect("dbi:SQLite:dbname=$path\\places.sqlite","","") || die( "Unable to connect to database\n" );  
 my $all = $db->selectall_arrayref("SELECT url,moz_places.last_visit_date/1000000 from moz_places order by moz_places.last_visit_date desc;");  
 foreach my $row (@$all) {  
     my ($url,$date) = @$row;  
     print $date."|FIREFOX|".$config{system}."|".$url."\n";  
 }  
 $db->disconnect;  
 sub _syntax {  
 print<< "EOT";  
 firefox.pl  
 [option]  
 Parse Firefox places.sqllite (Win2000, XP, 2003, Win7)  
  -p Path..................path to places.sqllite file to parse  
  -s Systemname............add systemname to appropriate field in tln file  
  -h Help..................Help (print this information)  
 Ex: C:\\> firefox.pl -f C:\\firefox\ -s COMPUTERNAME > events.txt  
 **All times printed as GMT/UTC    
 EOT  
 }   

chrome.pl

 #! c:\perl\bin\perl.exe  
 #---------------------------------------------------------------------  
 # chrome.pl  
 # Parse the History sqlite database to TLN format  
 #  
 # Ref: http://www.forensicswiki.org/wiki/Google_Chrome  
 # Ref: http://computer-forensics.sans.org/blog/2010/01/21/google-chrome-forensics/  
 #  
 # Version: 0.1 (BETA)  
 #---------------------------------------------------------------------  
 use DBI;  
 use strict;  
 use Getopt::Long;  
 my %config = ();  
 Getopt::Long::Configure("prefix_pattern=(-|\/)");  
 GetOptions(\%config, qw(path|p=s system|s=s help|?|h));  
 if ($config{help} || ! %config) {  
     _syntax();  
     exit 1;  
 }  
 die "You must enter a path.\n" unless ($config{path});  
 #die "File not found.\n" unless (-e $config{path} && -p $config{path});  
 my $path=$config{path};  
 my $db = DBI->connect("dbi:SQLite:dbname=$path\\History","","") || die( "Unable to connect to database\n" );  
 my $all = $db->selectall_arrayref("SELECT ((visits.visit_time/1000000)-11644473600), urls.url, urls.title FROM urls, visits WHERE urls.id = visits.url;");  
 foreach my $row (@$all) {  
     my ($date,$url,$title) = @$row;  
     print $date."|CHROME|".$config{system}."|".$url."\n";  
 }  
 $db->disconnect;  
 sub _syntax {  
 print<< "EOT";  
 chrome.pl  
 [option]  
 Parse Chrome History (Win2000, XP, 2003, Win7)  
  -p Path..................path to History file to parse  
  -s Systemname............add systemname to appropriate field in tln file  
  -h Help..................Help (print this information)  
 Ex: C:\\> chrome.pl -f C:\\Users\%username%\AppData\Local\Google\Chrome\User Data\Default\ -s COMPUTERNAME > events.txt  
 **All times printed as GMT/UTC    
 EOT  
 }   

Once you have the output from the following files you can then use Harlans parse.pl or parse.exe to convert this into your CSV timeline format.
I hope you find this useful.

9 comments:

  1. First, Congratulations for your work!

    I've just copied the firefox.pl code, saved and ran. I've got the error 'Can't find string terminator "EOT" anywhere before EOF'.

    What is causing this error? I'm not a expert in Perl...
    Am I doing anything wrong?

    Regards,

    Carlos Marins.

    ReplyDelete
    Replies
    1. Hi Carlos,

      Thanks for your post. Unfortunately when I posted the code into blogspot its added and removed some white space in strange places. To resolve this issue I removed all of the white space after EOT and this resolved the issue.

      Let me know how you go. I'll endeavour to host the code somewhere securely so that readers can download these files in future and hopefully not have these problems.

      Cheers

      Sploit

      Delete
  2. I look forward to trying this code out...

    ReplyDelete
    Replies
    1. Thanks Harlan, keep me updated if you make or need any improvements.

      Delete
  3. Keep getting this error DBD::SQLite::db selectall_arrayref failed: no such table: moz_places

    ReplyDelete
    Replies
    1. Doing some tests tonight an error can be generated if you add the \ to the end of the path. If you try adding or removing the \ off the end of the path and see if this helps. I'll see if i can fix this small bug and update the code also.

      Please see response below also if the above does not resolve your issue.

      Delete
  4. Thanks for the feedback anonymous. Does your path have spaces within it and if so did you put it within quotes when running the tool? The error means that the tool cannot find the moz_places table within places.sqlite. Feel free to post more if you're still having trouble. Want to know people are having success.

    ReplyDelete
  5. Hi there is no places.sqlite in the user profile, thank you for answering my earlier question

    ReplyDelete
  6. Sploit,

    I've recently really started digging into the specifications for the places.sqlite database, and what's available, and found that there is actually a great deal of very useful information available.

    I greatly appreciate you sharing your code...the same with Cheeky:
    http://cheeky4n6monkey.blogspot.com/2012/03/using-perl-to-view-sqlite-database.html

    ReplyDelete