[bug_fixed] Transparent encryption support for SQLite

[ehoffman]

Hello everybody,

as you may know SQLite supports transparent database file encrpytion. For this to work you need a commercial, but plattform independent SQLite plugin, available here: http://www.hwaci.com/sw/sqlite/prosupport.html#compress

For Windows there is also a free SQLite3 version with encryption, see here: http://sqlite.phxsoftware.com/

This free version is based on SQLite v3.5.4 and has full encryption support, but not plattform independet - it works only on windows. Originally this was written for Microsoft .NET compiler, but it also works with Delphi/C++ Builder and the Zeos components. Simply rename "System.Data.SQLite.dll" to "sqlite3.dll".

In the current version (v6.6.2-rc) Zeos does not implement the functions necessary for encryption support (some of the functions are there, but disabled).

That is why, I created a patch to fully activate encryption support for SQLite in Zeos. Here is what I did. The encryption support needs basicly three functions:

- sqlite_open_encrypted
- sqlite_key (set the encryption password)
- sqlite_rekey (set a new encryption password or re-encrypt a db file with a new password or encrypt an unencrypted db-file)

The first function is only available in SQLite v2.x. SQLite v3.x only knows the second and the third function, so the first is not necessary. So I implemented calls to all three functions in the plain SQLite drivers of Zeos and in IZSQLiteConnection.

If the three functions are not available in sqlite.dll the calls simply do nothing (because the functions pointers are nil/null). So the patch does not break compatibility with SQLite versions without encryption - they can be used as before.

To open an encrypted database file you can now specify the parameter "encrypted=true" in the parameters of ZConnection component. Then when opening / creating the database file a call to sqlite_key will be done. As the key the Password set in ZConnection component will be used.

Later on you can use

(ZConnection1.DbcConnection as IZConnection).GetPlainDriver() and
(ZConnection1.DbcConnection as IZConnection).GetConnectionHandle()

to access the Key and ReKey functions of IZSQLiteConnection.

So, what happens when you use this:

1. If your sqlite3.dll has no encrpytion support and you try to open ...

a) ... a unencrypted db file, with "encrypted=true": Works as usual, because the call to "Key" in Open is ignored. The db file is opened unencrypted.

b) ... a unencrypted db file, with "encrypted=false": Works as usual. The db file is simply opened unencrypted.

c) .... a encrypted db file, with "encrypted=true": You get an error, because the file can't be opened (thats the way it should be ).

d) ... a encrypted db file, with "encrypted=false": You get an error, because the file can't be opened (thats the way it should be ).


2. If your sqlite3.dll has encryption support and you try to open ...

a) ... a unencrypted db file, with "encrypted=true": You get an error, because when opening the file the "Open"-functions calls the "Key" function and sets the encryption key for this file. But because the file is unencrypted it has no encryption key, what means the key you set is wrong (if ZConnection.Password is not empty...). The database file is NOT simply encrypted (would be very dangerous if you do this by accident).

If you want to encrypt an existing db file you need to open it with "encrypted=false" then call IZSQLiteConnection.ReKey to set a password (now the file is encrypted). When you then open the db file the next time you need to set the correct password and "encrypted=true".

b) ... a unencrypted db file, with "encrypted=false": Works as usual. The db file is simply opened unencrypted.

c) ... a encrypted db file, with "encrypted=true": If your key in ZConnection.Password is correct, the encrypted db file is opened as usual and works as usual. If the key is wrong - guess - you get an error.

You can also Open the file with "encrypted=false", but then you need to set the correct key with IZSQLiteConnection.Key BEFORE you try to access any data in the database file.

If you want to set a new key for an encrypted db file, do it this way:
- open the file with the correct key, so "encrypted=true" or call Key-function.
- Call IZSQLiteConnection.ReKey to set the new key (be aware: the complete file is re-encrypted, that needs some time if the file is large). The next time you open the file use the new key...

d) ... a encrypted db file, with "encrypted=false": If you call "IZSQLiteConnection.Key" with the correct key BEFORE you access any data in the db file it will work fine, otherwise you simply get an error (that's the way it should be ).

Best regards, Eike

P.S. The patch is created with WinMerge

[mdaems]

Never seen a so well documented patch as long as I can remember.
(But my memory is weak )

Thanks a lot !
Keep an eye on me. If I forget to patch this within 2 weeks, please send me a pm to remind me.

I didn't fully check and try to intgrate the code yet, but at first glance it seems very good.

Mark

[ehoffman]

Thank you for your suggestions in the other thread! The documentation is for me, too - so at least it was a kind of self-interest .

And as I know from me daily work: The better the documentation, the better the patch and the faster it gets applied...

Best regards, Eike

[mdaems]

Hint for those who want to apply the patch on windows :

1) Install GNUWin32 (at least the patch utility)
2) Unzip Eike's file in your zeoslib main directory
3) run 'patch -p5 -c -ipatch-SQLite-encryption-zeos-6.6.2-rc.patch' from the command line, starting in your zeoslib main directory

I suppose only step 3 will do it in a linux environment.

Mark

[mdaems]

This patch has been applied to testing branch (SVN rev. 338)

I plan to include it in release 6.6.3 as the patch is easy, clean and does not affect any existing application (unless it's using the property encrypted=true that wasn't supported before, which is very unlikely).

Someone objecting?

