SQLitePCLRaw.bundle_e_sqlite3mc v2.1.11 cause SQLite Error 26

[ali50m]

After upgrade to the latest version of SQLitePCLRaw.bundle_e_sqlite3mc in my personal project, it will throws a Exception says.

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 26: 'file is not a database'.'

I have simplified the issue into a test repo.

reproduce:

git clone git@github.com:ali50m/SqliteMcConsole.git
cd SqliteMcConsole
dotnet run --project .\src\SqliteMcConsole\

Run the project more than two times you will met the exception.

Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 26: 'file is not a database'.

workaround:

1. downgrade the nuget SQLitePCLRaw.bundle_e_sqlite3mc to 2.1.10.
2. Or delete the password setting in connection string.

env:

1. windows 10 21H2 19044.5737
2. dotnet SDK 9.0.203

[ali50m]

utelle/SQLite3MultipleCiphers#201

ericsink/SQLitePCL.raw#626

[ali50m]

I'm not familiar with which repo to submit this kind of issue report to, so I submitted this issue in all three repos. Please forgive me if I'm wrong.

[utelle]

After upgrade to the latest version of SQLitePCLRaw.bundle_e_sqlite3mc in my personal project, it will throws a Exception says.

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 26: 'file is not a database'.'

AFAICT this issue is related to the issue that popped up during processing PR #9 (review).

Due to a behavioural change in SQLite itself beginning with version 3.48.0 the method to open a database used by Microsoft.Data.SQLite doesn't work any longer for encrypted databases. I filed an issue dotnet/efcore#35760 about a month ago, but up to now the problem hasn't been fixed.

workaround:

1. downgrade the nuget SQLitePCLRaw.bundle_e_sqlite3mc to 2.1.10.

As long as the problem in efcore/src/Microsoft.Data.Sqlite.Core/SqliteConnectionInternal.cs is not fixed, using version 2.1.10 is most likely the easiest option you have.

2. Or delete the password setting in connection string.

Instead of using the password property, you could specify all parameters related to the cipher scheme as URI parameters in the database file name - including the key=passphrase parameter. However, specifying the passphrase as a URI parameter is not recommended, because the clear-text password will float around in memory until the database connection is closed - imposing a security risk.

Another option could be to not use the password property and to specify all cipher scheme related parameters via PRAGMA SQL commands after the database file has been opened. I haven't tested this approach myself, so it may or may not work.

I'm not familiar with which repo to submit this kind of issue report to, so I submitted this issue in all three repos. Please forgive me if I'm wrong.

IMHO this repo is the right place. However, there is nothing wrong in opening an issue in SQLitePCLRaw as well, because not only SQLite3MC might be affected.

[ali50m]

@utelle Thanks for your detailed reply, I'm glad you guys are aware of this issue. I'll follow the progress of this issue as well.

I like using this nuget for sqlite encryption, it seems to be much faster than using other encryption libraries. Of course, I'm just a user and don't know the deeper details.

[utelle]

Thanks for your detailed reply, I'm glad you guys are aware of this issue. I'll follow the progress of this issue as well.

@ali50m: My PR dotnet/efcore#36956 was merged today. That is, issue dotnet/efcore#35760 should now be fixed, and the next release of Microsoft.Data.SQLite should work again as expected.

Unfortunately, this doesn't solve the problem that NuGet distributions of SQLitePCLRaw with SQLite3 Multiple Ciphers binaries are no longer provided by the SQLitePCLRaw developers.

[ali50m]

@utelle It's great to hear to the progress.

I just tried the following latest nugets in my project, but still with no luck. No sure if these versions have integrated the patch.

    <PackageVersion Include="Microsoft.Data.Sqlite" Version="9.0.10" />
    <PackageVersion Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="9.0.10" />
    <PackageVersion Include="SQLitePCLRaw.bundle_e_sqlite3mc" Version="2.1.11" />

Or could you kindly let me know the correct workaround at this time? Thanks!

[utelle]

I just tried the following latest nugets in my project, but still with no luck. No sure if these versions have integrated the patch.

<PackageVersion Include="Microsoft.Data.Sqlite" Version="9.0.10" />
<PackageVersion Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="9.0.10" />

Well, M$s mills grind slowly. At first the task of fixing the issue was pushed to Microsoft.Data.SQLite version 11.0, but the folks at M$ could be convinced to backport the fix to version 10.0 - the corresponding PR dotnet/efcore#36962 was already merged. That is, the next NuGet release for version 10.0 will include the fix - at least I hope so. (Version 9.0.x does not include the fix and most likely never will.)

<PackageVersion Include="SQLitePCLRaw.bundle_e_sqlite3mc" Version="2.1.11" />

This SQLitePCLRaw NuGet version is currently the last one containing binaries for SQLite3MC. The way, how SQLitePCLRaw is built, changed with the advent of version 3.0. Binaries are distributed in a separate NuGet package (without encryption support).

I intend to establish an alternative, but I don't know yet, when this will happen. C#/.Net is not my home turf, you know.

Or could you kindly let me know the correct workaround at this time? Thanks!

Workarounds are described here.

[ali50m]

Thanks @utelle . The workaround is a bit hard for me to fully understand (not a db specialist). I will just wait untill the next month`s .net 10 release coming out and try again.

I'm not sure why adding password protection to SQLite is such a complicated process. But still thank you very much for your kind and detailed reply!

[utelle]

The workaround is a bit hard for me to fully understand (not a db specialist).

Actually, it's really not that complicated. At the moment you can't use the password property of the Microsoft.Data.SQLite connection object together with SQLite version 3.48.0 or higher due to a change in the SQLite implementation, because the approach how the password is currently handled internally by Microsoft.Data.SQLite doesn't work anymore. With the next release of Microsoft.Data.SQLite 10 it will hopefully function again.

For the time being, the simplest approach is to not use the password property, but to execute the SQL command PRAGMA key='passphrase'; in your own application immediately after opening the SQLite connection.

Depending on your needs, you have to specify cipher scheme configuration parameters. That should be done via URI parameters. That is, the parameters are appended to the database file name. For example,

"file:databasefilename.db?cipher=sqlcipher&legacy=4"

to select the cipher scheme SQLCipher version 4.

I will just wait untill the next month`s .net 10 release coming out and try again.

Sure, that spares you to change your application. However, I have no idea how the Microsoft release plan works and when the next version including the fix will be available.

I'm not sure why adding password protection to SQLite is such a complicated process. But still thank you very much for your kind and detailed reply!

Well, the implementation as used in Microsoft.Data.SQLite up to now worked for many years ... but it stopped working, when the SQLite developer team decided to change the internal workings of SQLite in version 3.48.0. Such things happen, unfortunately.

[ali50m]

@utelle I used the nuget SQLitePCLRaw.bundle_e_sqlite3mc in the main app and several utils app. It`s uneasy to do all the migrations and test among these apps.

Just tried the latest .net10-rc2 libs with bundle_e_sqlite3mc v2.1.11, but still failed the same. Luckily, the previous version v2.1.10 still worked in rc2. I will keep waiting for the good news...

[utelle]

Just tried the latest .net10-rc2 libs with bundle_e_sqlite3mc v2.1.11, but still failed the same.

I doubt that this release already contains the fixes. It is a release candidate for version 10.0.0. Most likely the fix will only be included in version 10.0.1. It may take weeks or even months, before such a release will be made - depends on Microsoft's release strategy.

Providing a PR for fixing the issue and convincing the Microsoft folks to backport it to version 10, instead of postponing it until version 11, was all I could do from my side.