SQL Queries

The following is a number of SQL queries that can be used to query the SQLite databases associated with a user profile within Mozilla Firefox 3. These queries can be used via the Firefox add-on SQLite Manager or by the SQLite command line tool. Each of these SQL statements can be easily modified to suit your needs.

places.sqlite

Show URL and PRtime of all completed downloads

SELECT url, visit_date
FROM moz_places, moz_historyvisits
WHERE moz_places.id = moz_historyvisits.place_id AND visit_type = "7"
ORDER by visit_date


Show URL and PRtime of all URLs which contain the words 'rape', 'lolita' or 'porn'.

SELECT url, visit_date
FROM moz_places, moz_historyvisits
WHERE moz_places.id = moz_historyvisits.place_id AND (url LIKE "%rape%" OR url LIKE "%lolita%" OR url LIKE "%porn%")
ORDER by visit_date


Show the top 20 most visited hosts and the total number of visits they have had. Please note the hosts returned will be displayed in rev_host format, that is they will be reversed with a trailing period i.e. moc.koobecaf.www. for facebook.com

SELECT rev_host AS "Host Name - Reversed", sum(visit_count) AS "Total Visit Count"
FROM moz_places
WHERE rev_host <> "." AND rev_host <> ""
group by rev_host
ORDER by sum(visit_count) DESC
LIMIT 20


Show the top 20 most visited URLs and their visit count.

FROM moz_places
ORDER by visit_count DESC
LIMIT 20


Show the URLs and PRtime of all searches performed on Google, Yahoo, Ask and MSN Live.

SELECT url, visit_date
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


Show the URLs and PRtime of all typed URLs. See moz_places for definitions.

SELECT url, visit_date
FROM moz_places, moz_historyvisits
WHERE moz_places.id = moz_historyvisits.place_id AND typed = "1"
ORDER by visit_datee