Playing with Firefox’s SQLite bookmarks

As I said in the previous post, I wanted to add SQLite support to HippoHX. So far, so good: the AS3 client API is already on SVN and working. haXe and AS2 client APIs are still to come, and so does the mandatory demo. Doing demos is similar to documenting, a pain in the butt. I never find a nice example and when I think of one, most of the times I don’t go for it because fancy = long time.

However, still wanted to do something nice with the SQLite demo and luckily for me I remembered that since Firefox 3 bookmarks are stored using a local SQLite database. Reading your bookmarks from a Flash app sounds like a nice demo, right? ¬¬

First, you have to find where the data is stored. A quick Google search comes up with the name of the file: places.sqlite. The first problem I found was that that file is stored in your profile folder, but the name of your profile folder is random for security reasons. So I either had to:

1) Find a cross-platform way of finding the folder for the current user.
2) Search for the file in the user folder.

#2 triggered an old idea: adding search capabilities to HippoHX’s API. Back in September there was some discussion about this in haXe’s mailing list but I wasn’t completely happy with Michael Oster’s solution, so I’ve implemented my own search method based on regular expressions (File.search() is on SVN now).

Now that I can find where the file is, it’s time to display its content. First problem: you cannot open the file if Firefox is opened at the same time. Sort of makes sense because at the end of the day places.sqlite is only a file, so whoever is using it needs to lock it down to guarantee consistency. Ok, so if we get an error while accessing it, let’s just make a copy and access the copy instead.

Ok, we are getting somewhere. Next, what’s the structure of the database? You might think that because it’s an Open Source project you are going to find the schema of the database in a quick Google search right? Wrong. I still haven’t been able to find it. Ok, let’s just move on and finish the damn tutorial, shall we? Tell you what, let’s just open the db through the command line and dump it to a text file. Here I come: fire up the console in my Linux box:

  1. sqlite places.sqlite
  2. sqlite> SELECT * FROM moz_bookmarks;
  3. sqlite> SQL error: database is locked

Shit, forgot FF is opened. Let’s close FF, start again and:

  1. Unable to open database "places.sqlite": file is encrypted or is not a database

Say what now? If FF is opened I cannot access it, if FF is closed, the file is encrypted? Oh boy. Googled again until I found this:

The windows version seems to work

Let’s boot up virtual XP, install SQLite, find the file and finally I can access it on the command line! For some odd reason I still don’t know, the file is encrypted on Linux but not on Windows….

Aaaaaaaaaaaaaanyway, want to get some meaningful info out of your bookmarks? Try this:

  1. SELECT moz_bookmarks.title,moz_places.url FROM moz_bookmarks LEFT JOIN moz_places WHERE moz_bookmarks.fk = moz_places.id AND moz_bookmarks.title != "null" AND moz_places.url LIKE "%http%";

So, what have we learnt today? Stick to the crappy tutorials!

March 4th, 2009 at 13:20 | as3, hippohx, open source, sql | Trackback

7 Responses to “Playing with Firefox’s SQLite bookmarks”

  1. anonymous Says:
    March 5th, 2009 at 18:02

    The file is not really encrypted on a Linux installation because it the data can be viewed in cleartext by “cat places.sqlite”.

  2. m00dimus Says:
    March 5th, 2009 at 18:11

    try “sudo sqlite3 places.sqlite” to troublesheet admin permissions and sqlite version issues. – m00dimus

  3. Zarate Says:
    March 5th, 2009 at 18:44

    Hi to both : )

    If the file is not really encrypted, then I understand even less the error.

    And thanks m00dimus, might be a good idea for the *nix demo.

    Thanks!

    J

  4. neal_s Says:
    March 18th, 2009 at 20:13

    http://www.forensicswiki.org/index.php?title=Mozilla_Firefox_3_History_File_Format

    Here you go. :)

  5. anonymous Says:
    March 21st, 2009 at 10:50

    You need to copy the database before opening it, although be aware that the reason it isn’t letting you open it is that some write operation may be incomplete. Using root access get into it is major overkill.

  6. anonymous Says:
    March 21st, 2009 at 11:29

    >> Unable to open database “places.sqlite”: file is encrypted or is not a database

    Try using sqlite3 – using an older version of sqlite on a database from a more recent one can cause that error.

  7. anonymous Says:
    March 21st, 2009 at 11:30

    I’m finding this SQL handy for script input:

    sqlite3 ‘SELECT (SELECT title FROM moz_bookmarks as other WHERE id = moz_bookmarks.parent) AS folder, moz_places.url, moz_bookmarks.title, (SELECT content FROM moz_items_annos WHERE item_id = moz_bookmarks.id) AS note FROM moz_bookmarks, moz_places WHERE moz_bookmarks.fk = moz_places.id ORDER by url;’

Leave a Reply