System.Data.SQLite.Core不能使用“密码"连接字符串属性 [英] System.Data.SQLite.Core Cannot use "Password" connection string property

问题描述

我正在编写一个依赖于另一个库( Serilog.Sinks.SQLite )的.NET Core 3.1应用程序，该库正尝试将日志数据存储到SQLite数据库中.不幸的是， Serilog.Sinks.SQLite 不支持通过 SQLiteConnectionStringBuilder.Password 属性(您可以将密码传递给 System.Data.SQLite 提供给 SQLiteConnection 构造函数)，我真的很喜欢该功能.

I am writing a .NET Core 3.1 application that depends on another library (Serilog.Sinks.SQLite) which is attempting to store log data to an SQLite database. Unfortunately, Serilog.Sinks.SQLite does not support passing in a password to System.Data.SQLite via the SQLiteConnectionStringBuilder.Password property (which you provide to the SQLiteConnection constructor) and I would really like that functionality.

Serilog.Sinks.SQLite 用于连接到数据库的代码如下:

The code that Serilog.Sinks.SQLite uses to connect to the database is as follows:

private SQLiteConnection GetSqLiteConnection()
{
    var sqlConString = new SQLiteConnectionStringBuilder
    {
        DataSource = _databasePath,
        JournalMode = SQLiteJournalModeEnum.Memory,
        SyncMode = SynchronizationModes.Normal,
        CacheSize = 500,
        PageSize = (int)MaxSupportedPageSize,
        MaxPageCount = (int)(_maxDatabaseSize * BytesPerMb / MaxSupportedPageSize)
    }.ConnectionString;

    var sqLiteConnection = new SQLiteConnection(sqlConString);
    sqLiteConnection.Open();

    return sqLiteConnection;
}

StackOverflow上有许多关于使用SQLite加密的类似文章，非常有说服力地指出， System.Data.SQLite 确实支持数据库的加密/密码保护.但是，这与我的经验不符.

There are a number of similar posts on StackOverflow about encryption with SQLite stating very convincingly that encryption / password protection of the database is indeed supported by System.Data.SQLite. However, that is not matching my experience.

我抓取了 Serilog.Sinks.SQLite源代码的副本.尝试对其进行原型修改以支持指定密码.在上面的代码中添加以下内容(在连接字符串中指定Password属性)似乎很容易完成:

I grabbed a copy of the Serilog.Sinks.SQLite source in an attempt to prototype a modification to it to support specifying the password. This seems like it should be easy enough to accomplish with the following addition to the above code (specifying the Password property in the connection string):

        MaxPageCount = (int)(_maxDatabaseSize * BytesPerMb / MaxSupportedPageSize),
        Password = "mypasswordhere"
    }.ConnectionString;

不幸的是，这不起作用，并导致在启动时引发异常并显示以下消息:

Unfortunately, this does not work and results in an exception being thrown on startup with the following message:

发生异常:CLR/System.Data.SQLite.SQLiteException一个类型'System.Data.SQLite.SQLiteException'的未处理异常发生在LoggingWebApi.dll中:"SQL逻辑错误无法使用"密码"；连接字符串属性:库不是使用加密构建的支持，请参阅"https://www.sqlite.org/see"以获得更多信息'

Exception has occurred: CLR/System.Data.SQLite.SQLiteException An unhandled exception of type 'System.Data.SQLite.SQLiteException' occurred in LoggingWebApi.dll: 'SQL logic error Cannot use "Password" connection string property: library was not built with encryption support, please see "https://www.sqlite.org/see" for more information'

最让我感到困惑的是，我发现许多其他帖子声称我声称​​ System.Data.SQLite 支持此密码 .我公司另一个团队的NET Framework 4.6应用程序正在使用System.Data.SQLite.dll(尽管是较早的版本1.0.111.0)，并且此Password行为对他们来说效果很好.

What is confusing me the most here are the number of other posts I've found claiming that System.Data.SQLite supports this password and the fact that a .NET Framework 4.6 application from another team in my company is using System.Data.SQLite.dll (though an older version 1.0.111.0) and this Password behavior works fine for them.

我的代码针对的是 netcoreap3.1 ，而我对 Serilog.Sinks.SQLite 的依赖针对的是 netstandard2.0 ，这很明显我看到的区别.在我的 Serilog.Sinks.SQLite 代码的修改版本中，我引用了 System.Data.SQLite.Core 如下(通过 otnet添加包System添加).Data.SQLite.Core-版本1.0.113.1 ):

My code is targeting netcoreap3.1 and my dependency of Serilog.Sinks.SQLite is targeting netstandard2.0 so that is one obvious difference I see. In my modified version of the Serilog.Sinks.SQLite code, I am referencing System.Data.SQLite.Core as follows (added via otnet add package System.Data.SQLite.Core --version 1.0.113.1):

<PackageReference Include="System.Data.SQLite.Core" Version="1.0.113.1" />

.NET Core 3.1或.NET Standard 2.0是否存在某些导致System.Data.SQLite.Core不支持密码连接字符串属性的东西，就像其他人似乎都支持它一样?

Is there something with .NET Core 3.1 or .NET Standard 2.0 that causes System.Data.SQLite.Core to not support the Password connection string property like everyone else seems to think it supports?

我以为我的问题可能是在Linux上运行的，所以我尝试在Windows上运行，但是产生了同样的错误.

I thought maybe my issue was running on Linux so I tried running on my Windows but it produced the same error.

我确实找到了另一篇文章，引用了一种可能有用的方法，但是此示例未使用 System.Data.SQLite.Core ，而是使用了 SQLitePCLRaw.bundle_e_sqlcipher ，宁愿避免重写所有 Serilog.Sinks.SQLite 代码以使用其他SQLite客户端: https://github.com/paragpkulkarni/SQLiteEncryptionUsingEFCore

I did find another post referencing a potentially helpful approach but this sample is not using System.Data.SQLite.Core and instead is using SQLitePCLRaw.bundle_e_sqlcipher and I would prefer to avoid rewriting all of the Serilog.Sinks.SQLite code to use a different SQLite client: https://github.com/paragpkulkarni/SQLiteEncryptionUsingEFCore

推荐答案

似乎System.Data.SQLite自1.0.113.1版起已放弃对加密的支持，这解释了为什么我无法使其工作: https://system.data.sqlite.org/index.html/tktview?name = 9c330a3e03

It seems that System.Data.SQLite has dropped support for encryption as of version 1.0.113.1 which explains why I haven't been able to get it working: https://system.data.sqlite.org/index.html/tktview?name=9c330a3e03

在他们的新闻"页面上也提到了这一点，尽管我不清楚密码支持是否是旧版CryptoAPI编解码器"的一部分: https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

This is also mentioned on their News page though it was not clear to me that the Password support was part of the "legacy CryptoAPI Codec": https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

我提到的另一个团队正在使用旧版本1.0.111.0，因此，如果您需要此支持，我想就是不要升级...

The other team I referred to is using an older version 1.0.111.0 so if you require this support, I guess just don't upgrade...

这篇关于System.Data.SQLite.Core不能使用“密码"连接字符串属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！