How to make a custom/modified SQLite database recognised by Firefox?

Developer Community
1

I’m writing a Bash script part of which is dealing with creating a custom variant of permissions.sqlite with a list of websites added to it to make Firefox store the cookies locally. So far, I can successfully create the file which replicates the one used by Firefox but with extra website data. Basically, the script is reading each line from a user-provided plain text file with a list of URLs to be added to the database (moz_perms -> origin), creates a new database and populates the respective tables in it with URLs, timestamps, etc. I’ve paid particular attention to the structure of the file, including tables, value types, etc.

I can open and manipulate the file with SQLite Browser and SQLite Manager with no problem. But when I replace permissions.sqlite in the Firefox user profile with my version, the browser doesn’t recognise it - it shows no data under Settings -> Privacy and Security -> Cookies and Site Data -> Manage Exceptions. And I wonder why.

Also, I noticed that the file sizes are considerably different (mine is several times smaller), and I guess it’s related to tooling differences between Mozilla devs and me than the actual content of the file.

I’ll be grateful if you can enlighten me on the issue. Thank you!

1 Like
2

It could be due to a few reasons. One possibility is that Firefox expects certain metadata or formatting in the file that your script might not be providing. Another potential reason could be differences in how Firefox handles database files compared to SQLite Browser or SQLite Manager.

As for the discrepancy in file sizes, you’re probably right about it being related to differences in how the files are generated. It’s likely not a big deal as long as the essential data is present and in the correct format.

To troubleshoot further, you might want to compare the structure and contents of your modified file with a genuine permissions. SQLite file from Firefox to see if there are any noticeable differences. You could also try testing your script with a smaller set of URLs to see if that makes any difference in how Firefox interprets the file.

Keep tinkering with it, and hopefully, you’ll figure out what’s causing the issue. Good luck! :slightly_smiling_face:

1 Like
3

James, thank you for your prompt reply and encouragement! :+1: :+1: :+1:

Well, discovering and learning something new, especially for a fresher, is as challenging as a real PITA when the captain has a clear destination but doesn’t know how to steer with all the necessary tools at hand. For now…

Anyways, this is what I got today after checking both files (mine and Firefox’s) with the file command:

permissions-custom.sqlite:  SQLite 3.x database, last written using SQLite version 3037002, file counter 3, database pages 4, cookie 0x2, schema 4, UTF-8, version-valid-for 3
permissions-firefox.sqlite: SQLite 3.x database, user version 12, last written using SQLite version 3043002, page size 32768, file counter 772, database pages 3, cookie 0x3, schema 4, UTF-8, version-valid-for 772

Then, after some research, I amended the code with this to mitigate the difference:

c.execute("PRAGMA user_version = 12;")
c.execute("PRAGMA page_size = 32768;")

… and got this in return:

permissions-custom.sqlite:  SQLite 3.x database, user version 12, last written using SQLite version 3037002, file counter 4, database pages 4, cookie 0x2, schema 4, UTF-8, version-valid-for 4
permissions-firefox.sqlite: SQLite 3.x database, user version 12, last written using SQLite version 3043002, page size 32768, file counter 772, database pages 3, cookie 0x3, schema 4, UTF-8, version-valid-for 772

… still to no avail yet! But you can also notice the slight difference (and convergence) between the files. Interestingly, page size is not reported as changed after a code refresh.

EDIT 1: I also found this piece of code, if it can be of any help in my case. Presumably, the function also inserts data into permissions.sqlite but with no mention of the SQLite engine behind (stmt6Insert?)…

EDIT 2: After some more research, I came up with this to ‘normalise’ the page_size variable:

# Required by Firefox
c.execute("PRAGMA user_version = 12;")
c.execute("PRAGMA page_size = 32768;")
c.execute("VACUUM;") # the added line after the first edit

… and it works in Firefox finally. :slight_smile: I think I’ll stick with the code for now unless somebody has more to comment on or add to it. Feel free to ask for the code, if interested.