Authors: TJ O'Connor
In the last section, we examined a single application database stored by the Skype application. The database provided a great deal of forensically rich data for investigation. In this section, we will examine what the Firefox application stores in a series of databases. Firefox stores these databases in a default directory located at C:\Documents and Settings\
investigator$ ls ∗.sqlite
places.sqlite downloads.sqlite search.sqlite
addons.sqlite extensions.sqlite signons.sqlite
chromeappsstore.sqlite formhistory.sqlite webappsstore.sqlite
content-prefs.sqlite permissions.sqlite
cookies.sqlite places.sqlite
Examining the directory listing, it appears obvious that Firefox stores quite a bit of forensically rich data. But where should an investigator begin? Let’s start
with the
downloads.sqlite
database. The file
downloads.sqlite
stores information about the files downloaded by a Firefox user. It contains a single table named
moz_downloads
that stores information about the file name, source downloaded from, date downloaded, file size, referrer, and locally stored location of the file. We use a Python script to execute an SQLite SELECT statement for the appropriate columns: name, source, and datetime. Notice that Firefox does something interesting with the Unix epoch time we previously learned about. To store the Unix epoch time in the database, it multiplies by the number of seconds since January 1st, 1970 by 1,000,000. Thus, to properly format our time, we need to divide by 1 million.
import sqlite3
def printDownloads(downloadDB):
conn = sqlite3.connect(downloadDB)
c = conn.cursor()
c.execute(‘SELECT name, source, datetime(endTime/1000000,\
\’unixepoch\’) FROM moz_downloads;’
)
print ‘\n[∗] --- Files Downloaded --- ’
for row in c:
print ‘[+] File: ’ + str(row[0]) + ‘ from source: ’ \
+ str(row[1]) + ‘ at: ’ + str(row[2])
if __name__ == “__main__”:
main()
Running the script against the
downloads.sqlite
file, we see that this profile contains information about a file we previously downloaded. In fact, we downloaded this file in one of the previous sections to learn more about metadata.
investigator$ python firefoxDownloads.py
[∗] --- Files Downloaded ---
[+] File: ANONOPS_The_Press_Release.pdf from source: http://www.wired.com/images_blogs/threatlevel/2010/12/ANONOPS_The_Press_Release.pdf at: 2011-12-14 05:54:31
Excellent! We now know when a user downloaded specific files using Firefox. However, what if an investigator wants to log back onto sites that use authentication? For example, what if a police investigator determined a user downloaded images that depicted harmful actions towards children from a web-based email site? The police investigator (lawfully) would want to log back onto the web-based email, but most likely lacks the password or authentication to the user’s web-based email. Enter cookies. Because the HTTP protocol lacks a stateful design, origin Web sites utilize cookies to maintain state.
Consider, for example, when a user logs onto a web-based email: if the browser could not maintain cookies, the user would have to log on in order to read every individual email. Firefox stores these cookies in a database named
cookies.sqlite
. If an investigator can extract cookies and reuse them, it provides the opportunity to log on to resources that require authentication.
Dealing With Encrypted Database Error
Updating Sqlite3
You may notice that if you attempt to open the cookies.sqlite database with the default Sqlite3 installation from Backtrack 5 R2, that it
reports file is encrypted or is not a database
. The default installation of Sqlite3 is Sqlite3.6.22, which does not support WAL journal mode. Recent versions of Firefox use the PRAGMA journal_mode=WAL in their cookies.sqlite and places.sqlite databases. Attempting to open the file with an older version of Sqlite3 or the older Python-Sqlite3 libraries will report an error.
investigator:∼# sqlite3.6 ∼/.mozilla/firefox/nq474mcm.default/cookies.sqlite
SQLite version 3.6.22
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> select ∗ from moz_cookies;
Error: file is encrypted or is not a database
After upgrading your Sqlite3 binary and Pyton-Sqlite3 libraries to a version > 3.7, you should be able to open the newer Firefox databases.
investigator:∼# sqlite3.7 ∼/.mozilla/firefox/nq474mcm.default/cookies.sqlite
SQLite version 3.7.13 2012-06-11 02:05:22
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> select ∗ from moz_cookies;
1|backtrack-linux.org|__<..SNIPPED..>
4|sourceforge.net|sf_mirror_attempt|<..SNIPPED..>
To avoid our script crashing on this unhandled error, with the cookies.sqlite and places.sqlite databases, we put exceptions to catch the encrypted database error message. To avoid receiving this error, upgrade your Python-Sqlite3 library or use the older Firefox cookies.sqlite and places.sqlite databases included on the companion Web site.
Let’s write a quick Python script to extract cookies from a user under investigation. We connect to the database and execute our SELECT statement. In
the database, the moz_cookies maintains the data regarding cookies. From the moz_cookies table in the
cookies.sqlite
database, we will query the column values for host, name, and cookie value, and print them to the screen.
def printCookies(cookiesDB):
try:
conn = sqlite3.connect(cookiesDB)
c = conn.cursor()
c.execute(‘SELECT host, name, value FROM moz_cookies’)
print ‘\n[∗] -- Found Cookies --’
for row in c:
host = str(row[0])
name = str(row[1])
value = str(row[2])
print ‘[+] Host: ’ + host + ‘, Cookie: ’ + name \
+ ‘, Value: ’ + value
except Exception, e:
if ‘encrypted’ in str(e):
print ‘\n[∗] Error reading your cookies database.’
print ‘[∗] Upgrade your Python-Sqlite3 Library’
An investigator may also wish to enumerate the browser history. Firefox stores this data in a database named
places.sqlite.
Here, the moz_places table gives us valuable columns that include information about when (date) and where (address) a user visited a site. While our script for printHistory() only takes into account the moz_places table, the ForensicWiki Web site recommends using data from both the moz_places table and the moz_historyvisits table as well to get a live browser history (
Forensics Wiki, 2011
).
def printHistory(placesDB):
try:
conn = sqlite3.connect(placesDB)
c = conn.cursor()
c.execute(“select url, datetime(visit_date/1000000, \
‘unixepoch’) from moz_places, moz_historyvisits \
where visit_count > 0 and moz_places.id==\
moz_historyvisits.place_id;”)
print ‘\n[∗] -- Found History --’
for row in c:
url = str(row[0])
date = str(row[1])
print ‘[+] ’ + date + ‘ - Visited: ’ + url
except Exception, e:
if ‘encrypted’ in str(e):
print ‘\n[∗] Error reading your places database.’
print ‘[∗] Upgrade your Python-Sqlite3 Library’
exit(0)
Let’s use the last example and our knowledge of regular expressions to expand the previous function. While browser history is infinitely valuable, it would be useful to look deeper into some of the specific URLs visited. Google search queries contain the search terms right inside of the URL, for example. In the wireless section, we will expand on this in great depth. However, right now, let’s just extract the search terms right out of the URL. If we spot a URL in our history that contains
Google
, we will search it for the characters
q=
followed by an &. This specific sequence of characters indicates a Google search. If we do find this term, we will clean up the output by replacing some of the characters used in URLs to pad whitespace with actual whitespace. Finally, we will print out the corrected output to the screen. Now we have a function that can search the
places.sqlite
file for and print out Google search queries.
import sqlite3, re
def printGoogle(placesDB):
conn = sqlite3.connect(placesDB)
c = conn.cursor()
c.execute(“select url, datetime(visit_date/1000000, \
‘unixepoch’) from moz_places, moz_historyvisits \
where visit_count > 0 and moz_places.id==\
moz_historyvisits.place_id;”)
print ‘\n[∗] -- Found Google --’
for row in c:
url = str(row[0])
date = str(row[1])
if ‘google’ in url.lower():
r = re.findall(r’q=.∗\&’, url)
if r:
search=r[0].split(‘&’)[0]
search=search.replace(‘q=’, ‘’).replace(‘+’, ‘ ’)
print ‘[+] ’+date+‘ - Searched For: ’ + search
Wrapping it all together, we now have functions to print downloaded files, cookies, the history of a profile, and even print out the terms a user goggled.
The option parsing should look very similar to our script to investigate the Skype profile database, from the previous section.
You may notice the use of the function os.path.join when creating the full path to a file and ask why we do not just add the string values for the path and the file together. What prevents us from using an example such as
downloadDB = pathName + “\\downloads.sqlite” instead of
downloadDB = os.path.join(pathName,“downloads.sqlite”)
Consider this: Windows uses a path file of C:\Users\
and
Mac OS.
With that sidebar aside, we have a complete working script to do some serious investigations into a Firefox profile. For practice, try adding some addition functions to this script and modify it for your own investigations.
import re
import optparse
import os
import sqlite3
def printDownloads(downloadDB):
conn = sqlite3.connect(downloadDB)
c = conn.cursor()
c.execute(‘SELECT name, source, datetime(endTime/1000000,\
\’unixepoch\’) FROM moz_downloads;’
)
print ‘\n[∗] --- Files Downloaded --- ’
for row in c:
print ‘[+] File: ’ + str(row[0]) + ‘ from source: ’ \
+ str(row[1]) + ‘ at: ’ + str(row[2])
def printCookies(cookiesDB):
try:
conn = sqlite3.connect(cookiesDB)
c = conn.cursor()
c.execute(‘SELECT host, name, value FROM moz_cookies’)
print ‘\n[∗] -- Found Cookies --’
for row in c:
host = str(row[0])
name = str(row[1])
value = str(row[2])
print ‘[+] Host: ’ + host + ‘, Cookie: ’ + name \
+ ‘, Value: ’ + value
except Exception, e:
if ‘encrypted’ in str(e):
print ‘\n[∗] Error reading your cookies database.’
print ‘[∗] Upgrade your Python-Sqlite3 Library’
def printHistory(placesDB):
try:
conn = sqlite3.connect(placesDB)
c = conn.cursor()
c.execute(“select url, datetime(visit_date/1000000, \
‘unixepoch’) from moz_places, moz_historyvisits \
where visit_count > 0 and moz_places.id==\
moz_historyvisits.place_id;”)
print ‘\n[∗] -- Found History --’
for row in c:
url = str(row[0])
date = str(row[1])
print ‘[+] ’ + date + ‘ - Visited: ’ + url
except Exception, e:
if ‘encrypted’ in str(e):
print ‘\n[∗] Error reading your places database.’
print ‘[∗] Upgrade your Python-Sqlite3 Library’
exit(0)
def printGoogle(placesDB):
conn = sqlite3.connect(placesDB)
c = conn.cursor()
c.execute(“select url, datetime(visit_date/1000000, \
‘unixepoch’) from moz_places, moz_historyvisits \
where visit_count > 0 and moz_places.id==\
moz_historyvisits.place_id;”)
print ‘\n[∗] -- Found Google --’
for row in c:
url = str(row[0])
date = str(row[1])
if ‘google’ in url.lower():
r = re.findall(r’q=.∗\&’, url)
if r:
search=r[0].split(‘&’)[0]
search=search.replace(‘q=’, ’’).replace(‘+’, ‘ ’)
print ‘[+] ’+date+’ - Searched For: ’ + search
def main():
parser = optparse.OptionParser(“usage%prog “+\
“-p
parser.add_option(‘-p’, dest=’pathName’, type=’string’,\
help=’specify skype profile path’)
(options, args) = parser.parse_args()
pathName = options.pathName
if pathName == None:
print parser.usage
exit(0)
elif os.path.isdir(pathName) == False:
print ‘[!] Path Does Not Exist: ’ + pathName
exit(0)
else:
downloadDB = os.path.join(pathName, ‘downloads.sqlite’)
if os.path.isfile(downloadDB):
printDownloads(downloadDB)
else:
print ‘[!] Downloads Db does not exist: ’+downloadDB
cookiesDB = os.path.join(pathName, ‘cookies.sqlite’)
if os.path.isfile(cookiesDB):
pass
printCookies(cookiesDB)
else:
print ‘[!] Cookies Db does not exist:’ + cookiesDB
placesDB = os.path.join(pathName, ‘places.sqlite’)
if os.path.isfile(placesDB):
printHistory(placesDB)
printGoogle(placesDB)
else:
print ‘[!] PlacesDb does not exist: ’ + placesDB
if __name__ == ‘__main__’:
main()