With NuGet version 2.1.11 cipher=rc4 is not working any more

[uholeschak]

With NuGet Version version 2.1.10 it was possible to open a database with the connection settings:

            SqliteConnectionStringBuilder sqliteConnectionString = new SqliteConnectionStringBuilder
            {
                DataSource = "file:" + databaseFile + "?cipher=rc4",
                Mode = SqliteOpenMode.ReadOnly,
                Password = DatabaseFunctions.DatabasePassword,
            };
            SqliteConnection mDbConnection = new SqliteConnection(sqliteConnectionString.ConnectionString);
            mDbConnection.Open();

After NuGet update 2.1.11 this fails with the exception: File is not a database.
Is cipher=rc4 not supported any more or has the connection string changed?

[utelle]

With NuGet Version version 2.1.10 it was possible to open a database with the connection settings: [...]
After NuGet update 2.1.11 this fails with the exception: File is not a database. Is cipher=rc4 not supported any more or has the connection string changed?

The NuGet project SQLitePCL.raw is maintained by @ericsink. He was urged to make a new release supporting the latest SQLite3 Multiple Ciphers version 2.1.0. Unfortunately, the building process does not include a test of the encryption feature, otherwise the issue with opening encrypted databases would have been detected probably before making the release.

In my testbed project SQLite3MultipleCiphers-NuGet the tests of the encryption feature in PR #9 (created 2 days ago by @bricelam) fails for the SQLCipher cipher scheme with the same error message (File is not a database).

Of course, SQLite3 Multiple Ciphers still supports all cipher schemes listed in its documentation. And the connection string hasn't changed either, AFAIK. However, 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-L114

                var quotedPassword = ExecuteScalar(
                    "SELECT quote($password);",
                    connectionOptions.Password,
                    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.

I suspect that not only SQLite3 Multiple Ciphers but all SQLite encryption extensions will malfunction due to this behaviour of Microsoft.Data.SQLite, as soon as SQLite version 3.48.0 or higher is used.

The code in Microsoft.Data.SQLite should be changed - preferrably to call sqlite3_key explicitly instead of using PRAGMA key.

The only workaround I see at the moment is to specify the encryption key as a URI parameter in the connection string instead of using the Password property. However, that workaround is far from ideal, because the encryption key will then be kept in memory as long as the database connection is open.

[uholeschak]

Thanks for the info, in my case the password is not a security the problem.
How can I specify the password in the URI? Simply by adding &key=<pwd>?

[baaaaif]

I've had this problem for a while in combination with the sqlcipher PRAGMAS cipher_plaintext_header_size and cipher_salt

I have a workaround for it:

• Remove the password from the ConnectionString/ConnectionStringBuilder.
• Execute the necessary pragmas immediately after opening the connection

connection.Open();

//this will allow double-quotes in identifiers - can be omitted
SQLitePCL.raw.sqlite3_db_config(connection.Handle, SQLitePCL.raw.SQLITE_DBCONFIG_DQS_DDL, 1, out int _);
SQLitePCL.raw.sqlite3_db_config(connection.Handle, SQLitePCL.raw.SQLITE_DBCONFIG_DQS_DML, 1, out int _);

//Workaround für fehlende Hooks / PRAGMA key selbst ausführen
//https://github.com/dotnet/efcore/issues/29186
if (isDatabaseEncrypted)
{
    using (SqliteCommand command = connection.CreateCommand())
    {
        command.CommandText =
            """
            PRAGMA cipher  = 'sqlcipher';
            PRAGMA legacy=4;
            """;
        command.ExecuteNonQuery();
    }

    using (SqliteCommand command = connection.CreateCommand())
    {
        command.CommandText = $"PRAGMA key = '{GetDatabasePassword()}';";
        command.ExecuteNonQuery();
    }
}

[utelle]

Thanks for the info, in my case the password is not a security the problem. How can I specify the password in the URI? Simply by adding &key=<pwd>?

Yes. If the password contains non-printable characters, you can use &hexkey=<hexpswd> and specify the key in hexadecimal notation.

[uholeschak]

Thanks the solution seems to work.

[ericsink]

"Unfortunately, the building process does not include a test of the encryption feature"

Actually, I think it does, but the test was clearly somehow deficient in this case.

[utelle]

"Unfortunately, the building process does not include a test of the encryption feature"

Actually, I think it does, but the test was clearly somehow deficient in this case.

Yes, you are right, there are tests of the encryption feature. Sorry for overlooking that. However, those tests don't go through the Microsoft.Data.SQLite layer (AFAICT), so that the flaw in Microsoft.Data.SQLite was not catched.

Actually, I was surprised by the behaviour change in the underlying SQLite library, because the problem affects even the official SQLite Encryption Extension (SEE), but most likely the SQLite developers test only with System.Data.SQLite (which doesn't suffer from the problem) and are simply not aware of the issue.

It is unlikely that the behaviour of SQLite will be reverted to the previous behaviour. Therefore users will have to use a workaround, until hopefully the Microsoft.Data.SQLite implementation will be changed to handle the password property correctly under the new circumstances.