Sqlite with encryption

[oghenez]

Is your feature request related to a problem? Please describe. I cannot access sqlite database, the databases are encrypted

Describe the solution you'd like Access to Sqlite database with the System.Data.Sqlite.dll which supported encryption

Describe alternatives you've considered Fish Database.Net fishcodelib.com

[ansgarbecker]

I already consulted this project, as their library also supports authentication with SQLite: https://github.com/utelle/SQLite3MultipleCiphers

You could check that project if the supported encryptions include one you want to see in HeidiSQL.

[ansgarbecker]

@oghenez did you check above linked project for supported encryptions?

[oghenez]

I wanted to use this System.Data.SQLite

[ansgarbecker]

Yes, you already said so. I wanted to know whether that Multiple Ciphers library supports the encryption you are using as well.

[utelle]

The project SQLite3 Multiple Ciphers supports the following cipher schemes:

• wxSQLite3: AES 128 Bit CBC - No HMAC
• wxSQLite3: AES 256 Bit CBC - No HMAC
• sqleet: ChaCha20 - Poly1305 HMAC
• SQLCipher: AES 256 Bit CBC - SHA1/SHA256/SHA512 HMAC
• System.Data.SQLite: RC4

That is, the cipher scheme System.Data.SQLite, asked for by @oghenez, is supported.

[michael-ts]

How can I set up the database to be keyed before the automatic queries are run to set up the UI? I can specify a script file to run (yech, key stored in plain text in the file system!) but this still doesn't work because it isn't run until after the boilerplate SQL such as SELECT * FROM pragma_database_list;

[a82kd]

Hi, since this is open for some longer time, just wanted to give it a ping. I have the same requirement, opening an SQLite. The database seems to use System.Data.SQLite Thanks!

[ansgarbecker]

@a82kd as @utelle pointed out, his SQLite3 Multiple Ciphers supports various ciphers. Could you please download the right dll file from their release page, place it into your HeidiSQL folder (normally C:\Program Files\HeidiSQL), and try out if that works for you?

[a82kd]

I tried that already. However, the user/pass fields are greyed out when selecting SQLite:

To be precise, the db is protected with a general password. So at least for System.Data.SQLite I need to enter the password as a parameter. Meaning, it is not user/pass of a db user, but the overall db password.

[utelle]

Unfortunately, I don't know anything about the internals of HeidiSQL. However, it is necessary to specify the cipher scheme and the password used for encrypting the database.

I suspect that HeidiSQL asks only for credentials for the user authentication (which is a deprecated extension in SQLite).

Try to specify cipher scheme and password via URI parameters in the database file name:

file:db01.sqlite?cipher=rc4&key=passphrase

rc4 is the cipher scheme used by System.Data.SQLite.

Good luck.

[ansgarbecker]

@utelle thanks for your quick input!

HeidiSQL's user/password boxes are currently disabled for SQLite. I did not even knew there was a deprecated extension supporting these. So my plan was to enable these, rename the "Username" label into "Cipher name", and let the password input handle the phrase.

Reading your docs, I would add the following to the current connection logic:

• after calling sqlite3_open(...), call sqlite3mc_cipher_index('whatever-user-typed-in-cipher-field') to get the cipher index for let's say 'rc4'
• given that cipher index, call sqlite3mc_config(FHandle, 'cipher', CipherIndex)
• finally, pass the user key/passwphrase: sqlite3_key(FHandle, Pointer(RawPassword), Length(RawPassword))

Does that sound correct, or am I missing something important?

Of course I would add some error handling. But especially the sqlite3_key is documented as "will never fail", and I will have to fire some dummy query to check its result.

[utelle]

HeidiSQL's user/password boxes are currently disabled for SQLite. I did not even knew there was a deprecated extension supporting these.

The SQLite developer team added an extension User Authentication to SQLite. With this extension enabled database access requires user authentication before access is granted. This extension was formally deprecated in January 2024.

The extension is still included in the SQLite release distribution. It is also in the release distribution of SQLite3 Multiple Ciphers, but it is now disabled by default.

So my plan was to enable these, rename the "Username" label into "Cipher name", and let the password input handle the phrase.

I would recommend to add separate input field(s) instead of diverting the existing fields from their intended use.

For example, SQLiteStudio lets the user select one of the supported ciphers and enter a passphrase. Additionally, the user can enter a list of SQL PRAGMA statements to configure the cipher scheme.

The steps to apply the cipher configuration is

1. Set the required cipher scheme, for example with PRAGMA cipher=<cipher-name>
2. Execute the user supplied cipher configuration pragmas
3. Set the key material by executing PRAGMA key='<passphrase>

These steps have to be executed before the database is actually accessed.

Reading your docs, I would add the following to the current connection logic:

• after calling sqlite3_open(...), call sqlite3mc_cipher_index('whatever-user-typed-in-cipher-field') to get the cipher index for let's say 'rc4'
• given that cipher index, call sqlite3mc_config(FHandle, 'cipher', CipherIndex)
• finally, pass the user key/passwphrase: sqlite3_key(FHandle, Pointer(RawPassword), Length(RawPassword))

Does that sound correct, or am I missing something important?

The cipher configuration step is missing. This step is not always required, but if a user wants for example access a database that was encrypted with the original SQLCipher library it will be necessary to select the correct legacy mode. For the latest SQLCipher version 4 the following pragma has to be executed:

