[Question] Recompiling system.data.sqlite with wxsqlite3

[svengeance]

Hey guys (specifically Ulrich),

I'm a bit stuck in the process of compiling sqlite3 encryption with Java and C#. I am fortunate enough that somebody else has compiled a JDBC with this encryption, but it appears that I'm on my own with getting C# encryption. The default encryption method that system.data.sqlite comes with is RC4 I believe, so they are not compatible.

I recognize that this is not an issue with your repository directly, but I've scourged the internet for the past few days on these issues, and I've come across you in many different locations (sqlite mailing list, soverflow, etc..) and was hoping you could provide any advice or pointers on how to ship system.data.sqlite with your version of sqlite3.

Danke schön, wenn du mir irgendwie helfen kannst!

[utelle]

Hi Stephen,

indeed the default encryption of System.Data.SQLite is RC4 encryption. RC4 is known to be weak, and the implementation is restricted to the Windows platform. To overcome this limitation System.Data.SQLite supports the official SQLite Encryption Extension (SEE) as an alternative. However, SEE has to be purchased.

And you are correct: neither System.Data.SQLite's RC4 encryption nor SEE is compatible with the encryption extension coming with wxSQLite3.

Unfortunately I'm not aware of an existing adaptation of System.Data.SQLite to use the wxSQLite3 encryption extension. However, it should not be too hard to replace the SQLite implementation used by System.Data.SQLite by the one coming with wxSQLite3.

In fact, the main task would be to modify the subproject SQLite.Interop located in subfolder SQLite.Interop of the System.Data.SQLite source distribution.

You need all files from subfolder sqlite3/secure/src from the wxSQLite3 source distribution.

You would remove completely the subfolders SQLite.Interop/src/contrib and SQLite.Interop/src/core, since the files are all included in wxSQLite3.

