search

utelle / SQLite3MultipleCiphers

SQLite3 encryption extension with support for multiple ciphers
https://utelle.github.io/SQLite3MultipleCiphers/
MIT License
410 stars 76 forks source link

Feature Request: C#/.NET SQLitePCLRaw provider #120

Closed jammerxd closed 12 months ago

jammerxd commented 1 year ago

Create a nuget packge that implements the SQLitePCLRaw provider as a bundle so that c# applications can use SQLite3MultipleCiphers.

https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/custom-versions?tabs=netcore-cli

utelle commented 1 year ago

Create a nuget packge that implements the SQLitePCLRaw provider as a bundle so that c# applications can use SQLite3MultipleCiphers.

In principle, I would be ready to create such a package. However, when I do a search for SQLitePCLRaw on nuget.org I get a really long list with 148 entries (as of today). There are different packages for different platforms. So, what sort of nuget support do you have in mind?

jammerxd commented 1 year ago

Well if you read the entire doc linked - the SQLitePCLRaw is for the raw/base compiled binaries for various platforms that bind with the .NET core provider.

So yes, there's hundreds because there's about half a dozen existing providers with various implementations of sqlite (SQLCipher is one of them) - and then multiply that by the number of platforms and other sub-dependend packages.

So we'd have to do exactly what the SQLCipher packages do starting with the ground up.

utelle commented 1 year ago

Well if you read the entire doc linked - the SQLitePCLRaw is for the raw/base compiled binaries for various platforms that bind with the .NET core provider.

So yes, there's hundreds because there's about half a dozen existing providers with various implementations of sqlite (SQLCipher is one of them) - and then multiply that by the number of platforms and other sub-dependend packages.

So we'd have to do exactly what the SQLCipher packages do starting with the ground up.

If I restrict the search on nuget.org to SQLCipher related packages, I still get 40 entries. This is still an overwhelmingly high number.

jammerxd commented 1 year ago

Again, read the documentation from Microsoft that describes what packages we need to create and what interfaces need to be implemented.

https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/custom-versions?tabs=netcore-cli

utelle commented 1 year ago

If someone is in a hurry, this recipe for using SQLite3MultipleCiphers with SQLitePCLRaw may be a good start.

I will take a look at the SQLitePCL.raw project. Maybe generating packages for SQLite3MultipleCiphers can be integrated into that project somehow.

utelle commented 1 year ago

Actually, the project ericsink/SQLitePCL.raw project depends on the project ericsink/cb. The latter creates the binaries of the native SQLite library for all supported platforms.

In a first step I tried to adjust the project cb locally to support SQLite3 Multiple Ciphers. I needed several attempts to accomplish that task, but eventually I succeeded.

Before continuing with the integration of SQLite3 Multiple Ciphers into SQLitePCL.raw we have to clarify which compile time options should be used with SQLite3 Multiple Ciphers. For the binaries included in the releases of SQLite3 Multiple Ciphers I enable quite a number of SQLite features. The cb project uses a much smaller list of defines, namely

#define SQLITE_ENABLE_COLUMN_METADATA
#define SQLITE_ENABLE_FTS3_PARENTHESIS
#define SQLITE_ENABLE_FTS4
#define SQLITE_ENABLE_FTS5
#define SQLITE_ENABLE_JSON1
#define SQLITE_ENABLE_MATH_FUNCTIONS
#define SQLITE_ENABLE_RTREE
#define SQLITE_ENABLE_SNAPSHOT
#define SQLITE_DEFAULT_FOREIGN_KEYS 1

Below is a list of options that are normally enabled for SQLite3 Multiple Ciphers in addition to the options listed above. However, probably not all are useful in the C#/.Net context:

/* Required / recommended options */
#define CODEC_TYPE CODEC_TYPE_CHACHA20
#define SQLITE_TEMP_STORE 2
#define SQLITE_USE_URI
#define SQLITE_SECURE_DELETE

/* Disable support for double-quoted literals */
#define SQLITE_DQS 0

