Install Sqlite And Query Firefox Log Files Using SQL


Firefox saves a lot of log information in RDBMS files that can be queried in sqlite, using standard SQL commands.

SQLite3

SQLite3

 

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

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: