[sqlite] Unable to provide sqlcipher encryption configuration

[szymek156]

Short description

It's impossible to set sqlcipher configuration - cipher related pragmas are not put first in order (except of key).

Affected versions

I tested on main and on v0.6.0.

Minimal example to reproduce

cargo run in:
https://github.com/szymek156/sqlx/tree/szymek156/slqcipher_configuration_example/examples/sqlite/sqlcipher

Details

Configuration of encryption scheme for sqlcipher is provided by pragma statements, similarly to the pragma key that is already treated well in repository, they also have to be set before any action on the database.

The configuration allows to set things like number of iterations in key derivation process (by PRAGMA kdf_iter), selection of KDF algorithm (PRAGMA cipher_kdf_algorithm), and others.

So for example in order to open database encrypted in v3 version of sqlcipher following pragmas has to be set:

sqlite> PRAGMA key = 'testkey';
sqlite> PRAGMA cipher_page_size = 1024;
sqlite> PRAGMA kdf_iter = 64000;
sqlite> PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
sqlite> PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;

As documentation states, and my own experiments shows, those has to be also very first pragmas to make encryption work.

Consider this example:

let mut conn = SqliteConnectOptions::from_str(&url)?
        .pragma("key", "the_password")
        .pragma("cipher_page_size", "1024")
        .pragma("kdf_iter", "64000")
        .pragma("cipher_hmac_algorithm", "HMAC_SHA1")
        .pragma("cipher_kdf_algorithm", "PBKDF2_HMAC_SHA1")
        .journal_mode(SqliteJournalMode::Delete)
        .foreign_keys(false)
        .connect()
        .await?;

It results in following pragma order:

PRAGMA key = the_password; 
PRAGMA journal_mode = DELETE;
PRAGMA foreign_keys = OFF;
PRAGMA cipher_page_size = 1024; 
PRAGMA kdf_iter = 64000;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1; 
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1; 

Resulting with following user facing error:

Error: error returned from database: (code: 26) file is not a database

(The error occurs at the stage of executing PRAGMA journal_mode)

Reordering of pragmas as follows, solves the problem:

PRAGMA key = the_password; 
PRAGMA cipher_page_size = 1024; 
PRAGMA kdf_iter = 64000;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1; 
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1; 

PRAGMA journal_mode = DELETE;
PRAGMA foreign_keys = OFF;

The fix

I think appropriate fix would be to put cipher related pragmas just after the key but before page_size, here

I can take care of it if you want.

[abonander]

Yeah, feel free to open a PR.

[abonander]

Closed by #2014