/* Useful extensions */
#define SQLITE_ENABLE_EXTFUNC
#define SQLITE_ENABLE_GEOPOLY
#define SQLITE_ENABLE_REGEXP
#define SQLITE_ENABLE_SERIES
#define SQLITE_ENABLE_SHA3
#define SQLITE_ENABLE_UUID
#define SQLITE_SOUNDEX

/* File oriented extensions */
#define SQLITE_ENABLE_CSV
#define SQLITE_ENABLE_VSV
#define SQLITE_ENABLE_FILEIO

/* Session extension (not usuable without support in the wrapper) */
#define SQLITE_ENABLE_DESERIALIZE
#define SQLITE_ENABLE_SESSION

/* User authentication extension */
#define SQLITE_USER_AUTHENTICATION 1

Notes: A few comments regarding the above options:

Any comments from your side?

jammerxd commented 1 year ago

Let me review and I'll let you know.

utelle commented 1 year ago

In the meantime I created a fork of project ericsink/cb, added a branch sqlitemc, and adjusted the build scripts. I used a certain subset of SQLite compile time options; we can adjust that later on.

The adjusted GitHub workflow completes successfully. However, inspecting the workflow runs there are some warnings and errors:

I used the workflow file of the project ericsink/cb with only minor adjustments (adding SQLite3MC, and excluding the tests). The workflow puts the resulting binaries in a separate branch called staging. At the moment I don't know how and when these binaries are transferred to the main branch.

jammerxd commented 1 year ago

Yeah .net core 3.1 isn't a thing anymore - if you want to target .net we'll need to target the following:

.net standard 2.0/2.1

.net 6, .net 7, .net 8 (currently in CR1)

windows does have an arm64 version - and we should look into why the arm64 architecture of sqlite isn't compiling.

I'd say we should enable as many features as we can with sqlitemc with regard to the following:

Disable support for double-quoted literals I'm not sure about the side effects of disabling double quotes, but if it's disabled in the others, then it'd make sense to disable for the nuget package. Useful extensions I agree in keeping those extensions enabled. File oriented extensions I also agree with dropping them - but I haven't used them before either so I'm not really qualified on why it should or should not be enabled. Session extension Understood, if this isn't something that's supported we would either need to add it to the wrapper or disable it. I'm in favor of disabling until we have it functional andthen maybe re-evaluate. User authentication extension If it's rarely used sure. I'd be in favor of keeping it if it doesn't require any modifications - this is something I'd personally explore using with what I'm currently working on.

utelle commented 1 year ago

Yeah .net core 3.1 isn't a thing anymore - if you want to target .net we'll need to target the following:

.net standard 2.0/2.1

.net 6, .net 7, .net 8 (currently in CR1)

This is sort of a misunderstanding. SQLitePCL.raw supports all these targets (AFAIK). And the underlying native SQLite binaries are independent of the .net version. However, the GitHub workflow for generating the native libraries uses .net core 3.1. Most likely this could be changed. However, there might be a reason why the SQLitePCL.raw folks haven't done that yet.

windows does have an arm64 version - and we should look into why the arm64 architecture of sqlite isn't compiling.

Again a misunderstanding. The native library for arm64 is generated, but the one for arm is not. The reason is that some function declarations for the Neon support seem to be incorrect. And that's a bit strange, because all non-Windows builds for arm succeed.

I'd say we should enable as many features as we can with sqlitemc with regard to the following:

In principle, I agree.

Disable support for double-quoted literals I'm not sure about the side effects of disabling double quotes, but if it's disabled in the others, then it'd make sense to disable for the nuget package.

The real problem here is that the default settings of SQLite allow double-quoted literals. SQLitePCL.raw does not set this option so far, so that all other SQLitePCL.raw providers allow double-quoted literals. Therefore we maybe shouldn't use this option either - even if double-quoted literals are really bad SQL style.

File oriented extensions I also agree with dropping them - but I haven't used them before either so I'm not really qualified on why it should or should not be enabled.

