Setting the password for an encrypted database fails, if version 3.48.0 or higher of the underlying SQLite library is used

[utelle]

Bug description

The behaviour of SQLite itself changed beginning with version 3.48.0. Since then simple queries like SELECT 1; touch the underlying database file, although the database schema is not affected - this was not the case for prior versions of SQLite.

Unfortunately, Microsoft.Data.SQLite uses such a SQL statement to quote the password, before setting the encryption key with PRAGMA key - see

https://github.com/dotnet/efcore/blob/6e104349a2c4041bcc7447541828940bdf3223be/src/Microsoft.Data.Sqlite.Core/SqliteConnectionInternal.cs#L111-L117

This SQL statement accesses the SQLite database file internally, before the encryption key is set, and therefore throws the error message File is not a database.

The code should be changed - either by quoting the password without using SQL or by calling sqlite3_key instead of using PRAGMA key.

Source code and a database file exposing the issue can be found here: Source: https://github.com/utelle/SQLite3MultipleCiphers-NuGet/blob/main/Tests/Tests/Tests.cs Database: https://github.com/utelle/SQLite3MultipleCiphers-NuGet/blob/main/Tests/Tests/sqlcipher-4.0-testkey.db

Error messages: https://github.com/utelle/SQLite3MultipleCiphers-NuGet/actions/runs/13732334999/job/38411482080?pr=9

Your code

// Part of the test application (see https://github.com/utelle/SQLite3MultipleCiphers-NuGet/blob/main/Tests/Tests/Tests.cs)
public void Database_is_SQLCipher4()
{
    // Test to access database encrypted with SQLCipher version 4
    // Result: 78536 1 1 one one 1 2 one two
    using var connection = new SqliteConnection("Data Source=file:sqlcipher-4.0-testkey.db?cipher=sqlcipher&legacy=4;Password=testkey");

    var value = connection.ExecuteScalar<int>("select count(*) from t1");

    Assert.Equal(78536, value);
}

Stack traces

