Incompatible SQLCipher 4 database (SQLite3MultiplerCiphers vs DB Browser for SQLite)

[yyamasak]

I created a database using Microsoft.Data.Sqlite with SQLitePCLRaw.bundle_e_sqlcipher. I'm a newbie to these libraries. I didn't specify any special options. So I believe the database is formatted with the defaults of SQLCipher Version 4.

I could browse the contents with DB Browser for SQLite (SQLCipher). The default options for SQLCipher 4 were applied to open it.

I'm a maintainer of TkSQLite-Cipher. So, I wanted to open the database with SQLite3MultipleCiphers too. But it couldn't decrypt the database.

This is SQL version of what TkSQLite-Cipher does. I used sqlite3mc_shell_x64.exe to run this SQL.

PRAGMA cipher='sqlcipher';
--> sqlcipher
PRAGMA key='pass';
--> ok
SELECT * FROM accounts;
--> Error: file is not a database

I also checked if the database created by SQLite3MultipleCiphers can be opened with DB Browser.

PRAGMA cipher='sqlcipher';
PRAGMA rekey='pass';
CREATE TABLE accounts (ID INTEGER PRIMARY KEY, User TEXT, Password TEXT, Privilege INTEGER DEFAULT NULL);
INSERT INTO accounts (User, Password, Privilege) VALUES ('yusuke', 'pass1', 63);
SELECT * FROM accounts;
--> 1|yusuke|pass1|63

The database created by SQLite3MultipleCiphers is actually encrypted. It can be opened with sqlite3mc_shell_x64.exe and TkSQLite-Cipher.

I'm not sure which of SQLite3MultipleCiphers or Microsoft.Data.Sqlite is correct in that it follows the standard configuration of SQLCipher 4. I wish you could give me any advice.

[utelle]

I created a database using Microsoft.Data.Sqlite with SQLitePCLRaw.bundle_e_sqlcipher. I'm a newbie to these libraries. I didn't specify any special options. So I believe the database is formatted with the defaults of SQLCipher Version 4.

As far as I know Microsoft.Data.Sqlite with SQLitePCLRaw.bundle_e_sqlcipher is based on the original public SQLCipher version, and therefore it will use the default settings of SQLCipher.

I could browse the contents with DB Browser for SQLite (SQLCipher). The default options for SQLCipher 4 were applied to open it.

DB Browser for SQLite (SQLCipher) is also based on the original public SQLCipher version.

There are other SQLite management tools available like SQLiteStudio, using SQLite Multiple Ciphers and allowing to control how the encryption extension should operate.

I'm a maintainer of TkSQLite-Cipher. So, I wanted to open the database with SQLite3MultipleCiphers too. But it couldn't decrypt the database.

Looking at the commands you used to open the database file, this is not really surprising. While the original SQLCipher library, and hence DB Browser for SQLite (SQLCipher), operate in what I call legacy mode, the default for SQLite Multiple Ciphers is to operate in non-legacy mode. For details please read the chapter Legacy Cipher Modes in the SQLite3 Multiple Ciphers documentation.

I'm not sure which of SQLite3MultipleCiphers or Microsoft.Data.Sqlite is correct in that it follows the standard configuration of SQLCipher 4. I wish you could give me any advice.

To open a database file created with a legacy version of SQLCipher you need to tell SQLite3 Multiple Ciphers to use the legacy mode:

PRAGMA cipher='sqlcipher';
PRAGMA legacy=4;
PRAGMA key='passphrase';

If you want to create a SQLCipher encrypted database file with SQLite3 Multiple Ciphers in such a way that it can be accessed with legacy tools, you have to use PRAGMA legacy=4; as well on creating the database.

[yyamasak]

@utelle
Thank you very much for your advice!
I could open/create/attach an SQLCipher 4 legacy mode encrypted database in TkSQLite-Cipher by adding PRAGMA legacy=4.

[utelle]

Thank you very much for your advice!

You are welcome.

I assume the issue can now be closed. If not, feel free to re-open it.

[EthianWong]

I need some help @utelle , in my application i only use pragma key='passphrase', and i want to know how to open this db in SQLiteStudio.

I try those configuration in SQLiteStudio, but it's not work。

Password:

passphrase

Configuration:

PRAGMA kdf_iter ='256000';
PRAGMA fast_kdf_iter ='2';
PRAGMA hmac_use ='1';
PRAGMA hmac_pgno ='1';
PRAGMA hmac_salt_mask ='0x3a';
PRAGMA legacy ='0';
PRAGMA legacy_page_size ='4096';
PRAGMA kdf_algorithm ='2';
PRAGMA hmac_algorithm ='2';
PRAGMA plaintext_header_size = '0';

I don't know how to set right configuration...
if i use legacy=4 it can open with SQLiteStudio, but i don't want use legacy mode

[utelle]

I need some help @utelle , in my application i only use pragma key='passphrase',

If your application uses SQLite3MC in default mode (that is, without explicitly setting the cipher scheme), your application will use the default cipher scheme - which is ChaCha20 (not SQLCipher). However, maybe you compiled SQLite3MC yourself and changed the default cipher scheme to sqlcipher. In that case just using PRAGMA key will use the cipher configuration corresponding to SQLCipher version 4 - except for the legacy mode.

and i want to know how to open this db in SQLiteStudio.

With SQLiteStudio version 3.3.3 (released in April 2021) you can add the database by executing the menu option "Database/Add Database". In the dialog that opens, you select WxSQLite3 as the database type; this will then show additional options. There you will have to enter the password and to select the cipher:

• sqleet: ChaCha20-Poly1305 for the cipher scheme ChaCha20, or
• SQLCipher: AES 256 Bit for the cipher scheme SQLCipher

And that should be enough.

I don't know how to set right configuration... if i use legacy=4 it can open with SQLiteStudio, but i don't want use legacy mode

If you select in SQLiteStudio the database type SQLCipher, you can only open database files that use legacy=4.

[EthianWong]

@utelle thank for you help, i get it now.