SANS Forensic Artifact 5: Downloads.sqlite

I thought I'd get through this next artifact fairly quickly as again I've done some work prior with my Firefox script which has the option available to parse the information out of the Downloads.sqlite database.

Please note that the last category should have been posted as Artifact 4, I've adjusted that, and therefore this makes Artifact number 5 on the poster.

SANS lists the following information within the poster within their File Download Category

Downloads.sqlite
Description:
Firefox has a built-in download manager application which keeps a history of every file downloaded by the user. This browser artifact can provide excellent information about what sites a user has been visiting and what kinds of files they have been downloading from them.

Location: Firefox
XP %userprofile%\Application Data\Mozilla\ Firefox\Profiles\<random text>.default\downloads.sqlite
Win7 %userprofile%\AppData\Roaming\Mozilla\ Firefox\Profiles\<random text>.default\downloads.sqlite
Interpretation:
Downloads.sqlite will include:
• Filename, Size, and Type
• Download from and Referring Page
• File Save Location
• Application Used to Open File
• Download Start and End Times

While we are on this topic I thought it might be timely to touch on a recent post by Patrick Olsen over at the System Forensics blog. Patrick posted this week about the creation of a new tool that he'd been working upon named BARFF which stands for Browser Artifact Recovery Forensic Framework. This tool is beneficial for both my last and current post but in particular the SANS poster category of "Browser Forensics". I haven't had the chance to download a copy myself as yet but I encourage anyone to give it a go and provide him with your feedback.

In terms of the structure of the Downloads.sqlite database and any of the databases associated with Firefox David Koepi has an excellent resource available here which will provide a strong resource for those wanting to get started on browser forensics. I thought it would be beneficial to first download a number of applications through Firefox and then using SQLite Manager, a plugin for firefox, we can run an initial query and take a look at what we see.

From the above screenshot there are a number of items we can use from a forensic perspective:

• The name which contains the name of the executable
• The source which contains the source of where the file was downloaded from.
• The target file path
• Start and end time which is what we'll use within our timelines
• The state of the download as mentioned by David Koepi
• "0"  in the state object indicates download is in progress
• "1" in the state object indicates download is successful
• "3" indicates download is cancelled
• "4" indicates download is paused

• We have a referer  field for the referring site
•  Although not shown well in the above screenshot two important fields are preferredApplication and preferredAction which show the default application for opening 
• "0" states that the file has been saved
• "4" I believe states that it was open with a preferred application but more testing is required
• In my tests i was unable to populate the preferredApplication field and again  some further testing is required
• Lastly the currBytes and MaxBytes which can be used for a comparison between how large the file is in comparison to what has actually been downloaded.

In my example in the screenshot above I cancelled the download of FTK 4.1 and that is reflected by the state of 3 and MaxBytes lists it as -1. Important to note that this database is updated to reflect the same view as the one viewed in the graphical downloads window. Should a user delete all of the entries or remove individual downloads then this will also remove it from the database. As well as the tool mentioned above I've also created a number of Perl scripts or their converted executable to parse this information. Lets take a look at how we'd run those tools and compare the output.

To run the command you can run something like the following and obviously be aware that you're profile will be in a different location to mine.

 firefox.exe -d -p C:/Documents and Settings/username/Application Data/Mozilla/Firefox/Profiles/fd9zh9ag.default -s WORKSTATION -u USERNAME > c:\temp\events.txt  

Again this parses to Harlan's TLN timeline format and you can then convert it with the parse.pl/exe script that Harlan provides and turn this into a spreadsheet for your analysis.The output is the following.

 1353362936|FIREFOX|WORKSTATION|USERNAME|dl:winscp511setup.exe src:http://download.winscp.net/download/files/201211192201657b470a18225537e6515b0e998929a1/winscp511setup.exe cB:4854080 mB:4854080  
 1353364779|FIREFOX|WORKSTATION|USERNAME|dl:sav32sfx(1).exe src:http://downloads.sophos.com/tools/sav32sfx.exe cB:72805712 mB:72805712  
 1353364806|FIREFOX|WORKSTATION|USERNAME|dl:483_ides.zip src:http://downloads.sophos.com/downloads/ide/483_ides.zip cB:3656900 mB:3656900  
 1354486120|FIREFOX|WORKSTATION|USERNAME|dl:googletalk-setup.exe src:http://dl.google.com/googletalk/googletalk-setup.exe cB:1606064 mB:1606064  
 1354493698|FIREFOX|WORKSTATION|USERNAME|dl:FTK 4.1.0 Intl.iso src:https://ad-iso.s3.amazonaws.com/FTK%204.1.0%20Intl.iso cB:0 mB:-1  

Although Chrome is not specifically mentioned I felt it was of equal importance in this category and therefore it was best I showed examples for both. Again with these examples its important that when testing these tools you note the time that you download each of the files and confirm in the output, as we did in the last post, that your timeline produces the correct time while at the same time understanding any conversions required from UTC to local time.

Again I opened the database, the History file, using SQLite Manager

In this case we don't have as much detail in the downloads table as we do with the downloads database within firefox. Once again I ran the command using a similar command to the one we used above however this time using my chrome script

 chrome -d -p "C:\Documents and Settings\username\Local Settings\Application Data\Google\Chrome\User Data\Default" -s WORKSTATION -u USERNAME > c:\temp\chrome_events.txt  

The output is the following.

 1347264951|CHROME|WORKSTATION|USERNAME|dl:C:\Documents and Settings\username\My Documents\Downloads\ChromeSetup (1).exe src:https://dl.google.com/tag/s/appguid%3D%7B8A69D345-D564-463C-AFF1-A69D9E530F96%7D%26iid%3D%7B017F3DCB-E134-832D-5F16-ACB3A3AAB5E1%7D%26lang%3Den%26browser%3D4%26usagestats%3D0%26appname%3DGoogle%2520Chrome%26needsadmin%3Dfalse/update2/installers/ChromeSetup.exe cB:739808 mB:739808  
 1354584249|CHROME|WORKSTATION|USERNAME|dl:C:\Documents and Settings\username\My Documents\Downloads\sav32sfx (1).exe src:http://downloads.sophos.com/tools/sav32sfx.exe cB:72805712 mB:72805712  
 1354584279|CHROME|WORKSTATION|USERNAME|dl:C:\Documents and Settings\username\My Documents\Downloads\googletalk-setup (1).exe src:http://dl.google.com/googletalk/googletalk-setup.exe cB:1606064 mB:1606064  
 1354584297|CHROME|WORKSTATION|USERNAME|dl:C:\Documents and Settings\username\My Documents\Downloads\sav32sfx (2).exe src:http://downloads.sophos.com/tools/sav32sfx.exe cB:72805712 mB:72805712  

Well I think I've discussed this topic enough. Again not overly complex but like everything you'll have a return on your tools if you understand what they do and have some assurance that the output is expected and once again you're aware of any adjustments you may need to make to ensure you're looking at the correct local time if required. If you're looking for the tools mentioned above that I've written you can find them at the following location http://code.google.com/p/sploited/downloads/list



[1] http://davidkoepi.wordpress.com/2010/11/27/firefoxforensics/
[2] http://computer-forensics.sans.org/blog/2010/01/21/google-chrome-forensics/
[3] http://renaissancesecurity.blogspot.com.au/2011/04/firefox-4-browser-forensics-part-4.html
[4] https://wiki.mozilla.org/images/d/d5/Places.sqlite.schema3.pdf