Integration in System.Data.SQLite (.NET)

[MarcelVersteeg]

Can you give a hint on how to use this project in the System.Data.SQLite assemblies/distribution so the ciphers can be used in .NET development?

[utelle]

I suppose you refer to System.Data.SQLite. As far as I know its implementation is based on the SQLite amalgamation source code. So the best approach will certainly be to replace the SQLite amalgamation by the SQLite3 Multiple Ciphers amalgamation (which is made available with each release or which can be created with a script from the original sources). However, additionally it will be most likely necessary to adjust the code in SQLite.Interop/src/generic/interop.c of the System.Data.SQLite distribution. And it will be necessary to adjust the list of compile time symbols. Maybe the postings in wxSQLite3 issue #24 are also useful.

To enable the methods/functions to set the encryption key it will be necessary to define the symbol INTEROP_CODEC. However, this has an unwanted side effect: the code in SQLite.Interop/src/win/crypt.c is included in interop.c - this must be avoided!

Additionally, it might be useful to make the additional API functions fo SQLite3 Multiple Ciphers available (in the same way as the functions sqlite3_key and sqlite3_rekey), but it is not absolutely necessary, because the cipher scheme can be selected and configured via PRAGMA commands or URI parameters.

An alternative might be Microsoft.Data.SQLite. There is for example a blog post by Brice Lambson, Encryption in Microsoft.Data.Sqlite. However, I have no idea whether integrating SQLite3 Multiple Ciphers into Microsoft.Data.SQLite is easier to accomplish.

[shamork]

Integrating SQLite3 Multiple Ciphers into Microsoft.Data.SQLite IS easier to accomplish.
I've make it working, steps in https://github.com/shamork/SQLitePCLRaw.multipleciphers

[utelle]

Integrating SQLite3 Multiple Ciphers into Microsoft.Data.SQLite IS easier to accomplish. I've make it working, steps in https://github.com/shamork/SQLitePCLRaw.multipleciphers

Thanks for providing a description of how to use SQLite Multiple Ciphers with Microsoft.Data.SQLite. For sure, this will be very helpful for developers seeking support for SQLite3 Multiple Ciphers in the .Net world.

[utelle]

Based on the latest officially released System.Data.SQLite code (namely version 1.0.113.0) I checked which modifications might be required to integrate SQLite3 Multiple Ciphers in System.Data.SQLite.

A few minor adjustments to the code of SQLite3 Multiple Ciphers are required to make it compile with the solution files coming with System.Data.SQLite. These adjustments were applied in commit 5013599.

Additionally, the property files as well as the project files of System.Data.SQLite need minor adjustments - adding compile time symbols in the property files, removing several source files which cause conflicts from the project files.

As already mentioned mainly the source file SQLite.Interop/src/generic/interop.c has to be adjusted (removing #include statements for SQLite extensions which are already included in the SQLite3 Multiple Ciphers amalgamation in the first place).

[MarcelVersteeg]

Thank you for the information (and the changes to your code).

I will look into it and see if I can get it to compile. I might consider creating a project out of it on GitHub, as others might also be interested in it.

[utelle]

Looking at the System.Data.SQLite timeline I get the impression that the release of System.Data.SQLite version 1.0.114.0 will happen in the near future. Most likely integration of SQLite3 Multiple Ciphers into System.Data.SQLite will be easier to accomplish with that new version, because it will be already adapted to the latest SQLite version. So I would recommend to wait a few days (or to use a recent development snapshot).

[kroimon]

I would be very interested in any solution allowing me to use SQLite3 Multiple Ciphers in .NET as I need to implement support for reading databases encrypted by wxsqlite3 (using AES128 CBC) in a .NET application.

I had a quick look around and I feel like this might be easier to do with Microsoft.Data.Sqlite as it is using SQLitePCLRaw which is specifically built to use a variety of sqlite libraries.
@utelle Do you think there are any reasons this might not work with just using the SQLite3 Multiple Ciphers release binaries directly with SQLitePCLRaw?

[utelle]

@kroimon

Do you think there are any reasons this might not work with just using the SQLite3 Multiple Ciphers release binaries directly with SQLitePCLRaw?

Since SQLitePCLRaw seems to use SQLite DLLs, I see no reason why it should not work. The SQLite3 Multiple Ciphers DLLs provided with the releases support the same API as the original SQLite DLL.

There are a few additional functions in the SQLite3 Multiple Ciphers interface, but they are not needed to make use of the SQLite3 Multiple Ciphers DLL, because the encryption extension can be configured via SQL pragma statements or via URI parameters (on opening a SQLite database).

That is, renaming the DLL coming with SQLite3 Multiple Ciphers releases to sqlite3.dll should allow you to simply replace the DLL used by SQLitePCLRaw.

However, in respect to security using SQLite DLLs is not ideal, because an attacker could easily intercept calls into the DLL.

[haiduong87]

Hi @MarcelVersteeg,

Did you success making your own System.Data.SQlite?
I'm in this trouble right now.
I try to follow the conversation, but the source code of both projects have some recent updates...

@utelle ,
Can you help me here (I can't find any "amalgamation" file in this source code, https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki - sqlite-netFx-source-1.0.114.0.zip (7.67 MiB))

[utelle]

Can you help me here (I can't find any "amalgamation" file in this source code, https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki - sqlite-netFx-source-1.0.114.0.zip (7.67 MiB))

If you unzip the archive sqlite-netFx-source-1.0.114.0.zip you will find therein subdirectory SQLite.Interop/src/core. This subdirectory contains the files sqlite3.c and sqlite3.h, which constitute the SQLite amalgamation. The first step would be to replace those 2 files by the SQLite3 Multiple Ciphers amalgamation (sqlite3mc_amalgamation.c and sqlite3mc_amalgamation.h). However, this is not enough. In a second step you need to adjust the file SQLite.Interop/src/generic/interop.c to resolve compilation conflicts. Additionally, it is most likely necessary to adjust the solution/project files by adding or changing some preprocessor symbols.

[utelle]

I will convert this issue into a discussion, because the SQLite3 Multiple Ciphers amalgamation source code can be used as a drop-in replacement for the SQLite3 amalgamation source code used in System.Data.SQLite. As described in my previous post full integration in System.Data.SQLite is then only a matter of adjusting the file SQLite.Interop/src/generic/interop.c and the set of used preprocessor symbol definitions. However, this is not a task of SQLite3 Multiple Ciphers.