Analysis of the places.sqlite Firefox History Database

Analysis of the places.sqlite Firefox History Database

John David Pressman

Firefox history is stored in a sqlite file called ‘places.sqlite’. It can be processed and manipulated with the standard sqlite3 utility available for Linux, Mac, and Windows. However this is inconveinent because the schema is complicated and requires the user to be familiar with SQL and to write out a new query each time they want to get results from the firefox places database.

However it is still a viable way of retrieving records. Since ffox (A utility I’m working on to deal with firefox history) is written to be schema independent, to explore the places database we’ll be using the sqlite utility.

Tables

There are eleven tables in places.sqlite:


sqlite> .tables
moz_anno_attributes  moz_favicons         moz_items_annos    
moz_annos            moz_historyvisits    moz_keywords       
moz_bookmarks        moz_hosts            moz_places         
moz_bookmarks_roots  moz_inputhistory    

The three we’re interested in particular are ‘moz_places’ or the places table, moz_historyvisits and moz_bookmarks the bookmarks table. We can get the schema for each of these with the .schema command in sqlite, we’ll start with the moz_places table.

moz_places


sqlite> .schema moz_places
CREATE TABLE moz_places (   id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER , guid TEXT);
CREATE INDEX moz_places_faviconindex ON moz_places (favicon_id);
CREATE INDEX moz_places_frecencyindex ON moz_places (frecency);
CREATE UNIQUE INDEX moz_places_guid_uniqueindex ON moz_places (guid);
CREATE INDEX moz_places_hostindex ON moz_places (rev_host);
CREATE INDEX moz_places_lastvisitdateindex ON moz_places (last_visit_date);
CREATE UNIQUE INDEX moz_places_url_uniqueindex ON moz_places (url);
CREATE INDEX moz_places_visitcount ON moz_places (visit_count);

The places database stores the places (URL’s, Titles) of various webpages and keeps track of statistics like how many times they’ve been visited total.

This can be a little hard to puzzle out from just the schema, so grabbing some data from the table might be illustrative:


sqlite> select * from moz_places;
1|https://www.mozilla.org/en-US/firefox/central/||gro.allizom.www.|0|0|0||140||-SoXdLlLbzb6
2|https://www.mozilla.org/en-US/firefox/help/||gro.allizom.www.|0|0|0|1|140||GDXI4SVJZx4C
3|https://www.mozilla.org/en-US/firefox/customize/||gro.allizom.www.|0|0|0|2|140||LAusSCIPJmNM
4|https://www.mozilla.org/en-US/contribute/||gro.allizom.www.|0|0|0|3|140||zF3CZ3hpil54
5|https://www.mozilla.org/en-US/about/||gro.allizom.www.|0|0|0|4|140||lugf4p2AEEnr
6|place:sort=8&maxResults=10|||0|1|0||0||qy8FkCgjjW7O
7|place:folder=BOOKMARKS_MENU&folder=UNFILED_BOOKMARKS&folder=TOOLBAR&queryType=1&sort=12&maxResults=10&excludeQueries=1|||0|1|0||0||IGXImj8QIW3t
8|place:type=6&sort=14&maxResults=10|||0|1|0||0||D2WuBxF_KvUX
9|http://softholmsyndrome.com/||moc.emordnysmlohtfos.|2|0|1||4000|1413775816200139|DW4hqmG-uu1L
10|http://softholmsyndrome.com/about||moc.emordnysmlohtfos.|2|0|0||200|1413775817871414|oHjCTzcYpPON
11|http://softholmsyndrome.com/jd-key.gpg||moc.emordnysmlohtfos.|1|0|0||100|1413775670489873|3XrTxlRgewNc
12|http://softholmsyndrome.com/2014-08-14-the-halo-mapmakers-guild.html||moc.emordnysmlohtfos.|1|0|0||100|1413775680130922|yjcaE0Q5av7B

There are eleven columns in the places table.

id: We can see that the first column is a sequential index of the records, and as the schema points out is the primary key for this table.

url: The second column is of course the URL of the web page visited.

title: Softholmsyndrome doesn’t have titles (something I will obviously need to fix) but the third column is the title of the page.

rev_host: At first the fourth column might seem puzzling but it’s actually the hostname of the site spelled backwards.

visit_count:The next column is how many times the domain has been visited. As you can see in the entry for softholmsyndrome it’s been visited twice. Items that have never been visited are default bookmarks.

hidden: The sixth column controls whether this record shows up in the graphical history viewer included with Firefox. ffox does not respect this field when it chooses whether to display records by default. (Source: See nslBrowserHistory.hidePage)

typed: This column indicates whether the URL was typed into the address bar or not. (Source: See “nslBrowserHistory.markPageAsTyped”)

favicon_id: A unique ID for the websites favicon (the little image next to each tab).

frecency: Frequency and recency in one measure. More info on Mozilla’s frecency algorithm here.

last_visit_date: The date when the page was last visited. This is actually in PRTime format and is a 64 bit timestamp that measures microseconds since the Unix epoch. You can get a regular Unix timestamp by dividing this value by one million.

guid: The globally unique identifier for this record.

moz_historyvisits

The historyvisits table has six columns.