From subfolder SQLite.Interop/src/ext you would remove fts5 and json1, since they are integrated part of the SQLite amalgamation wxSQLite3 is based on. You need to decide whether you want to include the extensions percentile, regexp, sha1, totype, and vtshim - if yes, this might require some effort to include them in sqlite3secure.c and to automatically activate them on opening a database connection (I did not check if and how these extensions are connected to the C# interface).

From subfolder SQLite.Interop/src/win you would remove file crypt.c. I'm not sure about the other 2 files located there: AssemblyInfo.cpp and SQLite.Interop.rc. It could be that they are required for the C# interface.

The most complex task will be to adjust the file interop.c in subfolder SQLite.Interop/src/generic. The line #include "../core/sqlite3.c" would be replaced by including sqlite3secure.c. Inclusion of extension-functions.c and win/crypt.c would be removed. I'm not so sure about how to handle the other extensions that are included later on in interop.c - it depends on how they are (or are not) connected to the C# interface.

I hope my comments give you an idea how you could start to tackle the problem.

Regards,

Ulrich

[svengeance]

Wow. This is much more than I could have asked for, in terms of guidance. I am walking grounds that are far beyond my skill level in trying my hand at this, but I know I need to learn how to do some of the more difficult things at some point. Thank you.

From my experiments this afternoon with wxSQLite, I'd arrived at the (thanks to you, proven correct) conclusion that the majority of the work would be to incorporate the C# interop interface, so that the .NET GC and other managed parts of the code can better handle the native tools I'm giving it. Your outlined instructions have given me a much better place to start from, and once I get back to cracking at it tomorrow I'll see where it takes me.

As an aside, I've also successfully compiled the windows binaries for SQLCipher. As a mixed blessing, both the JDBC and .NET wrappers exist -- but as expensively priced products. Oh well. I don't think I'm missing out by sticking with the work you've done here -- and somebody else has already gone through the trouble of getting the JDBC working. '

Thank you again so much, for your kindness and help.

[utelle]

If you succeed to adopt the wxSQLite3 encryption in System.Data.SQLite, please let me know. This might be of interest to others as well.

Please feel free to contact me again, if you need more in-depth information about the wxSQLite3 part.

[svengeance]

I do actually one more question. What is this library's relation to: https://github.com/rindeal/SQLite3-Encryption

I know that wx and this one use compatible encryption methods since I can open encrypted databases produced from your wx libray, and looking at the source files they appear the same. Was this library the work of someone else using the encryption wx provides?

[utelle]

Well, obviously the developer @rindeal copied the files of the encryption extension coming with wxSQLite3 to his project SQLite3-Encryption several years ago, added some scripting to update the wxSQLite3 encryption extension and to generate build files for the Windows platform, and intended to add ICU support (yet unfinished). Since the used SQLite3 encryption extension is in fact that of wxSQLite3, the encrypted database files are compatible, of course. However, the last commit was about 2 years ago.

I started the development of wxSQLite3 in 2005, and in 2007 I published the first version of the encryption extension, since protecting a database is a common requirement for many applications, and especially open source projects and non-profit projects usually can't afford to spend money for commercial encryption extensions. Since then quite a few developers worldwide reused the code of the wxSQLite3 encryption extension, since it is real cross-platform, offers strong AES encryption, and comes under a liberal license, which allows to include it in commercial closed-source applications.

[rindeal]

@utelle that's correct, I'll just add it all started when I needed an sqlite3 library with encryption for some projects I was working on at that time, and I had very hard time finding one that was free with free licence and that worked on Windows. Then I finally found it burried in the wxsqlite3 source code, so I decided to help others find it as well and hopefully easier by creating the above-mentioned project. I believe I reached that goal since the project is currently ranked 5th in Google for term "sqlite3 encryption". I ceased on maintaining the project as I no longer develop for windows neither use that platform, but it's still drawing the attention of ~300 unique vistors monthly.

[svengeance]

I believe I've succeeded, though I need to test out how how well this really works.

I updated the project that @rindeal created with the latest wxsqlite3 code from @utelle 's project. This gave me an easy platform since the visual studio premake files were readily available. From there I just rudimentarily appended SDS/interop.c to the wxs/sqlite3secure file. My logic here is that 99% of what needed to be done was to give wxs's sqlite the interop interfaces that let C#/.NET assist in memory and connection management. This compiled seamlessly with the output of Jan's premake setup, and added the requisite interop interfaces the DLL needed container to be used by system.data.sqlite. So for my goal of having JAVA <-> AES256 SQLITE3 <-> C#, I believe I am in the clear. I have tested this by building a Java project with the JDBC provided by Willena, which uses wxsqlite3, assigning the database a password via PRAGMA rekey, copying the database to my C# application which uses my newly compiled SQLite.Interop.dll, and successfully opening it with the ConnectionString "Data Source=password.sqlite;Password=password" and running a test query.

I have no idea how the extension functions will work; I'm most likely going to tackle those and ensure they get properly included when their respective compiler options are specified.

I'd like to thank you both for the tools you've provided. I honestly have no clue what I'm doing, and I'm a bit out of my league when it comes to this stuff. If you notice any immediate flaws in what I've done, please let me know. I do intend to publish the final result on Github this weekend, so I'd like to clean up the mess I've made with this merge and make it look somewhat presentable.

Best,
Sven

[utelle]

Congratulations!

Most important is that it works for you. Maybe it would ne nice to have a repository which combines the advantages of SQLite3-Encryption, JDBC (Willena) and System.Data.SQLite.

I have no idea how the extension functions will work

Are you talking about the file extensionfunctions.c? If yes, this SQLite extension adds a bunch of functions which can then be used from SQL statements. No changes to the interface are required.

[Nicola1969]

Hi Utelle,
I managed to integrate wxsqllite into system.data.sqllite 1.0.109.0 thanks to your instructions.
Inside my C#.NET process it seems to work fine, I am able to crypt and decrypt .db files.
using

#define CODEC_TYPE_AES256 2

I have a problem if i try to open a crypted db file with an external GUI used by colleagues, GUI is Database .NET (v 26.1 of November, 2, 2018) and you can find a trial version here

https://fishcodelib.com/index.htm

The GUI uses AES256 v3.8 and is not able to open db encrypted by wxSqlite

I am a bit confused because I thought that AES256 was a standard encryption algorithm.

Could you please suggest if there is a solution to this issue? Is there any setting to add/change in the code?

Thank you in advance

Best Regards

Nicola

[utelle]

Hi @Nicola1969,

I managed to integrate wxsqllite into system.data.sqllite 1.0.109.0 thanks to your instructions.
Inside my C#.NET process it seems to work fine, I am able to crypt and decrypt .db files.
using

#define CODEC_TYPE_AES256 2

I have a problem if i try to open a crypted db file with an external GUI used by colleagues, GUI is Database .NET (v 26.1 of November, 2, 2018) ...
The GUI uses AES256 v3.8 and is not able to open db encrypted by wxSqlite

I am a bit confused because I thought that AES256 was a standard encryption algorithm.

While the algorithm AES 256 Bits is a standard encryption algorithm, there are various ways to use it. For example, you can change the way how to determine the initial vector and the way how to derive the effective encryption key from the passphrase entered by the user.

Unfortunately, there exist several different implementations for the encryption of SQLite databases. Among them the official SQLite Encryption extension (SEE) or SQLCipher, both using AES 256 Bit, but with different parameters. While the former (SEE) is closed source and therefore not supported by wxSQLite3, the latter (SQLCipher) is open source and supported by wxSQLite3.

The GUI tool Database .NET (v 26.1 of November, 2, 2018) supports 2 encryption methods:

1. RC4 - the encryption method coming with System.Data.SQLite
2. AES256 (v3.8) - SQLCipher based on SQLite 3.8

While method 1 is not supported by wxSQLite3, method 2 is. That is, you should be able to create a database that can be opened in the GUI tool and vice versa by just specifying the correct cipher parameters.

With the latest release of wxSQLite3 you can use a database URI for this purpose. When using your wxSQLite3 based library use a database URI like the following:

file:databaseFilename?cipher=sqlcipher&legacy=1&key=passphrase

Replace databaseFilename by the filename of your database file and passphrase by the password/key you intend to use as your encryption key.

[Nicola1969]

@utelle
Thank you very much. I set Sqlcipher as encryption mode and Legacy flag to 1 and so .db files encrypted by code are now usable by GUI and viceversa.

Best regards

Nicola

[MarcelVersteeg]

Two years have passed since the original question. Now there is a sqlite3mc_amalgamation in the source distribution.

I am also trying to build a new Sqlite.Interop.dll (from the System.Data.Sqlite solution) with the encryption enabled. I simply replaced the sqlite3.c and sqlite3.h files in src\core with the amalgation files in the wxsqlite3 distribution. However, this gives me some compile errors and warnings:

sqlite.interop\src\generic\../core/sqlite3.c(43095): error C2375: 'sqlite3_win32_write_debug': redefinition; different linkage
sqlite.interop\src\generic\../core/sqlite3.c(56): note: see declaration of 'sqlite3_win32_write_debug'
sqlite.interop\src\generic\../core/sqlite3.c(43578): error C2375: 'sqlite3_win32_utf8_to_unicode': redefinition; different linkage
sqlite.interop\src\generic\../core/sqlite3.c(62): note: see declaration of 'sqlite3_win32_utf8_to_unicode'
sqlite.interop\src\generic\../core/sqlite3.c(43594): error C2375: 'sqlite3_win32_unicode_to_utf8': redefinition; different linkage
sqlite.interop\src\generic\../core/sqlite3.c(57): note: see declaration of 'sqlite3_win32_unicode_to_utf8'
sqlite.interop\src\generic\../core/sqlite3.c(43610): error C2375: 'sqlite3_win32_mbcs_to_utf8': redefinition; different linkage
sqlite.interop\src\generic\../core/sqlite3.c(58): note: see declaration of 'sqlite3_win32_mbcs_to_utf8'
sqlite.interop\src\generic\../core/sqlite3.c(43626): error C2375: 'sqlite3_win32_mbcs_to_utf8_v2': redefinition; different linkage
sqlite.interop\src\generic\../core/sqlite3.c(59): note: see declaration of 'sqlite3_win32_mbcs_to_utf8_v2'
sqlite.interop\src\generic\../core/sqlite3.c(43642): error C2375: 'sqlite3_win32_utf8_to_mbcs': redefinition; different linkage
sqlite.interop\src\generic\../core/sqlite3.c(60): note: see declaration of 'sqlite3_win32_utf8_to_mbcs'
sqlite.interop\src\generic\../core/sqlite3.c(43658): error C2375: 'sqlite3_win32_utf8_to_mbcs_v2': redefinition; different linkage
sqlite.interop\src\generic\../core/sqlite3.c(61): note: see declaration of 'sqlite3_win32_utf8_to_mbcs_v2'
sqlite.interop\src\generic\../core/sqlite3.c(251764): warning C4456: declaration of 'j' hides previous local declaration
sqlite.interop\src\generic\../core/sqlite3.c(251718): note: see declaration of 'j'
sqlite.interop\src\generic\../core/sqlite3.c(262970): warning C4057: 'function': 'const u8 *' differs in indirection to slightly different base types from 'char [4]'
sqlite.interop\src\generic\../core/sqlite3.c(263228): warning C4057: 'function': 'const u8 *' differs in indirection to slightly different base types from 'char [4]'
sqlite.interop\src\generic\../contrib/extension-functions.c(147): error C2371: 'int16_t': redefinition; different basic types
C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC\include\stdint.h(18): note: see declaration of 'int16_t'
sqlite.interop\src\generic\../contrib/extension-functions.c(148): error C2371: 'uint16_t': redefinition; different basic types
C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC\include\stdint.h(22): note: see declaration of 'uint16_t'
sqlite.interop\src\generic\../contrib/extension-functions.c(149): warning C4142: 'int32_t': benign redefinition of type
C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC\include\stdint.h(19): note: see declaration of 'int32_t'
sqlite.interop\src\generic\../contrib/extension-functions.c(150): warning C4142: 'uint32_t': benign redefinition of type
C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC\include\stdint.h(23): note: see declaration of 'uint32_t'
src\generic\interop.c(1069): warning C4057: 'function': 'u32 *' differs in indirection to slightly different base types from 'int *'
sqlite.interop\src\generic\../ext/sha1.c(43): warning C4005: 'rol': macro redefinition
sqlite.interop\src\generic\../core/sqlite3.c(243623): note: see previous definition of 'rol'
sqlite.interop\src\generic\../ext/sha1.c(44): warning C4005: 'ror': macro redefinition
sqlite.interop\src\generic\../core/sqlite3.c(243624): note: see previous definition of 'ror'

To compile, I have set the following additional compiler defines to the C++ projects (I only want to support SQLCipher in my build):

CODEC_TYPE=CODEC_TYPE_SQLCIPHER
WXSQLITE3_HAVE_CIPHER_AES_128_CBC=0
WXSQLITE3_HAVE_CIPHER_AES_256_CBC=0
WXSQLITE3_HAVE_CIPHER_CHACHA20=0
WXSQLITE3_HAVE_CIPHER_SQLCIPHER=1
WXSQLITE3_HAVE_CIPHER_RC4=0

To prevent the errors, I commented out some code and saw that the sqlite_key function was indeed exported from the Sqlite.Interop.dll. However, when using that version, I get an access violation in the sqlite_key function.

@utelle Can you indicate what I might be doing wrong? I am using wxsqlite3-4.6.1

Regards,
Marcel

[utelle]

Two years have passed since the original question. Now there is a sqlite3mc_amalgamation in the source distribution.

The reason for this is that the so called SQLITE_HAS_CODEC API vanished from the original SQLite source code in February 2020. To overcome this obstacle I started a new GitHub project, SQLite3 Multiple Ciphers, which is based on a different implementation of the encryption extension. The sqlite3mc_amalgamation you see in wxSQLite3 comes from this new project.

I am also trying to build a new Sqlite.Interop.dll (from the System.Data.Sqlite solution) with the encryption enabled. I simply replaced the sqlite3.c and sqlite3.h files in src\core with the amalgation files in the wxsqlite3 distribution. However, this gives me some compile errors and warnings:

This is not really surprising, because there is quite some more work involved as just replacing the files sqlite3.c and sqlite3.h.

For example, the file contrib/extension-functions.c (or to be precise the equivalent of it) is already part of the new amalgamation. This is also true for several extensions located in folder ext/. The file win/crypt.c is not needed, because all encryption functions are included in the amalgamation. Finally, the file generic/interop.c needs to be adjusted - some code needs to be modified, some needs to be removed.

To compile, I have set the following additional compiler defines to the C++ projects (I only want to support SQLCipher in my build):

CODEC_TYPE=CODEC_TYPE_SQLCIPHER
WXSQLITE3_HAVE_CIPHER_AES_128_CBC=0
WXSQLITE3_HAVE_CIPHER_AES_256_CBC=0
WXSQLITE3_HAVE_CIPHER_CHACHA20=0
WXSQLITE3_HAVE_CIPHER_SQLCIPHER=1
WXSQLITE3_HAVE_CIPHER_RC4=0

On compiling you need to use the right set of defines to make things work. For example, SQLite3 Multiple Ciphers typically uses the following set of defines:

    CODEC_TYPE=CODEC_TYPE_SQLCIPHER /* your preferred default cipher scheme */
    SQLITE_ENABLE_DEBUG=0
    SQLITE_THREADSAFE=1
    SQLITE_DQS=0
    SQLITE_MAX_ATTACHED=10
    SQLITE_SOUNDEX=1
    SQLITE_ENABLE_COLUMN_METADATA=1
    SQLITE_SECURE_DELETE=1
    SQLITE_ENABLE_DESERIALIZE=1
    SQLITE_ENABLE_FTS3=1
    SQLITE_ENABLE_FTS3_PARENTHESIS=1
    SQLITE_ENABLE_FTS4=1
    SQLITE_ENABLE_FTS5=1
    SQLITE_ENABLE_JSON1=1
    SQLITE_ENABLE_RTREE=1
    SQLITE_ENABLE_GEOPOLY=1
    SQLITE_CORE=1
    SQLITE_ENABLE_EXTFUNC=1
    SQLITE_ENABLE_CSV=1
    SQLITE_ENABLE_VSV=1
    SQLITE_ENABLE_SHA3=1
    SQLITE_ENABLE_CARRAY=1
    SQLITE_ENABLE_FILEIO=1
    SQLITE_ENABLE_SERIES=1
    SQLITE_ENABLE_UUID=1
    SQLITE_ENABLE_REGEXP=1
    SQLITE_TEMP_STORE=2
    SQLITE_USE_URI=1
    SQLITE_USER_AUTHENTICATION=1

Of course, you may disable many of the enabled extensions, if you don't need them.

To be compatible with the original SQLCipher scheme, you would have to define additionally

SQLITE3MC_USE_SQLCIPHER_LEGACY

to select the SQLCipher legacy mode.

And if you need compatibility with a version of SQLCipher below version 4, you would have to define

SQLCIPHER_VERSION_DEFAULT=n

where n is the SQLCipher version number with which you want to be compatible.

To prevent the errors, I commented out some code and saw that the sqlite_key function was indeed exported from the Sqlite.Interop.dll. However, when using that version, I get an access violation in the sqlite_key function.

The SQLite3 Multiple Ciphers amalgamation contains also the function sqlite3_key. I don't know which implementation gets called; and you have to make sure that the code is compiled with matching calling conventions.

@utelle Can you indicate what I might be doing wrong? I am using wxsqlite3-4.6.1

Well, adjusting generic/interop.c is unfortunately not a trivial task. My earlier posts to this issue might give you an idea how to proceed.