Escape key in community version

I’m trying to escape the key before using it in PRAGMA key =
I’ve seen comments that I can use something like
SELECT quote($password) passing password as a parameter.

However this generates an error “File is not a database” because PRAGMA key is not the first statement.

How do I build a community version that lets me do the SELECT quote(..) first?

(which is basically what Microsoft.Data.SQLite is doing)

Hello @mtissington,

With the SQLCipher 4.7.0 release, the library updated the baseline of SQLite to 3.49.1. There were several notable changes from the upstream in that release that include:

  • SELECT statements (including schema independent queries like SELECT 1) cannot be executed prior to setting the database key on encrypted database

This explains the behavior you are seeing. One possible workaround would be to open a separate connection using the :memory: identifier as the filename and performing your SELECT quote(...) operation, then closing that connection.

Hmm, this is a section of the Open code from the current release of Microsoft.Data.SQLite.Core which obviously I can’t change.

 // NB: SQLite doesn't support parameters in PRAGMA statements, so we escape the value using the
                //     quote function before concatenating.
                var quotedPassword = ExecuteScalar(
                    "SELECT quote($password);",
                    connectionOptions.Password,
                    connectionOptions.DefaultTimeout);
                ExecuteNonQuery(
                    "PRAGMA key = " + quotedPassword + ";",
                    connectionOptions.DefaultTimeout);
``

Never mind I see this issue reported
https://github.com/dotnet/efcore/issues/35760