These extensions are very useful in a SQLite shell, but are less common in normal applications. For now, I would not enable them.

Session extension Understood, if this isn't something that's supported we would either need to add it to the wrapper or disable it. I'm in favor of disabling until we have it functional andthen maybe re-evaluate.

Some of the more recent SQLite API additions are on the todo list of the SQLitePCL.raw folks, but that todo list hasn't been updated for 2 years. I have no idea whether work is in progress to add support for those API additions.

User authentication extension If it's rarely used sure. I'd be in favor of keeping it if it doesn't require any modifications - this is something I'd personally explore using with what I'm currently working on.

Actually, this extension also needs extra support in the wrapper to be useful. So, for now I wouldn't include it.

Additionally, you should know that support for this extension by the SQLite core team is half-hearted. If the extension is enabled the SQLite code makes checks to prevent unauthorized users from accessing the database. However, the code of the extension has a known bug (that is fixed in SQLite3 Multiple Ciphers) that I reported on the SQLite forum long ago, but up to now a fix was not applied to the original source code.

Another thing one must take into consideration is that a database file that was created with enabled user authentication can easily be opened and modified by an application that uses a SQLite version not having this extension enabled.

That is, the usefulness of this extension seems to be limited. Therefore, I tend to not enable it.

jammerxd commented 1 year ago

Gotcha - sounds like we're on the same page then with what we want to have be enabled.

utelle commented 1 year ago

Gotcha - sounds like we're on the same page then with what we want to have be enabled.

I will check the list of options I used for my first attempts. They should mostly conform already with the list given here, except for the double-quoted literals.

I already started to add support for SQLite3 Multiple Ciphers to the actual SQLitePCL.raw code base. However, the workflow of that repository expects the native binaries in the master branch, and as already said in a previous post the cb workflow puts the resulting binaries in a separate branch called staging. Most likely it is a simple git operation, but at the moment I have no idea how and when these binaries are transferred from the staging branch to the master branch, as I see no traces of such a git operation in the original repository.

utelle commented 1 year ago

In the meantime I forked the repository ericsink/SQLitePCL.raw and tried to add support for SQLite3 Multiple Ciphers.

Not very suprising the first workflow run failed. One reason is that the files generated by the cb project are not (yet) visible in branch master. There may be other glitches, too.

bricelam commented 1 year ago

I would love to help with this. But we shouldn't start by forking SQLitePCL.raw. I can create a new project structure from scratch to get all the C#/.NET/NuGet parts going. Where will the native binaries come from? Are they already being built somewhere that we can just download from?

jammerxd commented 1 year ago

That would be awesome! We currently don't have the binaries available for download, but I can confirm they do build on windows, linux and osx (x86 and m1/m2) and could potentially zip them here if needed to get started.

bricelam commented 1 year ago

We should decide which platforms we want to support. The list for the main SQLitePCLRaw bundles is quite extensive:

bricelam commented 1 year ago

Forgot some:

utelle commented 1 year ago

I would love to help with this.

That's really great!

But we shouldn't start by forking SQLitePCL.raw.

I should have asked you first. 😄

I already forked both, ericsink/SQLitePCL.raw and ericsink/cb:

I added support for SQLite3 Multiple Ciphers in both repositories, see branch sqlite3mc. The cb workflow runs successfully already, with one exception Windows ARM.

I can create a new project structure from scratch to get all the C#/.NET/NuGet parts going. Where will the native binaries come from? Are they already being built somewhere that we can just download from?

Yes, as said above I forked Eric's cb project. The binaries can be found in subdirectory bld/bin/e_sqlite3mc of the cb staging branch.

We should decide which platforms we want to support. The list for the main SQLitePCLRaw bundles is quite extensive:

Well, in principle we could support all platforms that are currently included in the cb project.

utelle commented 1 year ago

We currently don't have the binaries available for download,

Fortunately, that's no longer true, because my fork of the cb repo already generates many binaries - although they should be tested that they actually work as expected.

