Firefox/Browsing history database

This guide shows how to read the browsing history database created by the Mozilla Firefox web browser. The title of this page can be understood as both "browsing through the database" and "the database that contains the browsing history".

The database is named places.sqlite and located in the profile folder and is in the SQL (Structured Query Language) 3 format. To facilitate using the commands, you may navigate to the profile folder directory, copy the database file into your working directory, or put the path to the database file in a variable.

Simple searching

Some data can be viewed directly through text editors and command line tools, but they are inconveniently wrapped in a mess of non-human-readable binary data.

When viewed through the command line simple checks for the existence of a title or URL can be performed using grep -a -i -l "string" places.sqlite (parameters: search binary file, case insensitivity, list files only). Title searches can be performed using grep -a -i -o -P ".{30}string.{30}" places.sqlite, where thirty characters around the string are also shown for context.

grep -P "[A-Za-z0-9/]" can partially filter out junk characters for a |less pipe. Additionally, |sed -r "s/http/\nhttp/g" creates a line break before each URL.

Listing the "tables"

First, we have to find the names of the "tables" that the database contains. These are the various areas within the database file that store parameters including the main history (moz_places), bookmarks (moz_bookmarks), favicons (moz_favicons), and user-specified keywords (moz_keywords).

This can be done either directly,

sqlite3  places.sqlite .tables

…or within an sqlite3 session.

$ sqlite3 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_deleted  moz_inputhistory     
sqlite>

The tables may vary depending on the browser's version.

A list of columns within a table can be obtained through pragma table_info, as shown here in this example:

$ sqlite3 places.sqlite "pragma table_info(moz_places)"
0|id|INTEGER|0||1
1|url|LONGVARCHAR|0||0
2|title|LONGVARCHAR|0||0
3|rev_host|LONGVARCHAR|0||0
4|visit_count|INTEGER|0|0|0
5|hidden|INTEGER|1|0|0
6|typed|INTEGER|1|0|0
7|favicon_id|INTEGER|0||0
8|frecency|INTEGER|1|-1|0
9|last_visit_date|INTEGER|0||0
10|guid|TEXT|0||0

$ sqlite3 places.sqlite "pragma table_info(moz_historyvisits)"
0|id|INTEGER|0||1
1|from_visit|INTEGER|0||0
2|place_id|INTEGER|0||0
3|visit_date|INTEGER|0||0
4|visit_type|INTEGER|0||0
5|session|INTEGER|0||0

Since the default pipe character in the output could interfere with a command line interpreter's URL highlighting, which makes the selection of URLs less convenient, a custom separator character can be specified using .separator in the ~/.sqliterc file to prevent this. For example, a space character is specified using .separator " ". Create that file if it does not exist.

Reading the data

Tables from the database can be read through the SELECT operation, with table columns as a dot notation after table names. For example, the following command obtains time stamps and URLs.

sqlite3 places.sqlite "SELECT datetime(moz_historyvisits.visit_date/1000000,'unixepoch'), moz_places.url,title FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id" |sort |less -i

An example output line is:

2012-04-15 15:56:56|http://www.youtube.com/watch?v=CiuK01bf9X8|Die Pinguine aus Madagascar - Rico Intel Werbung (Operation Super Rico) - YouTube

With custom space separators:

2012-04-15 15:56:56 http://www.youtube.com/watch?v=CiuK01bf9X8 Die Pinguine aus Madagascar - Rico Intel Werbung (Operation Super Rico) - YouTube

The datetime(moz_historyvisits.visit_date/1000000,'unixepoch') part is responsible for converting the Unix epoch time written in the database into a human-readable date and time stamp such as 2024-04-01 16:57:47.

The |sort pipe makes the entries appear chronologically. This can be desirable for exporting since the order the entries are written in the database is not exactly chronological. Alternatively, ORDER BY visit_date can be used if natively supported by your sqlite instance:

sqlite3 places.sqlite "SELECT datetime(moz_historyvisits.visit_date/1000000,'unixepoch'), moz_places.url,title FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id ORDER BY visit_date" |less -i

After ORDER BY visit_date, sorting order can be specified using ASC (ascending) or DESC (descending).

Finally, the |less pipe allows directly scrolling through the entries in the terminal software through the "less" utility which enables interactive bi-directional scrolling using the arrow keys and pgUp and pgDn and string searching using "/" (slash). The -i parameter makes string searching case-insensitive. Case-sensitivity can also be toggled within the utility by typing -i.

Optionally, the history can be exported by redirecting the output into a text file by using >>filename.txt instead of |less.

This also allows viewing individual time stamps for repeated visits of the same page, since Firefox records a time stamp for each visit, whereas Google Chrome, while recording visit count, overwrites the previous timestamp, meaning only the most recent visit date/time is recorded by Chrome. On Firefox, the former XUL-based extension "Norwell history tools" enabled viewing individual visit times too, but XUL extensions are unsupported on Firefox versions 57 "Quantum" (November 2017) onward, and no known successor to the extension has been released since as of 2022.

Shortcut function

Opening the database file can be facilitated through a shortcut function:

$ mozhist() { sqlite3 "$1" "SELECT datetime(moz_historyvisits.visit_date/1000000,'unixepoch'), moz_places.url,title FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id ORDER BY visit_date" |less -i; }
$ mozhist places.sqlite

To load this function automatically, add it to the ~/.bashrc file.

Raw data

To extract the raw data from the database in human-readable form, run sqlite3 places.sqlite .dump |less. This can be helpful for performing simple title or URL searches (|grep search_string), but not for dates, since they are not stored in a human-readable format in the database.

Repairing a corrupted database

If Firefox happens to rename the places.sqlite file to places.sqlite.corrupt, it means it has detected an error whlist reading the database. To fix it, do the following after choosing the browser's profile folder as working directory:

# rename to make place
mv places.sqlite places.sqlite.old 
mv places.sqlite.corrupt places.sqlite.corrupt.old

# create dump from corrupted data base
sqlite3 places.sqlite.corrupt.old ".dump" >>dump.sql 

# rebuild database from dump
sqlite3 places.sqlite ".read dump.sql"

This is not guaranteed to fix the problem, but it is worth a try.

See also

• Chromium browsing history database, for Google Chromium-based browsers, most popularly Google Chrome.