dotnet / dotnet/efcore

Add hook for sending pragmas before opening connection

Open
#29,186 4 comments 0 reactions 0 assignees View on GitHub
area-adonet-sqlite customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

SQLite and SQLCipher have pragma's that need to be set before "normal" queries are executed.

However when the password is set in the connection string, the "normal" query `SELECT COUNT(*) FROM sqlite_master;` (SqliteConnectionInternal.cs:120) is executed, preventing execution of these special pragma.

```C#
var builder = new SqliteConnectionStringBuilder
{
DataSource = Path.GetTempFileName(),
Cache = SqliteCacheMode.Private,
Mode = SqliteOpenMode.ReadWriteCreate,
Pooling = false,

Password = Guid.NewGuid().ToString()
};

for(int i = 0; i < 2; i++)
{
using var conn = new SqliteConnection(builder.ConnectionString);
conn.Open(); // Fails 2nd time with Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 26: 'file is not a database'.'

using (var command = conn.CreateCommand())
{
command.CommandText = "PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA256;";
command.ExecuteNonQuery();
}

using (var command = conn.CreateCommand())
{
command.CommandText = "PRAGMA journal_mode = WAL";
command.ExecuteNonQuery();
}
}
```
[ConsoleApp11.zip](https://github.com/dotnet/efcore/files/9633065/ConsoleApp11.zip)

A few pragma's need to be set before querying sqlite_master, for example:
- journal_mode
- kdf_iter
- cipher_kdf_algorithm
- cipher_page_size

It would be nicer if I can somehow define PRAGMAs in a SQLiteConnection before opening the connection. This would also work better with connection pooling because I don't have to manually execute the PRAGMA every time when opening a connection.

### Include version information

Microsoft.Data.Sqlite version: 6.0.9
SQLitePCLRaw: 2.1.2
Target framework: .NET 6
Operating system: Windows & Android

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.