but I can confirm they do build on windows, linux and osx (x86 and m1/m2)

The cb project generates macOS binaries for _x8664 and arm64. We could check whether we are able to extend the procedure to generate binaries for m1/m2 as well.

bricelam commented 1 year ago

Oh wonderful! Ok, I'm a little more caught up now that I've read the entire thread. 😉 Using a cb fork to build all the binaries will work great.

The workflow puts the resulting binaries in a separate branch called staging. At the moment I don't know how and when these binaries are transferred to the main branch.

I think this is currently a manual step. Once all the binaries are ready to flow into the SQLitePCL.raw repo, he merges from staging to master. Then kicks off a build over there which pulls master.

We probably don't need to commit anything to a branch. We can probably just have our other repo download the latest GitHub Actions artifacts directly.

bricelam commented 1 year ago

My comments on compile-time options:

Disable support for double-quoted literals

Do it. This shouldn't affect any sane user, or any .NET library worth its salt that generates SQL.

These three enabled by SQLitePCLRaw are important to the higher-level .NET library EF Core:

This one makes for a much better cross-database experience:

File oriented extensions, Session extension, User authentication extension

Drop 'em.

Useful extensions

I don't know what SQLITE_ENABLE_EXTFUNC is.

In EF Core, we've pushed users toward SpatiLite instead of SQLITE_ENABLE_GEOPOLY for now.

It's very easy to use .NET's implementation for SQLITE_ENABLE_REGEXP, SQLITE_ENABLE_SHA3, and SQLITE_ENABLE_UUID, so these really would just be adding size to the binary. I'd probably just disable them.

connection.CreateFunction("regexp", Regex.IsMatch);

As for SQLITE_ENABLE_SERIES and SQLITE_SOUNDEX, I haven't seen much use of them in .NET (especially since they're not enabled by SQLitePCLRaw), so unless they're useful in context of SQLite3MC, I'd probably just disable them.

utelle commented 1 year ago

Using a cb fork to build all the binaries will work great.

I renamed the repo to SQLite3MultipleCiphers-cb to make clear it is about SQLite3 Multiple Ciphers.

Currently, the the cb workflow builds binaries for all variants of SQLite:

In my first attempt I chose the name e_sqlite3mc as the library name for the binaries - without knowing whether the e_ prefix has a special meaning or not. However, just sqlite3mc seems to be more logical - unless there are reasons for the e_ prefix.

The workflow puts the resulting binaries in a separate branch called staging. At the moment I don't know how and when these binaries are transferred to the main branch.

I think this is currently a manual step. Once all the binaries are ready to flow into the SQLitePCL.raw repo, he merges from staging to master. Then kicks off a build over there which pulls master.

Ok. However, in Eric's public cb repo I see no traces of such merge operations. So, that is a bit confusing.

We probably don't need to commit anything to a branch. We can probably just have our other repo download the latest GitHub Actions artifacts directly.

That can be adjusted later on to the needs of the NuGet workflow.

utelle commented 1 year ago

My comments on compile-time options:

Disable support for double-quoted literals

Do it. This shouldn't affect any sane user, or any .NET library worth its salt that generates SQL.

Very good. I will keep the option SQLITE_DQS=0 then.

These three enabled by SQLitePCLRaw are important to the higher-level .NET library EF Core:

  • SQLITE_ENABLE_COLUMN_METADATA
  • SQLITE_ENABLE_JSON1
  • SQLITE_ENABLE_MATH_FUNCTIONS

These options are in the basic set of options and are therefore already enabled.

This one makes for a much better cross-database experience:

  • SQLITE_DEFAULT_FOREIGN_KEYS

Same as above.

File oriented extensions, Session extension, User authentication extension

Drop 'em.

The source code contains those extensions, so it will be easy to enable them later, in case support for them is added in the .Net wrapper in the future.

Useful extensions

I don't know what SQLITE_ENABLE_EXTFUNC is.