Failed Tests.Database_is_SQLCipher4 [46 ms]
  Error Message:
   Microsoft.Data.Sqlite.SqliteException : SQLite Error 26: 'file is not a database'.
  Stack Trace:
     at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal.RetryWhileBusy(Func`1 action, Action reset, Int32 timeout, Stopwatch timer)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal.RetryWhileBusy(Func`1 action, Int32 timeout, Stopwatch timer)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal.ExecuteScalar(String sql, String p1, Int32 timeout)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal..ctor(SqliteConnectionStringBuilder connectionOptions, SqliteConnectionPool pool)
   at Microsoft.Data.Sqlite.SqliteConnectionPool.GetConnection()
   at Microsoft.Data.Sqlite.SqliteConnectionFactory.GetConnection(SqliteConnection outerConnection)
   at Microsoft.Data.Sqlite.SqliteConnection.Open()
   at Dapper.SqlMapper.ExecuteScalarImpl[T](IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 3022
   at Dapper.SqlMapper.ExecuteScalar[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 597
   at Tests.Database_is_SQLCipher4() in D:\a\SQLite3MultipleCiphers-NuGet\SQLite3MultipleCiphers-NuGet\Tests\Tests\Tests.cs:line 44
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
   at System.Reflection.MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)

Microsoft.Data.Sqlite version

8.0.13

Target framework

.NET 8.0

Operating system

Microsoft Windows Server 2022

[bricelam]

I think there are two options forward here:

1. Re-implement the quote logic in .NET
2. Call quote on a temporary in-memory database. (Not the target database.)

Note, we use PRAGMA key because it's the common API that works across all the SQLite encryption solutions.

[utelle]

I think there are two options forward here:

1. Re-implement the quote logic in .NET

In principle, it would be possible to use SQLite's function sqlite3_mprintf or sqlite3_snprintf, but AFAIK SQLitePCL.Raw doesn't expose those SQLite API functions.

2. Call quote on a temporary in-memory database. (Not the target database.)

That is certainly an option that would always work.

An alternative could be to always use PRAGMA hexkey instead of PRAGMA key, but this option may not work for all encryption implementations.

Note, we use PRAGMA key because it's the common API that works across all the SQLite encryption solutions.

I'm not aware of any (open source) SQLite encryption solution that doesn't implement sqlite3_key, but of course there may exist (commercial) solutions which don't expose the API function sqlite3_key.

[utelle]

IMHO solving the issue could be easily done by adding the following local method

public static string QuoteSqlLiteral(string input)
{
    if (input == null)
        return "''";

    // Truncate at first \0 to mimic SQLite quote behavior
    int nullIndex = input.IndexOf('\0');
    if (nullIndex >= 0)
        input = input.Substring(0, nullIndex);

    // Duplicate single apostrophe characters and enclose the resulting string in apostrophes
    return "'" + input.Replace("'", "''") + "'";
}

and then calling this method instead of using a SELECT SQL statement:

var quotedPassword = QuoteSqlLiteral(connectionOptions.Password);
ExecuteNonQuery("PRAGMA key = " + quotedPassword + ";", connectionOptions.DefaultTimeout);

[utelle]

@SamMonoRT:

SamMonoRT modified the milestones: 10.0.0, 11.0.0 2 weeks ago

Obviously, you pushed the resolution of this bug to milestone 11.0.0 - that is, into a distant, unknown future. Is there a specific reason for postponing to fix this annoying bug (which can be easily fixed without any side effects, by the way)?

Not fixing this bug prevents the use of encrypted databases in Microsoft.Data.SQLite with SQLite version 3.48.0 and higher. Therefore, please reconsider your decision. TIA.

[DoubleDBE]

@utelle:

Do you know of a workaround that can be applied in consumer code?

If we have to wait another 1+ year for this tiny little fix i'd rather solve it myself somehow. 🙄

[utelle]

Do you know of a workaround that can be applied in consumer code?

If we have to wait another 1+ year for this tiny little fix i'd rather solve it myself somehow. 🙄

There are 2 possible workarounds:

1. Use PRAGMA SQL commands instead of the password property Immediately after opening the database connection you configure and apply the encryption scheme via PRAGMA SQL commands:

PRAGMA cipher=sqlcipher; -- or any other supported cipher scheme
PRAGMA legacy=4;         -- optionally configure the cipher scheme
PRAGMA key='passphrase'; -- apply cipher scheme

2. Use URI parameters instead of 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 passphrase will float around in memory until the database connection is closed - imposing a security risk.

[sjlombardo]

Use PRAGMA SQL commands instead of the password property

With connection pooling enabled (which I believe is the default) it may be risky to use this approach since you don't have full control over the connection lifecycle. This is especially true when using MDSC through EntityFramework Core. If you do it would be best to add Pooling=false to the connection string, and/or very carefully manage the connections.

[utelle]

Use PRAGMA SQL commands instead of the password property

With connection pooling enabled (which I believe is the default) it may be risky to use this approach since you don't have full control over the connection lifecycle. This is especially true when using MDSC through EntityFramework Core. If you do it would be best to add Pooling=false to the connection string, and/or very carefully manage the connections.

@sjlombardo: Thanks for pointing out this potential peculiarity.

In fact, support for encrypted databases in Microsoft.Data.SQLite could be improved. If a non-default encryption scheme is used, the current implementation for opening a connection would most likely not work properly, because SQLite is forced to access the database file immediately after the passphrase has been set with PRAGMA key and thereafter other additional pragmas are excuted.

For SQLite3 Multiple Ciphers a consequence is that non-default cipher configurations should be done via URI parameters. I don't know how this could be solved for your SQLCipher product, if a non-default cipher configuration requires additional PRAGMA commands after PRAGMA key.

[bricelam]

The Password connection string keyword is only designed for the 80% case. Using PRAGMA statements for more advanced scenarios was anticipated and is not considered a workaround. (But there is still a bug here regarding the quote query.)

[utelle]

The Password connection string keyword is only designed for the 80% case. Using PRAGMA statements for more advanced scenarios was anticipated and is not considered a workaround.

Regarding SQLite3 Multiple Ciphers the only obstacle is that the current implementation of handling the Password connection string keyword uses the quote query. No additional PRAGMA commands are mandatory to configure advanced scenarios, because cipher scheme configuration can be done completely via URI parameters. That is, in principle, a working Password connection string keyword is enough, to work with encrypted databases in any scenario. (This may be different for other SQLite encryption extension implementations.)

The workaround I described is just a way to overcome the problem that using the Password connection string keyword no longer works, if SQLite version 3.48.0 or higher is used (current version is 3.50.4 with version 3.51.0 to be expected within October).

But there is still a bug here regarding the quote query.

Yes, indeed. And it is a pity that fixing this issue was postponed until version 11.0.0 with an unknown release date.

The issue is a local problem, and it could be fixed locally without any side effects - either by mimicking the quote query with a small function, or - as you suggested earlier - by executing the query in a temporary in-memory database connection.

[SamMonoRT]

Yes, indeed. And it is a pity that fixing this issue was postponed until version 11.0.0 with an unknown release date.

Based on the severity, there is always a possibility to fix and release as a 10.0.x service release. We will investigate and get back to you. You are also welcome to create a PR and we can review it

[utelle]

Yes, indeed. And it is a pity that fixing this issue was postponed until version 11.0.0 with an unknown release date.

Based on the severity, there is always a possibility to fix and release as a 10.0.x service release. We will investigate and get back to you.

I'm pretty sure many developers would appreciate a fix for this issue rather sooner than later.

You are also welcome to create a PR and we can review it

Well, I'm just the developer of SQLite3 Multiple Ciphers, and I use mainly C/C++. Actually, C# and .Net is not my home turf. Therefore I'm a bit reluctant to create a PR.

In a previous post I added code for a function mimicking the quote query. However, the solution proposed by @bricelam using the quote query on a temporary in-memory database connection may be preferred.

[julianthurner]

I think the in-memory approach is more solid since it wouldn't make problems if future versions of SQLite change the implementation of the quote() function.

A solution could be as simple as:

var quotedPassword = string.Empty;

using (var inMemoryConnection = new SqliteConnection("Filename=:memory:"))
{
    inMemoryConnection.Open();

    quotedPassword = ExecuteScalar(
        "SELECT quote($password);",
        connectionOptions.Password,
        connectionOptions.DefaultTimeout);
}

ExecuteNonQuery(
    "PRAGMA key = " + quotedPassword + ";",
    connectionOptions.DefaultTimeout);

[utelle]

@julianthurner Thanks for providing the implementation details for using a temporary memory database connection. Based on this code I created PR #36956.

@SamMonoRT Please review PR #36956.

[julianthurner]

If someone needs a workaround until the bug fix is merged, this is a working solution I am currently using:

SqliteEncryptionInterceptor.cs

public class SqliteEncryptionInterceptor : DbConnectionInterceptor
{
    private readonly IDataProtector _dataProtector;

    public SqliteEncryptionInterceptor(IDataProtectionProvider dataProtectionProvider)
    {
        _dataProtector = dataProtectionProvider.CreateProtector(AppConstants.DbPasswordStorageKey);
    }
    
    public string EncryptedPassword { get; set; } = string.Empty;

    public void SetPassword(string password)
        => EncryptedPassword = _dataProtector.Protect(password);

    public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData)
    {
        ConfigureDbConnectionWithPassword(connection);
        base.ConnectionOpened(connection, eventData);
    }

    public override Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
    {
        ConfigureDbConnectionWithPassword(connection);
        return base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
    }

    private void ConfigureDbConnectionWithPassword(DbConnection connection)
    {      
        if (EncryptedPassword.IsNullOrWhiteSpace())
        {
            throw new InvalidOperationException("Password must be set before trying to open Database connection.");
        }

        var decryptedPassword = _dataProtector.Unprotect(EncryptedPassword);

        using var cmd = connection.CreateCommand();
        cmd.CommandText = $"PRAGMA key = '{decryptedPassword}';";
        cmd.ExecuteNonQuery();
    }
}

