SQLCipher Support added to 'DB Browser for SQLite'


#1

Hi all,

The DB Browser for SQLite project is adding support for SQLCipher in our next release.

(we were previously known as “SQLite Database Browser” which is probably more recognised :>)

An initial build for MacOS X (10.6-10.10) that includes SQLCipher support is here:

    http://mirror.salasaga.org/sqlitebrowser/onceoffs/sqlitebrowser_201411051855.dmg

Is anyone around with time to test it and report back success/failure and anything else relevant?

Our GitHub Issue tracker:

    https://github.com/sqlitebrowser/sqlitebrowser/issues

Regards and best wishes,

Justin Clift


#2

Hi Justin,

Thanks for sharing your project - we are glad to see you are working on
integrating SQLCipher!


#3

You’re welcome. :smiley:

We’re pretty driven by user requests, and this is one of our older ones:

    https://github.com/sqlitebrowser/sqlitebrowser/issues/12

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. :smiley:


#4

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.

Thanks!


#5

No worries at all.

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. :wink:

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. :smiley:


#6

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.


#7

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). :slight_smile:


#8

@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. :smile:

With the inconsistency in opening databases, the test database you’re using would really help. :wink:


#9

Hi Justin,

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!


#10

@developernotes @sjlombardo No worries. :wink:

Try this one instead, it’s updated to use 1024 as the default value, and has some workaround code that Martin says should fix the inconsistency.

    http://mirror.salasaga.org/sqlitebrowser/onceoffs/sqlitebrowser-sqlcipher_201411101930.dmg

Lets see how that goes. :blush:


#11

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

pragma key = ‘test’;
create table test(a int, b int);
.quit

So the page size is left at its default value - which indeed is 1024 :wink:

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 https://github.com/sqlitebrowser/sqlitebrowser/commit/a4552152af73569d57590a4a3f583727b706584c) but I’ve no idea how this fixes it even though it does :confused:
But maybe some of you can help me out here :slight_smile:


#12

Hello Martin,

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!


#13

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:

  1. prompt for the password
  2. sqlite3_close the database handle
  3. call sqlite3_open to open it cleanly
  4. call sqlite3_key with the password
  5. if the page size entered into the prompt is not 1024, call sqlite3_exec with pragma page_size
  6. 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!


#14

Thank you both for helping me out here! It’s making a lot more sense now :slight_smile: 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 :wink: I hope we can provide a fresh MacOS X and maybe even a Windows build with the new fix soon!


#15

Excellent stuff. :smile:

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:

    http://mirror.salasaga.org/sqlitebrowser/nightly/sqlitebrowser-sqlcipher_20141114.dmg
    http://mirror.salasaga.org/sqlitebrowser/nightly/sqlitebrowser_20141114.dmg

Tested the SQLCipher one on an OSX 10.8 desktop, and it opened and browsed an encrypted test database fine. (didn’t really try much else though)

Next, I guess we need to figure out our cross-compile Windows build support. (Windows is still 80% of our download #'s)

Anyone interested in getting SQLCipher into MXE (http://mxe.cc)? :wink:


#16

Oops, forgot to mention it previously… but this is now released and actively being used:

https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.5.1

(actually with v3.5.0 from a week ago)

Hope that helps. :smile:


#17

Congratulations, @justinclift!


#18

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?


#19

Ahhh, it sounds like you’re close. The instructions for compiling with SQLCipher support steps are a bit further down that BUILDING.md page.

I haven’t personally tried them on Linux, so am a bit unsure though. (I do OSX stuff)

If cmake isn’t picking up SQLCipher, then Martin or one of the SQLCipher Community here will hopefully know how to resolve that.

Btw, the .dmg file is an OSX binary, so probably not useful for you. Compiling from the 3.5.1 tarball would be the go. :smile:


#20

Thumbs up Justin! :smile:
I re-compiled it following the instruction and it works great!
Obviously, I didn’t scroll down enough to spot the SQLCipher paragraph :wink:

Keep up the good work: you have a great piece of software here!

Marco