Help required with encryption support on Sqlite3

[abhibera]

Hi Stephen

I would have loved to use SQLCipher but I need to use only the
BlowFish encryption for my application. I cannot bundle or include
OpenSSL due to system constraints.

Thus, I have to add my own encryption support to sqlite3.

Initially, I thought I would add the encryption and decryption
routines to the read and write calls of the VFS. But somehow it isn't
a good idea since there are different lengths and offsets.

Now I have hit a roadblock. I'm confused on how to proceed.

Would you be kind as to tell me how you implemented encryption on
SQLCipher?


Regards

Abhijit Bera

[Stephen Lombardo]

Hi Abhijit,

With a little work it would be possible to rewire most of the SQLCipher code to operate with Blowfish. Assuming you have suitable blowfish driver code you could rewrite the various parts of the SQLcipher extension. Off the top of my head, you'd minimally need to address:

1. Changes to cipher_ctx to track your blowfish context. SQLcipher currently uses a libcrypto EVP_CIPHER for this
2. Some method of deriving key data from the passphrase. SQLCipher currently uses openssl's PBKDF2
3. rewiring of codec_cipher to call appropriate blowfish routines
4. A source of good pseudo-random data for file headers and initialization vectors, etc

I can't say that this would be trivial at all, but it would probably be much easier to use SQLCipher as a starting point and re-wire it than to author a new codec implementation from scratch. I'd also recommend against the VFS approach - I don't think it would be any easier and you'd have a much harder time dealing with application initialization, cases like attached database, and you may loose out on some neat possibilities like per-page IVs, etc.

I hope this helps point you in the right direction.

Cheers,
Stephen

[abhibera]

Hi Stephen

Unfortunately, I cannot use your code directly because of a standalone
requirement with no dependencies on any other libraries like
OpenSSL. :(

I was studying your code last night and realised that the encryption
should be part of the pager. I wonder why the SQLITE_HAS_CODEC macro
isn't documented well enough? It sure looks like an interesting entry
point to add encryption or any other form of encoding.

I'm using Paul Kocher's Blowfish implementation with some additional
wrappers for processing buffers. So, let me see how I can create a
similar context as you have done.

Let's see what I come up with?

Thanks a lot once again.

Regards

Abhijit Bera

[abhibera]

Hi Stephen

I have gone through your source code and the original sqlite3 source
code.

Since I'm pretty much a newbie it has taken me some time to understand
it superficially.

This is what I understood so far:

crypto.c is an extension you have created to add the encryption
support. In this you have actually created implementations for the
sqlite3 codec interfaces.

There are interfaces already available on sqlite3 which allow any
developer to add encryption support according to their requirement.
All the interfaces required can be found in pager.c in the sections
where the #ifdef SQLITE_HAS_CODEC blocks appear. The encryption
function calls are already embedded into pager.c through the CODEC1
and CODEC2 macros. One doesn't need to manually scan through the pager
implementation to decide when and where the encryption routines should
be called.

The page struct has the following section:

#ifdef SQLITE_HAS_CODEC
void *(*xCodec)(void*,void*,Pgno,int); /* Routine for en/decoding
data */
void (*xCodecSizeChng)(void*,int,int); /* Notify of page size
changes */
void (*xCodecFree)(void*); /* Destructor for the codec
*/
void *pCodec; /* First argument to xCodec... methods
*/
#endif

Which indicates some of the functions required to be implemeted. For a
more verbose explanation about the interfaces required, one can read
through the sqlite3.h.in source file.

So, basically one needs to have the following functions implemented:

sqlite3_key : This function has to be called after sqlite3_open and
supplied a key with which the database file can be read. This is how
we can encrypt a sqlite3 database in our application.

sqlite3_rekey: this function will re-encrypt the database file with a
new key.

sqlite3Codec: This is an implementation of cCodec. Basically it's a
wrapper around the encryption/decryption routines which get called
depending on the mode.

sqlite3CodecAttach: this gets called in attach.c in the attach_func
when we try to attach a database to an existing connection. This
function also gets called from sqlite3_key.

sqlite3CodecGetKey: this function gets the key from the given db
pointer's page struct. I'm not sure when this is used though.

sqlite3FreeCodec: an implementation of xCodecFree.

Now starts a list of my questions for you:

Why have you not implemented a xCodecSizeChng function?

Why have you implemented a seperate sqlite3pager_sqlite3PagerSetCodec
as a wrapper over sqlite3PagerSetCodec?


I hope you can answer my questions and clear my doubts. I'll keep
looking at the code and hopefully I can implement something soon?

[Stephen Lombardo]

Hi Abhijit,

 

Your assessment and analysis looks correct. Answers to your quesetions are inline below.

 

On Wed, Dec 16, 2009 at 6:07 AM, abhibera <abhi...@gmail.com> wrote:

Why have you not implemented a xCodecSizeChng function?

 

SQLCipher's implementation includes some additional security that complicates page size changes, specifically a per-database random salt in the header, and a random per-block CBC intiailization vector. Because of this, SQLCipher does not support page size modification at this time. Thus the function is unnecessary.

 

Why have you implemented a seperate  sqlite3pager_sqlite3PagerSetCodec
as a wrapper over sqlite3PagerSetCodec?

 

sqlite3PagerSetCodec is static and therfore not visibile outside the scope of pager.c. The sqlite3pager_sqlite3PagerSetCodec wrapper is not static, and can thus be called from crypto.c. This minimizes the changes necessary to Makefiles to support both amalgamated and non-amalgabmated builds, reduces the risk of conflicts on merges from upstream sqlite source, and keeps the bulk of the crypto code spearate from the pager. There are other approaches that would also work, e.g. including pager.c into crypto.c, including pager.c into a separate extended crypto-specific pager implemenation that extends it, making sqlite3PagerSetCodec non-static, etc

 

Cheers,

Stephen

[abhibera]

Hi I'm sort of done with re-wiring crypto.c and crypto.h according to
my requirements. But I'm unable to build sqlitecipher. I'm getting
errors while compiling sqlite3.c. I tried with my modified version and
your version. Both give me build errors on sqlite3.c. Why do you
suppose this happens? I'm developing against the sqlite3 release.

Have sent you a seperate mail with my updated source code. Please take
a look at it when you are free.

Regards

Abhijit Bera

On Dec 17, 4:23 am, Stephen Lombardo <sjlomba...@zetetic.net> wrote:
> Hi Abhijit,
>
> Your assessment and analysis looks correct. Answers to your quesetions are
> inline below.
>

[abhibera]

One more query.

Where exactly is the macro SQLITE_HAS_CODEC defined?

I tried looking everywhere. It's neither defined in the source code
nor in the Makefiles with a -D option. So how are the CODEC code
blocks getting included?

Regards

Abhijit Bera

[abhibera]

I made some changes to the Makefile.

1) I disabled compilation of the amalgamation
2) I included a -DSQLITE_HAS_CODEC in the TCC variable

