wxsqlite3.4.2.0 how to open old encryted database Topic is solved

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
kjteng
In need of some credit
In need of some credit
Posts: 5
Joined: Wed Oct 31, 2018 1:57 am

wxsqlite3.4.2.0 how to open old encryted database

Post by kjteng »

I have some old database (encryted with AES256 using wxsqlite3.3.5.0.dll) which I want to access after switching to wxsqlite3.4.2.0.

According to the documentation, I need to run one of the following (depend on the encryption method used) :
SELECT wxsqlite3_config("cipher", "aes256cbc");
SELECT wxsqlite3_config("aes256cbc", "legacy", 0);
SELECT wxsqlite3_config("aes256cbc", "legacy", 1);
SELECT wxsqlite3_config("aes256cbc", "legacy_page_size", 1024);
SELECT wxsqlite3_config("aes256cbc", "legacy_page_size", 4096);
SELECT wxsqlite3_config("aes256cbc", "kdf_iter", 4001);

I tried each and every one of the above (with freepascal and zeolib component)
zconnection.ExecDirect( 'SELECT wxsqlite3_config("cipher", "aes256cbc");') etc but no success.
Error messge:
error3.png
error3.png (23.72 KiB) Viewed 16615 times
My code:

Code: Select all


procedure TForm1.bt1OpenTableClick(Sender: TObject);
begin
  ZConnection1.Disconnect;
  ZConnection1.Database := FileNameEdit1.FileName;
  ZConnection1.Password :=  tx1Pass.Text;
  if tx1Pass.Text > '' then
    ZConnection1.Properties.Values['encrypted']:= 'True'
  else
    ZConnection1.Properties.Values['encrypted']:= 'False' ;
  ZConnection1.Connect;

  ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("cipher", "aes256cbc");');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "legacy", 0);');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "legacy", 1);');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "legacy_page_size", 1024);');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "legacy_page_size", 4096);');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "kdf_iter", 4001);');
  ZQuery1.Open;
end;
Have I missed out something?
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: wxsqlite3.4.2.0 how to open old encryted database

Post by utelle »

kjteng wrote:I have some old database (encryted with AES256 using wxsqlite3.3.5.0.dll) which I want to access after switching to wxsqlite3.4.2.0.

According to the documentation, I need to run one of the following (depend on the encryption method used) :
SELECT wxsqlite3_config("cipher", "aes256cbc");
SELECT wxsqlite3_config("aes256cbc", "legacy", 0);
SELECT wxsqlite3_config("aes256cbc", "legacy", 1);
SELECT wxsqlite3_config("aes256cbc", "legacy_page_size", 1024);
SELECT wxsqlite3_config("aes256cbc", "legacy_page_size", 4096);
SELECT wxsqlite3_config("aes256cbc", "kdf_iter", 4001);
If your database was encrypted with the original wxSQLite3 3.5.0 version for AES256, then just selecting the cipher should work, because the default parameters were not changed since then. That is, issuing

Code: Select all

  SELECT wxsqlite3_config("cipher", "aes256cbc"); 
  PRAGMA key='your passphrase';
after opening the database should allow you to access your data.

You should verify that using the SQLite3 shell binary coming with the latest wxSQLite3 release (file wxsqlite3-sqlite3-multicipher.zip).
kjteng wrote:I tried each and every one of the above (with freepascal and zeolib component)
zconnection.ExecDirect( 'SELECT wxsqlite3_config("cipher", "aes256cbc");') etc but no success.

My code:

Code: Select all


procedure TForm1.bt1OpenTableClick(Sender: TObject);
begin
  ZConnection1.Disconnect;
  ZConnection1.Database := FileNameEdit1.FileName;
  ZConnection1.Password :=  tx1Pass.Text;
  if tx1Pass.Text > '' then
    ZConnection1.Properties.Values['encrypted']:= 'True'
  else
    ZConnection1.Properties.Values['encrypted']:= 'False' ;
  ZConnection1.Connect;

  ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("cipher", "aes256cbc");');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "legacy", 0);');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "legacy", 1);');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "legacy_page_size", 1024);');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "legacy_page_size", 4096);');
  // ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("aes256cbc", "kdf_iter", 4001);');
  ZQuery1.Open;
end;
Have I missed out something?
Looking at the code I get the impression that method Connect of the database connection ZConnection1 will already call the function sqlite3_key internally to set up the encryption key. Therefore the calls to method ExecuteDirect to adjust the encryption parameters come too late, because method Connect already established the encrypted database connection using the defaults (ChaCha20).

I don't know whether the implementation of method Connect performs any other database actions before returning to the application. In principle, the following code should work:

Code: Select all

procedure TForm1.bt1OpenTableClick(Sender: TObject);
var
  keyPragma : String;
begin
  ZConnection1.Disconnect;
  ZConnection1.Database := FileNameEdit1.FileName;
  ZConnection1.Properties.Values['encrypted']:= 'False' ;
  ZConnection1.Connect;

  ZConnection1.ExecuteDirect('SELECT wxsqlite3_config("cipher", "aes256cbc");');
  keyPragma := "PRAGMA key=" + QuotedStr(tx1Pass.Text) + ";";
  ZConnection1.ExecuteDirect(keyPragma);
  // Access the database as usual
  // ...
end;
It would be certainly best if the connection object would be able to pass cipher configuration parameters dircetly to the database object, but currently this doesn't seem to be possible.

I consider to implement alternatives for passing cipher configuration parameters to the database (like a key prefix or parameters that can be added to the database file URI). However, I have not yet decided which route to take, and therefore the above workaround will have to be used for now. Alternatively one could generate a SQLite DLL that supports the wanted cipher as the default selection.

Regards,

Ulrich
kjteng
In need of some credit
In need of some credit
Posts: 5
Joined: Wed Oct 31, 2018 1:57 am

Re: wxsqlite3.4.2.0 how to open old encryted database

Post by kjteng »

Oh yes! "PRAGMA key=" + QuotedStr(tx1Pass.Text) + ";";
Problem solved !
Also thanks for the detailed explanation.
Thank you very much.
Post Reply