Firefox saves a lot of log information in RDBMS files that can be queried in sqlite, using standard SQL commands.
To query the files, you have to have the right version of sqlite. Turns out that an old version was installed with my Redhat Linux 6.4 installation and I had to upgrade it in order to see the data. Here is how you can install the software and query the logs.
When I ran sample sqlite queries I found on the web, I got lots of errors:
sqlite3 ./cookies.sqlite 'select count() from moz_cookies' Error: file is encrypted or is not a database
attach “cookies.sqlite” as db1;
Error: file is encrypted or is not a database
sqlite3 cert8.db
SQLite version 3.6.20
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
.databases
Error: file is encrypted or is not a database
.tables
Error: file is encrypted or is not a database
.open cookies.sqlite
Error: unknown command or invalid arguments: “open”. Enter “.help” for help
In this version, there was not even the command, .open!
——
which sqlite3 /usr/bin/sqlite3 /usr/bin/sqlite3 --version 3.6.20 rpm -qa | grep -i "sqlite" | sort perl-DBD-SQLite-1.27-3.el6.x86_64 qt-sqlite-4.6.2-26.el6_4.x86_64 sqlite-3.6.20-1.el6.x86_64 sqlite-devel-3.6.20-1.el6.x86_64 sqlite> .version SQLite 3.8.6 2014-08-15 11:46:33 9491ba7d738528f168657adb43a198238abde19e
——
The problems were solved by installing the latest version of sqlite.
Download the latest sqlite from:
https://www.sqlite.org/download.html
mkdir /sqlite - set the ownership and permissions cd /sqlite - copy the .gz file over to this directory. gunzip sqlite-autoconf-3080600.tar.gz tar xvf sqlite-autoconf-3080600.tar cd sqlite-autoconf-3080600 pwd /sqlite/sqlite-autoconf-3080600 ./configure make as root: make install
——
Turns out, that installing this latest version of sqlite, does not upgrade the package at all. It just unzips the software into a directory. The packages were the same as before.
So, the same software, between versions, was installed in two completely different ways! Thus the inspiration for my last post.
—–
To see the Firefox log files:
cd /home/oracle/.mozilla/firefox/uxmtgwbm.default ls -1 *.sqlite addons.sqlite chromeappsstore.sqlite content-prefs.sqlite cookies.sqlite downloads.sqlite extensions.sqlite formhistory.sqlite permissions.sqlite places.sqlite search.sqlite signons.sqlite urlclassifier3.sqlite webappsstore.sqlite
—–
You can then use sqlite like this:
cd /home/oracle/.mozilla/firefox/uxmtgwbm.default
/sqlite/sqlite-autoconf-3080600/sqlite3
sqlite> .open cookies.sqlite
sqlite> .open cookies.sqlite
sqlite> .databases
seq name file
— ————— ———————————————————-
0 main /sqlite/sqlite-autoconf-3080600/cookies.sqlite
sqlite> .tables
moz_cookies
sqlite> select * from moz_cookies order by 2;
….
274090|mysql.com|__atuvc|1%7C51|www.mysql.com|/|1450365581|1406604582596339|1387293581733266|0|0
294594|mysql.com|__atuvc|48%7C51%2C5%7C52%2C1%7C1%2C2%7C2%2C2%7C3|dev.mysql.com|/|1452658476|1406604581568059|1383843257679650|0|0
354387|mysql.com|s_nr|1406085888989|.mysql.com|/|1408677888|1406604581568059|1406085885990362|0|0
357158|mysql.com|gpName|mysql%3Adev%3A/doc/refman/5.0/en/innodb-parameters.html|.mysql.com|/|1406606386|1406604586361551|1406604582771146|0|0
357159|mysql.com|gpChannel|mysql%3Adoc%3Arefman%3A5.0%3Aen|.mysql.com|/|1406606386|1406604586361899|1406604582771495|0|0
357160|mysql.com|gpServer|dev.mysql.com|.mysql.com|/|1406606386|1406604586362193|1406604582771779|0|0
…
Nothing like using SQL for queries! Cool!
——
You could uninstall the old sqlite, and make a link for the new version. However, this older version of sqlite is from Oracle.
rpm -qa –info sqlite
Name : sqlite Relocations: (not relocatable)
Version : 3.6.20 Vendor: Oracle America
Release : 1.el6 Build Date: Fri 30 Apr 2010 03:38:45 AM CDT
Install Date: Mon 03 Jun 2013 07:42:04 PM CDT Build Host: ca-build61.us.oracle.com
Group : Applications/Databases Source RPM: sqlite-3.6.20-1.el6.src.rpm
Size : 641180 License: Public Domain
Signature : RSA/8, Mon 03 Jan 2011 02:19:31 PM CST, Key ID 72f97b74ec551f03
URL : http://www.sqlite.org/
Summary : Library that implements an embeddable SQL database engine
Description :
SQLite is a C library that implements an SQL database engine. A large
subset of SQL92 is supported. A complete database is stored in a
single disk file. The API is designed for convenience and ease of use.
Applications that link against SQLite can enjoy the power and
flexibility of an SQL database without the administrative hassles of
supporting a separate database server. Version 2 and version 3 binaries
are named to permit each to be installed on a single host
And, it keeps track of files involving yum.
…
/var/cache/yum/x86_64/6Server/ol6_UEK_latest/primary.xml.gz.sqlite
/var/cache/yum/x86_64/6Server/ol6_UEK_latest/other.xml.gz.sqlite
…
Uninstalling the old version, and making a link “should” work. But if you look at my posts on how much time I spent overcoming Oracle Applications errors and links, I can’t help thinking something might break.
—–
which sqlite3
/usr/local/bin/sqlite3
which sqlite
/usr/bin/which: no sqlite in (/usr/lib64/qt-3.3/bin:
It’s easier to just make a link to the new sqlite3 and call it sqlite
as root:
cd /usr/local/bin
ln -s /sqlite/sqlite-autoconf-3080600/sqlite3 sqlite
ls -lrt
total 172
-rwxr-xr-x. 1 oracle root 2415 Jun 14 2013 dbhome
-rwxr-xr-x. 1 oracle root 5036 Jun 14 2013 oraenv
-rwxr-xr-x. 1 oracle root 4143 Jun 14 2013 coraenv
-rwxr-xr-x. 1 root root 153531 Sep 9 10:11 sqlite3
lrwxrwxrwx. 1 root root 39 Sep 10 11:25 sqlite -> /sqlite/sqlite-autoconf-3080600/sqlite3
sqlite –version
3.8.6 2014-08-15 11:46:33 9491ba7d738528f168657adb43a198238abde19e
——
Happy Queries!
——
Useful Links:
Download sqlite:
https://www.sqlite.org/download.html
Install sqlite:
http://www.thegeekstuff.com/2011/07/install-sqlite3/
http://www.linuxfromscratch.org/blfs/view/svn/server/sqlite.html
http://www.tutorialspoint.com/sqlite/sqlite_installation.htm
Using SQLITE:
http://www.sqlite.org/cli.html
http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/sqlite.html
http://linuxgazette.net/109/chirico1.html
http://stackoverflow.com/questions/9057787/opening-database-file-from-within-sqlite-command-line-shell