Mark

[mdaems]

Hi Eike,

What about adding your tutorial above to the knowledge base? It only needs a little or no cleanup, I think.
I'll add a new category 'SQLite' to the knowledge base as this is very db specific.

The goal of the knowledge base is to get a repository of independent articles, grouped together on topic. Just have a look at the contribution by gto to get an idea...

If you have questions, don't hesitate to pm or mail me.

Mark

[ehoffman]

Hi Mark,

sorry for my late reply, but I got a son on February 27 So I was a little bit busy...

You can do with my "tutorial" whatever you want. Add it to the Knowledge Base if you think it is good enough. I clean it up a little and tell you when it's done.

Best regards, Eike

[ehoffman]

Encryption support in SQLite with Zeos DBC components

ChangeLog

Version 1.0 - 08/08/2008
> First version of this article

Overview

As you may know SQLite supports transparent database file encrpytion. For this to work you need a commercial, but plattform independent SQLite plugin, available here: http://www.hwaci.com/sw/sqlite/prosupport.html#compress

But there are also two free SQLite ports which support encryption:

• For Windows you can use the System.Data.SQLite implementation. This library was written for Microsoft .NET, but also works as a normal Windows-DLL. For that: Simply rename "System.Data.SQLite.dll" to "sqlite3.dll". The encryption used here is not portable to eg. Linux, because it uses the Microsoft Encryption API (but Delphi applications are not portable to Linux, too - so this should not be a problem). You can find the library here: http://sqlite.phxsoftware.com
• The second implementation is wxSQLite3. This library was designed to be used with the wxWidgets library (http://www.wxwidgets.org/), but the plain DLL will also work with Zeos DBC. This library uses generic 128Bit AES encryption functions implemented in C. Sourcecode and compiled DLLs are included in the download which you can find here: http://wxcode.sourceforge.net/components/wxsqlite3/.

How to use encryption with Zeos and SQLite

The encryption support needs basicly three functions:

• sqlite_open_encrypted
• sqlite_key (set the encryption password, eg. when opening/creating a database)
• sqlite_rekey (set a new encryption password or re-encrypt a db file with a new password or encrypt an unencrypted db-file)

The first function is only available in SQLite v2.x. SQLite v3.x only knows the second and the third function, so the first (sqlite_open_encrypted) is not necessary. Calls to both of these functions are implemented into the plain SQLite drivers of Zeos and in IZSQLiteConnection.

The Zeos implementation checks if these functions are available in the sqlite.dll. If the functions are not available any call to them simply does nothing, because the function pointers are nil/null and a SQLITE_OK is returned.

To open an encrypted database file you need to specify the parameter "encrypted=true" in the parameters of ZConnection component. Then when opening / creating the database file a call to sqlite_key will be done. As the encryption key the Password set in ZConnection component will be used (do that before you open/create a database!).

Later on you can use

(ZConnection1.DbcConnection as IZConnection).GetPlainDriver() and
(ZConnection1.DbcConnection as IZConnection).GetConnectionHandle()

to access the Key and ReKey functions of IZSQLiteConnection and give them the proper parameter values.

What you need to know:

1. If your sqlite3.dll has no encrpytion support and you try to open ...

1. a unencrypted db file, with "encrypted=true": Works as usual, because the call to "Key" in Open is ignored. The db file is opened unencrypted.
2. a unencrypted db file, with "encrypted=false": Works as usual. The db file is simply opened unencrypted.
3. a encrypted db file, with "encrypted=true": You get an error, because the file can't be opened (thats the way it should be Smile).
4. a encrypted db file, with "encrypted=false": You get an error, because the file can't be opened (thats the way it should be Smile).

2. If your sqlite3.dll has encryption support and you try to open ...

1. a unencrypted db file, with "encrypted=true": You get an error, because when opening the file the "Open"-functions calls the "Key" function and sets the encryption key for this file. But because the file is unencrypted it has no encryption key, what means the key you set is wrong (if ZConnection.Password is not empty...). The database file is NOT simply encrypted (would be very dangerous if you do this by accident).
   
   If you want to encrypt an existing db file you need to open it with "encrypted=false" then call IZSQLiteConnection.ReKey to set a password (now the file is encrypted). When you then open the db file the next time you need to set the correct password and "encrypted=true".
2. a unencrypted db file, with "encrypted=false": Works as usual. The db file is simply opened unencrypted.
3. a encrypted db file, with "encrypted=true": If your key in ZConnection.Password is correct, the encrypted db file is opened as usual and works as usual. If the key is wrong - guess - you get an error.
   
   You can also Open the file with "encrypted=false", but then you need to set the correct key with IZSQLiteConnection.Key BEFORE you try to access any data in the database file.
   
   If you want to set a new key for an encrypted db file, do it this way:
   • open the file with the correct key, so "encrypted=true" or call Key-function.
   • Call IZSQLiteConnection.ReKey to set the new key (be aware: the complete file is re-encrypted, that needs some time if the file is large). The next time you open the file use the new key...
4. a encrypted db file, with "encrypted=false": If you call "IZSQLiteConnection.Key" with the correct key BEFORE you access any data in the db file it will work fine, otherwise you simply get an error (that's the way it should be ).

[ehoffman]

Hi Mark,

I hope this is ok for the knowledge base. If so please move it there.

Thank you, Eike