PRAGMA legacy=4;

or alternatively a call of function sqlite3mc_config_cipher.

I would recommend to use SQL pragma statements instead of the C API, because it will keep HeidiSQL independent of the SQLite3MC DLL. That is, a user could still use an official SQLite DLL, if encryption is not required.

Of course I would add some error handling. But especially the sqlite3_key is documented as "will never fail", and I will have to fire some dummy query to check its result.

Setting the encryption key does not verify whether the provided key is actually valid. If the key was not correct, trying to access the database content will result in a not a database file error.

A tool like HeidiSQL will read the database schema. This read will fail, if the wrong passphrase was provided for an encrypted database.

[a82kd]

Unfortunately, I don't know anything about the internals of HeidiSQL. However, it is necessary to specify the cipher scheme and the password used for encrypting the database.

I suspect that HeidiSQL asks only for credentials for the user authentication (which is a deprecated extension in SQLite).

Try to specify cipher scheme and password via URI parameters in the database file name:

file:db01.sqlite?cipher=rc4&key=passphrase

rc4 is the cipher scheme used by System.Data.SQLite.

Good luck.

Tried it, but I get an error: "SQL Error (7): out of memory" I have some doubts that this is really the right error message.

[ansgarbecker]

I will provide a new network type "SQLite (encrypted)" in the upcoming builds. Using that, it's easier for the user to set the right library, and to configure the cipher.

[utelle]

Tried it, but I get an error: "SQL Error (7): out of memory" I have some doubts that this is really the right error message.

Unfortunately, I have no clue what went wrong for you.

All I can say is that I tested it for myself a moment ago. I used the most recent portable Heidi version 12.7 which I extracted to my hard disk. Then I copied the file sqlite3mc_x64.dll from the latest SQLite3MC release into the Heidi directory. Thereafter I established a connection to an existing small test database using the settings I had described:

I opened the database and was able to query it:

That is, in principle it works.

Trying to use the database file without cipher configuration results in the SQL error (26) file is not a database:

[ansgarbecker]

@a82kd and @michael-ts please test the new build with an encrypted SQLite file. I have integrated the C API from @utelle , with a new "SQLite (encrypted)" network type. New things you need to set up can be seen in the screenshot:

If you want to test yourself, you will need to download the installer from the nightly builds section, not just the build file. Otherwise you will miss the required dll file:

[a82kd]

Yes! That works fine. Thank you very much!!! (tested with RC4)

[MrNemo64]

I'm trying the nightly build out. I need to check the contents of a file created using rusqlite with the bundled-sqlcipher-vendored-openssl feature, so I'm basically using SQLCipher. To my understanding, the hmac seems to default to sha512. I've tryed every cipher that appears on the list and with every one of them I get the same error SQL Error (26): file is not a database and You have activated encryption on a probably non-encrypted database. I've also tryed opening it without a password, with a SQLite configuration, and I get the same error. The database has a test table with a name text column and two rows with values one and two. The key is juan and the pragma legacy is set to 4 How can I get Heidi to open the file? test.zip

[utelle]

I'm trying the nightly build out. I need to check the contents of a file created using rusqlite with the bundled-sqlcipher-vendored-openssl feature, so I'm basically using SQLCipher. To my understanding, the hmac seems to default to sha512. I've tryed every cipher that appears on the list and with every one of them I get the same error SQL Error (26): file is not a database and You have activated encryption on a probably non-encrypted database. I've also tryed opening it without a password, with a SQLite configuration, and I get the same error. The database has a test table with a name text column and two rows with values one and two. The key is juan and the pragma legacy is set to 4 How can I get Heidi to open the file? test.zip

I downloaded your test database and I was able to open it in HeidiSQL with the given settings (cipher: sqlcipher; key: juan, encryption parameters: legacy=4) without any problems.

I used the nightly build installer HeidiSQL_12.7.0.6868_Setup.exe.

[MrNemo64]

I'm trying the nightly build out. I need to check the contents of a file created using rusqlite with the bundled-sqlcipher-vendored-openssl feature, so I'm basically using SQLCipher. To my understanding, the hmac seems to default to sha512. I've tryed every cipher that appears on the list and with every one of them I get the same error SQL Error (26): file is not a database and You have activated encryption on a probably non-encrypted database. I've also tryed opening it without a password, with a SQLite configuration, and I get the same error. The database has a test table with a name text column and two rows with values one and two. The key is juan and the pragma legacy is set to 4 How can I get Heidi to open the file? test.zip

I downloaded your test database and I was able to open it in HeidiSQL with the given settings (cipher: sqlcipher; key: juan, encryption parameters: legacy=4) without any problems.

I used the nightly build installer HeidiSQL_12.7.0.6868_Setup.exe.

I was missing the legacy=4 part so my bad

[ansgarbecker]

Thanks to all for your feedback!

[utelle]

During the development of the upcoming Python3 wrapper APSW-sqlite3mc several issues were found and fixed in SQLite3 Multiple Ciphers. Please consider to upgrade the SQLite3 Multiple Ciphers DLL bundled with the nightly builds of HeidiSQL to the latest version 1.8.6.

[ansgarbecker]

Thanks for the hint. I just updated them for the coming builds and releases. And I'll keep an eye on your releases.