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

efcore/src/Microsoft.Data.Sqlite.Core/SqliteConnectionInternal.cs

Lines 111 to 117 in 6e10434

	var quotedPassword = ExecuteScalar(
	"SELECT quote($password);",
	connectionOptions.Password,
	connectionOptions.DefaultTimeout);
	ExecuteNonQuery(
	"PRAGMA key = " + quotedPassword + ";",
	connectionOptions.DefaultTimeout);

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);