sqlite> .schema moz_historyvisits
CREATE TABLE moz_historyvisits (  id INTEGER PRIMARY KEY, from_visit INTEGER, place_id INTEGER, visit_date INTEGER, visit_type INTEGER, session INTEGER);
CREATE INDEX moz_historyvisits_dateindex ON moz_historyvisits (visit_date);
CREATE INDEX moz_historyvisits_fromindex ON moz_historyvisits (from_visit);
CREATE INDEX moz_historyvisits_placedateindex ON moz_historyvisits (place_id, visit_date);

This table records each visit to a web page, where in the places table there are no duplicate entries, if you visit the same page ten times historyvisits will make a record for each visit.

Again a sample of the data stored is illustrative:


sqlite> select * from moz_historyvisits;
1|0|9|1413775661907511|2|0
2|1|10|1413775666838763|1|0
3|2|11|1413775670489873|1|0
4|1|12|1413775680130922|1|0
5|0|9|1413775816200139|2|0
6|5|10|1413775817871414|1|0

id: Again, a sequential record id that acts as the primary key for the table.

from_visit: The id of the page from which this page was visited from. Eg. In the above, data entry two is the about page for softholmsyndrome and it’s from_visit value is one, which is the home page for softholmsyndrome. Because a record is made for each page visited, the id for the from_visit column doesn’t come from the places id column but the historyvisits id column so that you know from what exact visit this originated from.

place_id: The primary key id from the places table that you can reference to get information such as title and url for this record.

visit_date: The date in PRTime that the record was made.

visit_type: This helpful page on forensic wiki goes into the different visit types for a record in historyvisits.

session: The session in which these records were made.

moz_bookmarks

The bookmarks table has eleven columns:


sqlite> .schema moz_bookmarks
CREATE TABLE moz_bookmarks (  id INTEGER PRIMARY KEY, type INTEGER, fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER, guid TEXT);
CREATE UNIQUE INDEX moz_bookmarks_guid_uniqueindex ON moz_bookmarks (guid);
CREATE INDEX moz_bookmarks_itemindex ON moz_bookmarks (fk, type);
CREATE INDEX moz_bookmarks_itemlastmodifiedindex ON moz_bookmarks (fk, lastModified);
CREATE INDEX moz_bookmarks_parentindex ON moz_bookmarks (parent, position); 

A data sample:


sqlite> select * from moz_bookmarks;
1|2||0|0||||1413775649183257|1413775649183257|piV0vCpbkU4M
2|2||1|0|Bookmarks Menu|||1413775649183257|1413775650186105|wyFquRqKaWUk
3|2||1|1|Bookmarks Toolbar|||1413775649183257|1413775650184462|c66MMrFZF_Es
4|2||1|2|Tags|||1413775649183257|1413775649183257|xeNxrx-wVzpk
5|2||1|3|Unsorted Bookmarks|||1413775649183257|1413775649987860|Pk7LuigozBx6
6|1|1|3|1|Getting Started|||1413775649991070|1413775649992238|37U2fO5e-BoZ
7|2||2|3|Mozilla Firefox|||1413775649992663|1413775649999366|U2LrjyWlpiDW
8|1|2|7|0|Help and Tutorials|||1413775649993388|1413775649995166|Jgik9M-zZLLF
9|1|3|7|1|Customize Firefox|||1413775649995783|1413775649996748|X-Cdn6pFKoZy
10|1|4|7|2|Get Involved|||1413775649997283|1413775649998323|fF2EYOjKi_yq
11|1|5|7|3|About Us|||1413775649999366|1413775650000335|BnW7aEVP-Pfm
12|1|6|3|0|Most Visited|||1413775650184462|1413775650184780|KmxC4exyLc9Z
13|1|7|2|0|Recently Bookmarked|||1413775650184977|1413775650185316|0oMyEONtRB_I
14|1|8|2|1|Recent Tags|||1413775650185508|1413775650185868|KVP900gXA8wV
15|3||2|2||||1413775650186105|1413775650186105|bC4GCpAii1V7

id: The unique sequential id for each record in the database.

type: What ‘type’ of bookmark the record is. I know that type 2 is a folder and type 1 is a link.

fk: The places id of the record, used to look up additional information such as title and URL.

parent: ???

position: ???

title: The title of the page bookmarked.

keyword_id: You can add keywords to identify a bookmark, presumably this value is related somehow.

folder_type: ???

dateAdded: The time that this bookmark was added in PRTime.

lastModified: The time that this bookmark was last modified in PRTime.

guid: I know it’s a ‘globally unique identifier’ but to what I have no idea.

Bibliography:

  1. http://www.forensicswiki.org/wiki/Mozilla_Firefox_3_History_File_Format
  2. https://developer.mozilla.org/en-US/docs/Mozilla/Tech/Places/Database
  3. https://developer.mozilla.org/en-US/docs/Mozilla/Tech/Places/History_Service_Design
  4. http://articles.forensicfocus.com/2012/03/09/firefox-cache-format-and-extraction/
  5. http://articles.forensicfocus.com/2012/03/09/firefox-cache-format-and-ex
  6. https://code.google.com/p/firefox-cache-forensics/wiki/FfFormat
  7. https://support.mozilla.org/en-US/questions/835204
  8. https://developer.mozilla.org/en/docs/PRTime
  9. https://developer.mozilla.org/en-US/docs/Mozilla/Tech/Places/Frecency_algorithm
  10. https://developer.mozilla.org/en-US/docs/Mozilla/Tech/Places/Using_the_Places_history_service