SQLlite Encryption problem

[kjteng]

I just downloaded the new version of wxsqlite3.dll (ver 4.2.0) and have problem opening database previously created (with version3.5.8 encrypted with 128bit AES method).  I think this is because the new dll default encryption method is chacha20. 
My question: how can I change or set the encryption method to AES 128 so that I can read my old database?
 

[Thaddy]

provided the dll is compiled with all supported ciphers:
You can use a PRAGMA for that.
See https://www.sqlite.org/see/doc/trunk/www/readme.wiki
Make sure you read the part here:

When using PRAGMA hexkey or PRAGMA hexrekey, the key prefix must be hex encoded just like the rest of the key.

PRAGMA hexkey='aes128:6d796b6579';         -- Wrong!!
PRAGMA hexkey='6165733132383a6d796b6579';  -- correct

What worries me a little is that chacha20 is according to that official documentation not supported by the official Sqlite SEE.
If the wxsqlite3.dll has its own format it may also have a different implementation of AES.
Then it may or may not work: it looks like it is not compiled from an official source.

OTOH it is claimed here http://wxcode.sourceforge.net/components/wxsqlite3/ and here https://github.com/utelle/wxsqlite3 that the encryption is compatible, so the PRAGMA should work too?

[kjteng]

Thank you for the reply.
After some testings, I think the dll that I downloaded only supported chacha20. (Even for the earlier version I downloaded there are two separate version of dll: one for aes128 and another one for aes256).

Since I do not know how to compile the dll from the c++ source, I think the only way is to decrypt the existing database (using the old dll) and then encrypt it with the new dll ;-(

Please advise if there is alternative way to deal with this. Thanks again.

[Thaddy]

better ask on the wxsqlite forum or issue tracker

[utelle]

After some testings, I think the dll that I downloaded only supported chacha20.

From where did you download the DLL? The DLLs available from https://github.com/utelle/wxsqlite3/releases/latest definitely support multiple ciphers (wxSQLite3 AES 128 Bit, wxSQLite3 AES 256 Bit, ChaCha20, and SQLCipher).

(Even for the earlier version I downloaded there are two separate version of dll: one for aes128 and another one for aes256).

Since wxSQLite3 version 4.0.0 there is only a single DLL which allows to select the cipher at runtime. The default cipher was changed from AES 128 Bit to ChaCha20, because the latter is more secure and incurs less runtime overhead. However, the DLL could be built using a different default cipher if necessary.

Since I do not know how to compile the dll from the c++ source, I think the only way is to decrypt the existing database (using the old dll) and then encrypt it with the new dll ;-(

Please advise if there is alternative way to deal with this.

Since wxSQLite3 4.x supports the ciphers AES 128 Bit and AES 256 Bit of the pre-4.x versions of wxSQLite3, it is not necessary to decrypt and reencrypt your databases. However, you will have to adjust the cipher parameters, before issuing "PRAGMA key='...';

A description, how the cipher parameters can be adjusted can be found here: https://github.com/utelle/wxsqlite3/tree/master/sqlite3secure.

Regards,

Ulrich

P.S.: The ciphers of the official SQLite3 Encryption Extension (https://www.sqlite.org/see) are not supported by wxSQLite3. The reason is that SEE is a commercial extension with closed source.

[kjteng]

Many thanks your detailed explanations  (in this forum as well as https://forums.wxwidgets.or) 
I think I can replace the old version with new one now.
For the benefit of other members who may be interested, I have summarised below what I have learned for wxsqlite3.4.2.0 (using lazarus with zeolib db component):-

1. before connecting to the database -
     i) set the encrypted = 'True' (if database is encrypted, otherwise set this to '')
        or  set ZConnection1.Properties.Values['encrypted'] := 'True' // or 'False' as  the cas may be
     ii) set ZConnection1.Password
     iii) call ZConnection1.Connect

2. If the database was not encryted, you should be able to operate it as usual now.
    If the database was encryted, you must activate the encryption method by calling -
         select wxsqlite3_config(cipherName, cipherParameter, newValue);
   For the values of cipherName, cipherParameter, newValue, please refer to the files in
    ...\sqlite3secure\test\folder
   For example, if the database was encrypted in with AES256, check the file setaes256wx.sql
   and then call
      ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("cipher", "aes256cbc");');
      ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "legacy", 0);');   
       ....
3. Finally, call pragma key="password" eg -
     ZConnection1.ExecuteDirect('PRAGMA key="12345"' ); // say, 12345 is the password
   With this, I can operate the database tables as usual now

Note:
 Sstep 2 & 3 is not necessary if the database is encryted with default method i.e. chacha20.