Tuesday, April 17, 2012

Mozilla Firefox SQLite database records user browsing history

Topic : Mozilla Firefox 10.0.1 history user database, Mozilla Firefox sqlite database, Mozilla Firefox user browsing forensics, Mozilla Firefox SQLite database engine


I remember reading an article about Mozilla Firefox and how IT forensics use the SQLite database within Firefox for user's web browsing history (CyberSecurity eSecurity bulletin, Vol 23, Q2/2010 - http://www.cybersecurity.my/data/content_files/12/725.pdf). For all of you, the Mozilla Firefox in your PC has a SQLite database which records all user activity, and all user profiles too. These activities include browsing history, login details ( if saved), forms input history , cooking, bookmarks and so forth. A lot of info ! Smiley

I thought of checking out Mozilla Firefox (latest version 10.0.1 as of today) in my PC for such database, and yes there is !
I used SQLite Manager add-on for Firefox to browse the contents of the databases.

Details:

a) Database location - "C:\Users\\AppData\Roaming\Mozilla\Firefox\Profiles\xxxxxxxx.default" (Windows 7)
b) Tool used - SQLite Manager : Add-on for Firefox
c) Total databases - 12 databases (see screenshot below)
d) Database under review - places.sqlite (see screenshot below)


You can just browse the databases for various info such as Bookmarks info, Visited URL info, Visit history info, and Form input info. Do take note that the time format in these databases is not in a user friendly form. Use the SQL queries below to get the date/time in GMT format.

I ran the following SQL commands to get info on my browsing history
(in database places.sqlite):

i) SELECT datetime (moz_historyvisits.visit_date/1000000,"unixepoch"), moz_places.url FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.id
Gets info on the history of visits

ii) SELECT datetime (moz_places.last_visit_date/1000000,"unixepoch"), moz_places.url , moz_places.title FROM moz_places
Gets data/time, URL and page title of all recorded web sites visits

As you can see ( based on the screenshots below), there is a lot of information about your web browsing history that you can get from these databases. This can help in terms of forensics... or monitor if your roommate is using your laptop without your knowledge !

You can also use certain tools to analyze these tables, such as FoxAnalysis Plus ( see reference below ).

What a world we live in .......


Screenshots of Mozilla Firefoz SQLite database contents ( database places.sqlite )
Photobucket
List of databases for Mozilla Firefox, 12 databases in all....


Photobucket
SQL query to see browsing history using SQLite Manager for Firefox....


Photobucket
SQL query to see total browsing history (last visited) with page title using SQLite Manager for Firefox....




References
CyberSecurity eSecurity bulletin, Vol 23, Q2/2010
http://www.cybersecurity.my/data/content_files/12/725.pdf


SQLite Manager : Add-on for Firefox
https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/


FoxAnalysis Plus
http://forensic-software.co.uk/foxanalysis.aspx


Firefox Forensics
http://www.machor-software.com/firefox_forensics

5 comments:

  1. I also found out that Google Chrome also stores it's user browsing history in a SQLite database. You can get more info about it here : Google Chrome Forensics (http://computer-forensics.sans.org/blog/2010/01/21/google-chrome-forensics/)

    ReplyDelete
  2. Really helpful, was banging my head at the comparing date part, the /1000 was exaclty what I needed. Thanks!

    ReplyDelete
  3. BTW, you a little type in your first query, is "moz_places.id = moz_historyvisits.place_id"

    ReplyDelete
  4. This is my final approach.

    select moment, url from (select place_id, datetime(visit_date/1000000, 'unixepoch', 'localtime') as moment
    from moz_historyvisits ) tab inner join
    moz_places on tab.place_id = moz_places.id order by moment;

    ReplyDelete
  5. Take a look on a free tool -- Valentina Studio. Amazing product! IMO this is the best manager for SQLite for all platforms. http://www.valentina-db.com/en/valentina-studio-overview

    ReplyDelete