In our limited testing so far (on MacOS and Linux) it seems to be working fine.
We’re not exactly sure how to cross compile it all for Win32 yet, but we’ll figure it out in the next month or so. Each release of DB Browser for SQLite (every 6-8 weeks) gets ~200k downoads, and about 80% of that is Windows. So, we’ll need to figure it out.
Hopefully this all helps spread the usage of SQLCipher a bunch.
Hi Justin, this may sound a little slow on my part, but would you mind telling us how to enable the SQLCipher support in order to help you test the new feature? When I open an existing database I don’t see any options to specify a key or other SQLCipher settings used for opening the DB, nor do I see the options when I go to create a new database and wish to enable encryption. But they’re in the screenshots on your Github issue tracker, so I’m sure they’re there and I’m just looking in the wrong place.
Are you using the MacOS .dmg file, or are you using a different OS?
If you’re compiling on a different OS (eg Linux or FreeBSD), you’ll need to compile with:
$ qmake CONFIG+=sqlcipher
$ make
Instead of:
$ qmake
$ make
We need to update the instructions with that now I think about it… Oops.
With the MacOS .dmg file, there should be an option in the file menu to “Set Encryption”. That’s used to set the password and page size to encrypt the database.
When opening an encrypted database, the dialog asking for the decryption password and page size should (in theory) automatically pop up.
If something in the above isn’t doing what it’s supposed to, we’ll need to fix it.
Hi @justinclift, thanks for your work to integrate SQLCipher!
I just tried out the Mac version, it looks great. I noticed though that it seems to only inconsistently open databases I create with the command line tool. It seems like the first few tries fail, but after that, it ends up opening the database. I can’t seem to narrow down any specific criteria. Have you noticed this before? If you’d like, I can send over a test database.
Also, with regard to the page size, is there a reason that 4096 is set as the default? SQLCipher uses 1024 as the default page size, and the vast majority of databases are created using that page size.
Ahhh, I haven’t noticed that inconsistency before. If you could either email that test database to me (justin@postgresql.org), or chuck it somewhere online for download, that would be awesome.
With the 4096 default, I’m not sure (yet), as I didn’t write the code for our SQLCipher support. I’ll point Martin at this thread and either cut-n-paste responses back (or he can join and reply directly).
@sjlombardo Martin’s response re the 4096 default:
The reason for the default page size being 4096 is that this seems to be the
default value used by the sqlcipher Debian package which I’ve used for
testing. Maybe they’ve changed the default value for whatever reason… But if
1024 is the ‘official’ default value I’ll change it to that soon.
Is 1024 definitely the ‘official’ SQLCipher default? If so, Martin sounds happy to change it.
With the inconsistency in opening databases, the test database you’re using would really help.
I just took a peek at SQLCipher 3.2.0 on Debian installed via apt-get
and the default cipher_page_size value is 1024. It would be beneficial
to keep with the default value of 1024, allowing for easy portability
for users. Thanks for getting back to us so quickly!
Hi guys! My name is Martin and I’m one of the developers of DB Browser for SQLite. Thanks everybody for your kind words and especially @sjlombardo for his feedback.
So I think I’ve found this inconsistency in opening an encrypted file you were talking about. I could reproduce this by creating a file using the sqlcipher command line tool like this:
$ sqlcipher test.db
So the page size is left at its default value - which indeed is 1024
Now trying to open this database wouldn’t work if I use the correct password AND page size but only if I first use the correct password but incorrect page size and only then the correct password and correct page size.
I’ve spent a bit of time debugging this but couldn’t find a reason for this behaviour, so what I did was adding exactly that workaround to the program (see cipher: Fix opening SQLCipher DBs using page size 1024 · sqlitebrowser/sqlitebrowser@a455215 · GitHub) but I’ve no idea how this fixes it even though it does
But maybe some of you can help me out here
Taking a quick look at your commit, I believe the issue is two fold.
First, you will need to key the database first, immediately following
that you will want to perform the PRAGMA cipher_page_size. The
reasoning is because the cipher context within SQLCipher is not created
until the database has been keyed. Secondly, you will want to adjust
the cipher_page_size to 1024 by default. Could you try adjusting that
and let us know your results? Thanks!
Hi Martin, thanks so much for working on this, and looking into the issue I reported!
I was taking a look at the code here https://github.com/sqlitebrowser/sqlitebrowser/blob/master/src/sqlitedb.cpp#L75 and I think the problem is that, after the execution of the select from sqlite_master, the database handle is an inconsistent state (i.e. it tried to open the database and failed). Flipping around the page sizes is resetting things internally, allowing it to open, but there could be other lurking inconsistencies.
I don’t have a working build environment for the DB Browser for SQLite, but I would suggest an alternate approach for this:
First, try to open database, and select from sqlite master. If it fails, and encryption is enabled:
prompt for the password
sqlite3_close the database handle
call sqlite3_open to open it cleanly
call sqlite3_key with the password
if the page size entered into the prompt is not 1024, call sqlite3_exec with pragma page_size
select from sqlite master to verify that the database is readable
By closing the database and then doing a clean open/key/select sequence everything should be kept in a consistent state. Let me know what you think. Thanks!
Thank you both for helping me out here! It’s making a lot more sense now I’ve just pushed a commit which fixes this properly so that sqlite3_key is always called as the first operation on the database handle and it works like a charm.
Also I’ve set the default cipher page size to 1024 a few days ago. I think the other issue probably made me think it wasn’t 1024 on my system… It’s all kind of obvious in hindsight now I hope we can provide a fresh MacOS X and maybe even a Windows build with the new fix soon!
Looks like this is in a decent state now, so just now updated the OSX nightly build script to also generate binaries with SQLCipher support. Today’s builds:
Pardon my ignorance here. I am not sure which version I am supposed to compile using qmake. Is it the DMG one or the source one?
I have just downloaded version 3.5.1 (source code) and compiled it alright using:
cmake.
and
make
as per usual instruction here
At no point have I ever used qmake.
I can open databases but I do not get prompted for a password when I open an encrypted one.
I am using Linux Mint 17 with SQL Cipher installed.
Where did I go wrong?
Thumbs up Justin!
I re-compiled it following the instruction and it works great!
Obviously, I didn’t scroll down enough to spot the SQLCipher paragraph
Keep up the good work: you have a great piece of software here!
