Read Firefox sqlite files with Python

[Steve D'Aprano]

On Mon, 6 Nov 2017 12:39 am, Paul Moore wrote:

> On 5 November 2017 at 01:22, Steve D'Aprano <steve+python at pearwood.info>
> wrote:
>> On Sun, 5 Nov 2017 04:32 am, Steve D'Aprano wrote:
>>
>>> I'm trying to dump a Firefox IndexDB sqlite file to text using Python 3.5.
>>>
>>>
>>> import sqlite3
>>> con = sqlite3.connect('foo.sqlite')
>>> with open('dump.sql', 'w') as f:
>>>     for line in con.iterdump():
>>>         f.write(line + '\n')
>>
>>
>> Never mind. I dumped the file using the sqlite3 command line tool. Thank
>> you to all those who answered.
>>
>> The file contains three INSERT statements, the first two don't have
>> anything of interest, and the third (which presumably contains all the data
>> I'm trying to recover) is an opaque 600+ KB blob.
>>
>> Naturally. Why would you use a database as a database, when instead you
>> could just dump a big ball of mud into it?
> 
> Hmm, *.sql files normally contain SQL source code (as this one does).

The .sql file is the result of running .dump from the sqlite command line
tool. The original source database is 'foo.sqlite'. To be precise, it is the
database used by the Firefox Add-On "One Tab".

/home/steve/.mozilla/firefox/2z5po7dx.default/storage/permanent/indexeddb+++extension-at-one-tab-dot-com/idb/1832832054obnaet.sqlite

One Tab provides an alternative bookmark-like function, allowing you to record
URLs in groups for later use -- a bit like bookmarks. So I've been using this
for some months, until the add-on stopped working. (Yet again an automatic
update has screwed me and broken functionality.) So now I'm trying to
retrieve the bookmarks.

The database itself (the .sqlite file) is not corrupt. The sqlite CLI
processes it fine, and gives me a dump file containing a valid looking
transaction:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE file (id INTEGER PRIMARY KEY, refcount INTEGER NOT NULL);
CREATE TABLE "database" ( name TEXT PRIMARY KEY, origin TEXT NOT NULL, version
INTEGER NOT NULL DEFAULT 0, last_vacuum_time INTEGER NOT NULL DEFAULT 0,
last_analyze_time INTEGER NOT NULL DEFAULT 0, last_vacuum_size INTEGER NOT
NULL DEFAULT 0) WITHOUT ROWID;
...
COMMIT;


But the *interesting* part, the actual data which I hoped would be something
useful like a table of URLs, is a binary blob:

CREATE TABLE "object_data"( object_store_id INTEGER NOT NULL, key BLOB NOT
NULL, index_data_values BLOB DEFAULT NULL, file_ids TEXT, data BLOB NOT NULL,
PRIMARY KEY (object_store_id, key), FOREIGN KEY (object_store_id) REFERENCES
object_store(id) ) WITHOUT ROWID;

which then has three insertions, all of which look something like:

INSERT INTO "object_data" VALUES(1,X'307475627566',NULL,NULL,X'B8F32BA8...');

where the last argument is anything up to 11000+ hex digits. So it looks like
it might be just dumping its in-memory data structure into the database as a
blob.



-- 
Steve
“Cheer up,” they said, “things could be worse.” So I cheered up, and sure
enough, things got worse.