Program.cs

services.AddSingleton<SqliteEncryptionInterceptor>()
    .AddDbContext<MyAppContext>((serviceProvider, dbContextBuilder) =>
    {
        var interceptor = serviceProvider.GetRequiredService<SqliteEncryptionInterceptor>();

        dbContextBuilder.UseSqlite(connectionString)
            .AddInterceptors(interceptor);
    });

Startup.razor

protected override async Task OnInitializedAsync()
{
    // This part depends on your implementation, i.e. MAUI Secure Storage or something similar
    var encryptionPasswordProvider = ServiceProvider.GetRequiredService<IEncryptionPasswordProvider>();
    var password = await encryptionPasswordProvider.GetOrCreatePasswordAsync();

    var sqliteEncryptionInterceptor = ServiceProvider.GetRequiredService<SqliteEncryptionInterceptor>();
    sqliteEncryptionInterceptor.SetPassword(password);

    var context = ServiceProvider.GetRequiredService<MyAppContext>();
    await context.Database.MigrateAsync();

    NavigationManager.NavigateTo("/home");
}

[utelle]

@julianthurner Your workaround will only work, if the decryptedPassword string does not contain single apostrophe characters:

cmd.CommandText = $"PRAGMA key = '{decryptedPassword}';";

Escaping apostrophe characters in the password - that's the culprit we have all this fuzz about.