[Botan-devel] SQLite3 encryption codec with Botan

Mr Diggilin mr.diggilin at gmail.com
Sat Apr 11 02:39:38 EDT 2009

On Fri, 2009-04-10 at 23:21 -0400, Jack Lloyd wrote:
> On Sat, Apr 11, 2009 at 10:14:48AM +0800, Mr Diggilin wrote:
> > SQLite gives the passphrase for encryption, which I intend to use with a
> > PBKDF to create the key.
> Are you using the same API changes as described in
> http://www.sqlite-crypt.com/documentation.htm or is there some other
> convention for this? (I really don't know anything about the topic but
> this showed up in a quick google search).

I'm sorry, this does warrant a bit of explanation as the details of what
I'm using I haven't found using google either...
SQLite3 has hooks for encryption that can be implemented through what it
calls "codecs". Simply put that means just filling in some functions
that SQLite will call and modifying a function in sqlite to "register"
the codec. So, most of the work is done for me in SQLite3, I just need
to take the data it gives me and return encrypted data.
I haven't found any documentation for it, but am basing my knowledge off
of the work of the creator of wxSQLite3, who in turn based his
implementation off of System.Data.SQLite.
As far as I can tell, SQLite will first call your function to provide a
passphrase from which you create a key. Then it'll call another function
to request encryption or decryption of a given database page. It is also
my understanding that the key I create will be for the whole database,
and in any case I have no information on the row or table at all. All I
have is the given page number and page data for each encipher/decipher

As for Sqlite-Crypt, I believe they are also using the hooks, but I
think they go a bit beyond that (in such a manner as to make it
incompatible with wxSQLite3 and probably any other library that expects
only the built-in SQLite encryption hooks).

> > The only problem then, is that I need a salt. I cannot generate a
> > salt randomly because there's no place to store (and from thence
> > restore) it.
> > It seems like my only option is to hard-code the salt in the code, but
> > I'm not sure exactly what the security implications are when doing so.
> > 
> > Questions:
> > Is hard-coding the salt a bad idea?
> It's not ideal, and could cause real problems based on your threat
> model, but that depends on what your threat model is. Here are the
> implications of using PBKDF2 with a fixed salt, that I can think of:
> Dictionary attacks on this would be more difficult that, say, a plain
> hash, or an unsalted iterated hash, since the random but fixed salt
> means such an attack must attack your system in particular. However
> because the salt is shared, someone who was interested in attacking
> your system could perform a password search against all databases in
> parallel. The advantage of a random salt, especially with an iterated
> hash scheme like PBKDF2, is that if someone wants to check if "abc" is
> the password, they have to compute the hash of "abc" using each of the
> salts in question, while with a static salt they can just do it once,
> and know that, if the password was "abc", that the key will be the
> same across all databases. It also means that someone can precompute a
> huge list of password->key pairs, order them by some metric of
> how-likely-is-this-password, and then once they get access to the
> database to immediately start cracking it by starting with the most
> likely key and going down the list. This is the same procedure one
> would try with a random salt, but with one difference: the cracking
> process (including the (intentionally) slow hash iterations) cannot
> begin until access to the database has been achieved.
> Using the same passphrase twice (either someone reusing a password, or
> just collision by chance) results in the same key being used.
> Depending on the details of your scheme, using the same key might mean
> that identical content would encrypt the identical ciphertext. It also
> might mean that someone could copy and paste bits from one encrypted
> database into another (that used the same password/key), even if they
> didn't know the keys used. (This copy+paste attack is not relevant in
> many use cases but at the same time I can imagine use cases where this
> would completely break the security of the overall system - for
> instance if you can move a row containing an account identifier from
> the loans database to the account holder database...). Both of these
> dangers could be avoided with careful design, I think (using a
> randomized cipher mode, which is going to be a good idea anyway, and
> by somehow tying the ciphertext to its location (maybe by including
> the table name and rowid in the IV? I'm not sure how easily accesible
> this stuff is in sqlite).

All very useful input, thank you. Unfortunately, as I mentioned above
this is page-level encryption, and the rows and tables don't exist at
this level.
I intended to use the page number as the IV for the encryption operation
however, does that sound good?

> > Are there other options I'm not thinking of?
> This seems too obvious, but: why not just create a new table with a
> single column/row containing the salt? (Or a procedure that returns
> it).

Of course, there's the page thing again, but also I'm trying to make
this completely reusable as a drop-in SQLite3 codec, so I can't make any
assumption based on my use case.
Also, it seems to me at this point that what I'm looking for are best
practices to mitigate any threats caused by hard-coded salt, because as
far as I know, it can simply be said that I have no write access to any
permanent location. With one exception...
The only potential for storage I can think of is the db page itself.
That is, when I encrypt the page I can also prepend or append data. The
reason this won't work for the key is that the key is used to encipher
all the pages in the database, and I don't have access to any of the
pages at key creation time. This could be used if I wanted to do
something other than use the page number for the IV, however.

> I'm not sure exactly how you intend for this to work so I'm not sure
> this suggestion makes sense, but another alternative that came to mind
> is use multiple keys, one per table, with the table name used as the
> salt. This is not as good as a randomized per-database salt, as if the
> same passphrase is used and the databases in question share a table
> name (not uncommon), you still have to deal with key collisions.
> > PS. Jack, would you be interested in hosting an SQlite3 encryption codec
> > implemented using Botan? If I do go this route I'd be happy to provide
> > what I come up with.
> I'm not sure if it would make sense to include in the main source tree
> (especially as this bumps up directly against configure.pl's distinct
> lack of ability to test the environment) but certainly it could be
> included in the distribution as an add-on.

Yes, an add-on of some sort was what I had in mind here. You have to
recompile SQLite3 with your codecs to add them, so it's not something
that could just be installed anyway.

The only cross-platform implementation of this other than the wxSQLite3
one costs 2000 USD to license
The one by the maker of wxSQLite3 one seems good, but is 128 bit AES,
advertised only in the Readme of wxSQLite3 (miracle I found it), and
would take a lot of work to get working with the SQLite amalgamation

This implementation should be more flexible than either, as it has the
full power of botan behind it.
I intend to make it something you can append to the SQLite amalgamation
distribution (which seems to be good practice among SQLite3 codecs), and
then to be able choose your algorithm simply by changing the const
strings that are passed to Botan's get_cipher and get_s2k.
The reason I ask if you'd be interested is because I don't have any
channel for distribution, and besides, if done right it could become a
valuable feature for Botan.

> -Jack
> _______________________________________________
> botan-devel mailing list
> botan-devel at randombit.net
> http://lists.randombit.net/mailman/listinfo/botan-devel

More information about the botan-devel mailing list