Firefox and places.sqlite tricks

Sometimes you need to remotely get your bookmarks, history or check the top-10 sites, etc. Here’s some neat tricks to get the info from the Firefox Places file called places.sqlite, which is an SQLite database with the following tables (see also the picture):

$ sqlite3 places.sqlite
SQLite version 3.6.17
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite>.tables
moz_anno_attributes moz_favicons moz_keywords
moz_annos moz_historyvisits moz_places
moz_bookmarks moz_inputhistory
moz_bookmarks_roots moz_items_annos

To get all the saved history in history.out:

sqlite> .output history.out
sqlite> SELECT datetime(moz_historyvisits.visit_date/1000000,’unixepoch’), moz_places.url
…> FROM moz_places, moz_historyvisits
…> WHERE moz_places.id = moz_historyvisits.place_id
…> ;
sqlite> .quit

Or using one line:

$ sqlite3 places.sqlite “SELECT datetime(moz_historyvisits.visit_date/1000000,’unixepoch’), moz_places.url FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id”

Top-20 URL’s:

$ sqlite3 places.sqlite “SELECT moz_places.visit_count, moz_places.url FROM moz_places ORDER by visit_count DESC LIMIT 20;”

All URL’s (and dates) containing the word “solaris”:

$ sqlite3 places.sqlite ‘SELECT datetime(moz_historyvisits.visit_date/1000000,”unixepoch”), moz_places.url FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id AND moz_places.url LIKE “%solaris%” ;’

All searches:

$ sqlite3 places.sqlite ‘SELECT datetime(moz_historyvisits.visit_date/1000000,”unixepoch”), moz_places.url FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id AND (url LIKE “%search?q=%” OR url LIKE “%search?p=%” OR url LIKE “%results.aspx?q=%” OR url LIKE “%web?q=%”) ORDER by visit_date;’

Sources:

One thought on “Firefox and places.sqlite tricks”

Leave a Reply

Your email address will not be published. Required fields are marked *