This is an extension that provides many math and string UDFs for SQLite. It was created long before the SQLite developer team decided to add SQLITE_ENABLE_MATH_FUNCTIONS. In my own projects I frequently needed various math functions, and therefore this extension was part of wxSQLite3 (and now SQLite3 Multiple Ciphers) for already a long time. Here you find a comparison of both extensions.

Both extensions can be enabled at the same time. If SQLITE_ENABLE_MATH_FUNCTIONS is enabled the code for the corresponding functions in the EXTFUNC extension will be excluded from compilation.

In EF Core, we've pushed users toward SpatiLite instead of SQLITE_ENABLE_GEOPOLY for now.

Ok, I'll drop SQLITE_ENABLE_GEOPOLY for now.

It's very easy to use .NET's implementation for SQLITE_ENABLE_REGEXP, SQLITE_ENABLE_SHA3, and SQLITE_ENABLE_UUID, so these really would just be adding size to the binary. I'd probably just disable them.

Ok, I will disable them.

As for SQLITE_ENABLE_SERIES and SQLITE_SOUNDEX, I haven't seen much use of them in .NET (especially since they're not enabled by SQLitePCLRaw), so unless they're useful in context of SQLite3MC, I'd probably just disable them.

No, SQLite3 Multiple Ciphers itself doesn't make use of those extensions. That is, they can be disabled.

In case there is a user demand for certain extensions in the future, they can be easily enabled again.

utelle commented 1 year ago

As mentioned in a prior post generating SQLite3 Multiple Ciphers binaries failed for Windows ARM.

In the meantime I analyzed why that happened. The reason was that the header file windows.h includes somewhere internally the header file arm_neon.h, so that later on in my own code this header file was not included anymore and a little necessary tweak by defining a certain symbol was not applied, leading to invalid Neon function declarations.

This has been fixed and now the binaries for all platforms and architectures can be build successfully.

bricelam commented 1 year ago

I don't know what SQLITE_ENABLE_EXTFUNC is.

This is an extension that provides many math and string UDFs for SQLite.

Ah. Either way then. I'm sure it doesn't add much to the binary size.

utelle commented 1 year ago

I don't know what SQLITE_ENABLE_EXTFUNC is.

This is an extension that provides many math and string UDFs for SQLite.

Ah. Either way then. I'm sure it doesn't add much to the binary size.

Compared to the code of the encryption extension the size increase for the function extension is really neglectable.

For generating the binaries in SQLite3MultipleCiphers-cb the following default set of SQLite options is used (as defined in Eric Sink's repo):

SQLITE_ENABLE_COLUMN_METADATA
SQLITE_ENABLE_FTS3_PARENTHESIS
SQLITE_ENABLE_FTS4
SQLITE_ENABLE_FTS5
SQLITE_ENABLE_JSON1
SQLITE_ENABLE_MATH_FUNCTIONS
SQLITE_ENABLE_RTREE
SQLITE_ENABLE_SNAPSHOT
SQLITE_DEFAULT_FOREIGN_KEYS=1

and in addition the following options are used for the SQLite3 Multiple Ciphers variant:

CODEC_TYPE=CODEC_TYPE_CHACHA20   // Default cipher scheme
SQLITE_TEMP_STORE=2              // Temporary storage in memory
SQLITE_USE_URI=1                 // Support config parameters via URI parameters
SQLITE_DQS=0                     // Do NOT support double-quoted literals
SQLITE_SECURE_DELETE=1           // Use secure delete
SQLITE_ENABLE_EXTFUNC=1          // Enable function extension
utelle commented 12 months ago

Create a nuget packge that implements the SQLitePCLRaw provider as a bundle so that c# applications can use SQLite3MultipleCiphers.

In teamwork with @bricelam and @ericsink building a SQLite3 Multiple Ciphers NuGet package has been made a part of the main SQLitePCLRaw project. A first prerelease as NuGet package SQLitePCLRaw.bundle_e_sqlite3mc has been made yesterday. Please give it a try (and report any issues you experience).