SQLCipher v4 incompatibility

[Willena]

Hi !

While troubleshooting an issue (Willena/sqlite-jdbc-crypt#25) on my repo, I came to the conclusion that there is something wrong when using SQLCipher and SQLiteMC together. SQLiteMC cannot read databases created with SQLCipher and SQLCipher cannot read database created by SQLiteMC.

I did some tests myself with a fresh build of SQLCipher and using 1.0.1 binaries of SQLiteMC.
Here is the scenario I used:

1. I created a new db named fromCipher.db using SQLCipher:

SQLite version 3.33.0 2020-08-14 13:23:32 (SQLCipher 4.4.2 community)
sqlite> .open fromCipher.db 
sqlite> pragma cipher_default_settings;
PRAGMA cipher_default_kdf_iter = 256000;
PRAGMA cipher_default_page_size = 4096;
PRAGMA cipher_default_use_hmac = 1;
PRAGMA cipher_default_plaintext_header_size = 0;
PRAGMA cipher_default_hmac_algorithm = HMAC_SHA512;
PRAGMA cipher_default_kdf_algorithm = PBKDF2_HMAC_SHA512;
sqlite> pragma key='abc';
ok
sqlite> CREATE TABLE "T1" (
	"Field1"	INTEGER,
	"Field2"	INTEGER,
	"Field3"	INTEGER,
	"Field4"	INTEGER
);

2. I created a "SQLCipher" database named fromMC.db with SQLiteMC

SQLite version 3.33.0 2020-08-14 13:23:32
sqlite> .open fromMC.db
sqlite> pragma cipher = 'sqlcipher';
sqlcipher
sqlite> pragma kdf_iter = 256000;
256000
sqlite> pragma fast_kdf_iter = 2;
2
sqlite> pragma hmac_use = 1;
1
sqlite> pragma hmac_pgno = 1;
1
sqlite> pragma hmac_salt_mask = 0x3a;
58
sqlite> pragma legacy = 4;
4
sqlite> pragma legacy_page_size = 4096;
4096
sqlite> pragma kdf_algorithm = 2;
2
sqlite> pragma hmac_algorithm = 2;
2
sqlite> pragma plaintext_header_size = 0;
0
sqlite> pragma key = 'abc';
ok
sqlite> CREATE TABLE "T1" (
   ...> "Field1"INTEGER,
   ...> "Field2"INTEGER,
   ...> "Field3"INTEGER,
   ...> "Field4"INTEGER
   ...> );

3. Try to open fromMC.db with SQLCipher (it fails)

SQLite version 3.33.0 2020-08-14 13:23:32 (SQLCipher 4.4.2 community)
sqlite> .open fromMC.db
sqlite> pragma cipher_default_settings;
PRAGMA cipher_default_kdf_iter = 256000;
PRAGMA cipher_default_page_size = 4096;
PRAGMA cipher_default_use_hmac = 1;
PRAGMA cipher_default_plaintext_header_size = 0;
PRAGMA cipher_default_hmac_algorithm = HMAC_SHA512;
PRAGMA cipher_default_kdf_algorithm = PBKDF2_HMAC_SHA512;
sqlite> pragma key='abc';
ok
sqlite> select 1 from sqlite_master;
Error: file is not a database

4. Try to open fromCipher.db with SQLiteMC (it fails)

SQLite version 3.33.0 2020-08-14 13:23:32 (SQLCipher 4.4.2 community)
sqlite> .open fromCipher.db
sqlite> pragma cipher = 'sqlcipher';
sqlcipher
sqlite> pragma kdf_iter = 256000;
256000
sqlite> pragma fast_kdf_iter = 2;
2
sqlite> pragma hmac_use = 1;
1
sqlite> pragma hmac_pgno = 1;
1
sqlite> pragma hmac_salt_mask = 0x3a;
58
sqlite> pragma legacy = 4;
4
sqlite> pragma legacy_page_size = 4096;
4096
sqlite> pragma kdf_algorithm = 2;
2
sqlite> pragma hmac_algorithm = 2;
2
sqlite> pragma plaintext_header_size = 0;
0
sqlite> pragma key = 'abc';
ok
sqlite> select 1 from sqlite_master;
Error: file is not a database

Both test databases files are available here test_databases.zip

Any thoughts or ideas ?

Note : SQLite Version is 3.33.0, SQLiteMC version is 1.0.1, SQLCipher is 4.4.2
Note : SQLiteMC can write and read to it own created database as well as SQLCipher.
Note : Bug is also present in latest version (SQLiteMC 1.1.0)
This issue prevent users coming from SQLite tools like DB Browser (which support SQLCipher) to use their database with their java code (in the use case of my JDBC connector).

[utelle]

Thanks for reporting the issue.

Note : SQLite Version is 3.33.0, SQLiteMC version is 1.0.1, SQLCipher is 4.4.2

The default configuration parameters for SQLCipher 4 did not change, only the underlying SQLite version was updated.

Note : SQLiteMC can write and read to it own created database as well as SQLCipher.

The big question is, which cipher configuration parameters SQLite3MC effectively used.

Edit : Bug is also present in latest version (SQLiteMC 1.1.0)

Obviously all versions based on the new implementation of the encryption extension are affected, unfortunately. Well, there is a reason I call this project still work in progress ... 😞

This issue prevent users coming from SQLite tools like DB Browser (which support SQLCipher) to use their database with their java code.

Yes, I fully understand that. Be assured that we will find a solution soon.

Compatibility with previous versions of SQLCipher might need investigation / verifications (or a displayed warning if not compatible).

I just made a few experiments myself. It seems that the PRAGMA interface for manipulating the cipher configuration is somehow broken. If I use the C API (as in wxSQLite3) or the SQL functions (SELECT sqlite3mc_config(...);) all works as expected and I was able to open original SQLCipher databases (even the master.db file provided by @netmikey.

Any thoughts or ideas ?

Well, I will have to analyze what's going wrong when setting cipher configuration parameters via the PRAGMA interface. I will look into this issue within the next few days.

[Willena]

I just made a few experiments myself. It seems that the PRAGMA interface for manipulating the cipher configuration is somehow broken. If I use the C API (as in wxSQLite3) or the SQL functions (SELECT sqlite3mc_config(...);) all works as expected [...]

Nice to know ! Thanks

[utelle]

In fact, it is a rather stupid bug: The command PRAGMA legacy='<value>'; sets all default configuration parameters for the selected SQLCipher version ... except the legacy parameter itself ... and this results in incompatible databases.

I'm already working on a fix for the issue and will provide it within the next couple of days.

BTW, when using the legacy configuration parameter, the order of PRAGMA commands matters. That is, if you want to change some of the default settings of a legacy cipher scheme that must be done after setting the legacy parameter value.

[utelle]

Commit e27c041 should fix the issue.

@Willena:
I tested it successfully myself with original SQLCipher 4 databases. However, I'd like to ask you to test it, too, and report back. After confirmation that the issue is indeed fixed, I will make a new release. TIA.

[Willena]

@utelle I tested on few databases I had and things are working now 😄 !
Thanks for being that quick at answering issues

BTW, when using the legacy configuration parameter, the order of PRAGMA commands matters. That is, if you want to change some of the default settings of a legacy cipher scheme that must be done after setting the legacy parameter value.

Is it written somewhere ? I don't remember reading it in the documentation. I made changes to my code as it did not follow the right execution order for pragmas...

[utelle]

I tested on few databases I had and things are working now 😄 !

Great. Thanks for confirming. I will then make an official release tomorrow (or the day after).

Thanks for being that quick at answering issues

You are welcome. Thanks for reporting such issues.

BTW, when using the legacy configuration parameter, the order of PRAGMA commands matters. That is, if you want to change some of the default settings of a legacy cipher scheme that must be done after setting the legacy parameter value.

Is it written somewhere ? I don't remember reading it in the documentation.

Well, it is not yet mentioned in the documentation, but certainly should be mentioned. Currently only the SQLCipher scheme is affected, because it is the only one, for which several versions (with differing defaults) are supported.

I made changes to my code as it did not follow the right execution order for pragmas...

For selecting the default variants of SQLCipher scheme versions you only need to issue the PRAGMA legacy=<SQLCipher version> command, because under the hood it will set the defaults correctly. Only if one wants to change the defaults, additional PRAGMA commands are required - and it is only logical that they have to be issued afterwards. That is, typically you need only 2 PRAGMA commands:

-- Select SQLCipher version 4 database encryption scheme
PRAGMA cipher='SQLCipher';
PRAGMA legacy=4;

If URI parameters are used to set cipher configuration parameters, the order doesn't matter, because the code takes care and checks for the legacy parameter first.

[utelle]

I made a new release, version 1.1.2, today, and I added a note to the PRAGMA legacy command documentation.