Josh
March 23, 2019, 1:01am
1
For anyone who wants to practice SQL, Firefox stores a lot of info in sqlite. You can cd into your Firefox profile directory and then load any of the databases in sqlite:
content-prefs.sqlite
cookies.sqlite
favicons.sqlite
formhistory.sqlite
permissions.sqlite
places.sqlite
storage.sqlite
storage-sync.sqlite
webappsstore.sqlite
Example, the browsing history is stored in places.sqlite:
$ sqlite3 places.sqlite
Or for cookies:
$ sqlite3 cookies.sqlite
sqlite> SELECT * FROM moz_cookies LIMIT 10;
See also, this sqlite cheatsheet and the post below.
I learned some quick, useful tips for working with sqlite that people might be interested in.
Here’s a quick snippet on how to export query results to CSV format:
sqlite> .mode csv
sqlite> .output my_data.csv
sqlite> SELECT * FROM my_table;
sqlite> .output stdout
You can also change the separator with this command:
sqlite> .separator ", "
There are more details on section 5 of this page .
Basically, there are 8 output formats:
list – this is the default, pipe separated output
csv – comm…
Josh
March 23, 2019, 10:00pm
2
Here’s an idea for a sqlite/SQL learning project that would be fun:
Write a script to count how many times you search Google (or other search engine) per day and create a report.
The browsing history is stored in a variety of tables. I’m not sure what’s in all of them, but it looked interesting from a quick glance.
moz_anno_attributes
moz_historyvisits
moz_keywords
moz_annos
moz_host
moz_meta
moz_bookmarks
moz_inputhistory
moz_origins
moz_bookmarks_deleted
moz_items_annos
moz_places