and it compiled! :)

I installed it under /usr/local/lib so that I could keep my original
sqlite3 libraries intact. I don't want to get rid of them as lot of
other software on my system are dependent on it.

But now one more problem.

I wrote a simple program to test:

#include <stdio.h>
#include <sqlite3.h>
void main() {
sqlite3 *db;
sqlite3_open("mydb.db",&db);

sqlite3_key(db,"Hello1234",9);

}

Compiled it with: gcc hello.c -I/usr/include -L/usr/lib -lsqlite3 -
lpthread -o hello

and got this error:

/tmp/ccW50STn.o: In function `main':
hello.c:(.text+0x3b): undefined reference to `sqlite3_key'
collect2: ld returned 1 exit status

:(

Abhijit Bera

[Stephen Lombardo]

Abhijit,

If you take a look in the SQLCipher readme we recommend setting the CFLAGS environmental variable on the command line to include -DSQLITE_HAS_CODEC instead of modifying the Makefile. Its just one less thing to deal with when merging upstream source changes from SQLite.

Since you didn't remove or replace your system's SQLite library you need to tell gcc where to find your custom version. Set -L /usr/local/lib on the gcc command line. If you want that to work with the dynamic library you'll also need to modify LD_LIBRARY_PATH on linux or DYLD_LIBRARY_PATH on Mac OS X, prepending /usr/local/lib so it is searched first. Your other option is to statically link against libsqlite3.a as generated by SQLCipher.

As an aside, these types of questions and issues aren't really specific to SQLCipher. It might make sense for to spend some time with the GCC docs (http://gcc.gnu.org/onlinedocs/gcc-4.4.2/gcc/) to get a better understanding of your options around compile time defines, linking, etc. That said, I'm very glad that you were able to get your own custom crypto extension working using SQLCipher as a reference.

Cheers,
Stephen

[abhibera]

I get it. Didn't read your build instructions. I thought u already had
the build scripts ready.

I found errors in my code. Going to fix them.

Can I just include crypto_vaccum.c to allow plaintext db rekey
support?

[abhibera]

Hi Stephen

It's done. Will mail it to you soon as a tarball. Please see it and
tell me if it is of any use.

Also I will be hosting it shortly on my website.

Regards

Abhijit Bera