[Botan-devel] SQLite3 encryption codec with Botan

Jack Lloyd lloyd at randombit.net
Fri Apr 10 23:21:33 EDT 2009

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

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

> 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

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.


More information about the botan-devel mailing list