Hidden early PRAGMAs prevent using SQLCipher

[kashikoibumi]

In order to use encryption with SQLCipher,
it is required to issue "PRAGMA key = 'passphrase'" as the first command
just after connected.

But, current implementation of SQLx always issues
"PRAGMA journal_mode = {}; PRAGMA foreign_keys = {};"
just after connected before any user's command issued.
That prevents using SQLx with SQLCipher.

[kashikoibumi]

Specifying journal_mode Delete on SqliteConnectOptions resolved the issue.
Sorry for disturbing.

[kashikoibumi]

Creating a database can be resolved by specifying journal_mode Delete on SqliteConnectOptions,
but when reopened the created database cannot be read.
The reason is same, hidden PRAGMAs.

[kashikoibumi]

SQLx's SQLite support depends on libsqlite3-sys which also supports SQLCipher in addition to SQLite.
If you specify

libsqlite3-sys = { version = "0.18.0", features = ["sqlcipher"] }

in your application's Cargo.toml, SQLx will use SQLCipher via libsqlite3-sys.

SQLCipher source code is not bundled with libsqlite3-sys.
On Debian buster or derived distro, if libsqlcipher-dev package is installed its library file will be used.
The Debian package version of SQLCipher is sufficient for rusqlite, but not for SQLx.
When building SQLx, it complains that symbol sqlite3_prepare_v3 is undefined.

In replacement, a SQLCipher built from its source code retrieved from GitHub can be used with SQLx.
v3.4.2 tag version works.
I built it as follows:

./configure --prefix=$HOME/local --enable-load-extension=yes --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_ENABLE_COLUMN_METADATA"
make
make install

Note, if you omit SQLITE_ENABLE_COLUMN_METADATA, SQLx complains again that symbol sqlite3_column_origin_name is undefined.
And note, before running cargo build, set the environment variables as follows:

export LD_LIBRARY_PATH=$HOME/local/lib
export PKG_CONFIG_PATH=$HOME/local/lib/pkgconfig

To test disabling SQLx's hidden early PRAGMAs, modify connect function in sqlx-core/src/sqlite/options/connect.rs commenting out not to execute PRAGMAs.

    fn connect(&self) -> BoxFuture<'_, Result<Self::Connection, Error>>
    where
        Self::Connection: Sized,
    {
        Box::pin(async move {
            let mut conn = establish(self).await?;

/*
            // send an initial sql statement comprised of options
            let init = format!(
                "PRAGMA journal_mode = {}; PRAGMA foreign_keys = {};",
                self.journal_mode.as_str(),
                if self.foreign_keys { "ON" } else { "OFF" }
            );

            conn.execute(&*init).await?;
*/

            Ok(conn)
        })

In your application issue "PRAGMA key = 'passphrase'" as the first command after connection, and encryption works.

[igalic]

BTW, Diesel allows you to implement the CustomizeConnection, and then hook that into the Builder.something like that would be very useful for SQLx as well

[abonander]

Closed by #1295