problem with wxsqlite (zeos7.2.6-stable)

[kjteng]

I need help!
I am using wxsqlite.dll (with encryption) with zeos ver 7.14 and has no problem with the components so far until I
upgrade to zeos ver7.2.6 today. With the new version, I cannot connect to encrypted sqlite databases which any more.
Error msg: file is not a database (see attached image for more details).
Any clue? or should I revert to old version first?

Code: Select all

  with Zdb1 do
    begin
      Disconnect;
      Database :=  ed1DB.Text;
      if Database > '' then
      begin
showmessage('0');
     Connected := True;  //error occurs after this 
showmessage('1');
        ExecuteDirect('SELECT wxsqlite3_config(''cipher'','+ QuotedStr('chacha20') + ');');
        ExecuteDirect('PRAGMA key=' + Quotedstr(ed2Password.Text) + ';') ;
       end;
    end;   
           

[marsupilami]

I am sorry, but to me it seems encryption cannot be made to work with the current version of Zeos 7.2 I created a ticket on Sourceforge: https://sourceforge.net/p/zeoslib/tickets/387/
For further progress on this topic please look there.

[kjteng]

Hi, I noticed that there are patches available lately. However I am unable to get it via svn.code.sf.net/p/zeoslib/code-0/trunk zeoslib-code-0.
May I know where can I get the latest update or should I just download the snapshot ?
Sorry for my ignorance.

[marsupilami]

Hello,

you can downlad the current snapshot from our Subversion page on SourceForge. There is a "Download Snapshot" button on the top right corner.

Please try the following:

Code: Select all

  with Zdb1 do
    begin
      Disconnect;
      Database :=  ed1DB.Text;
      Properties.Add('encrypted=true');
      Password := ed2Password.Text;
      if Database > '' then
      begin
showmessage('0');
     Connected := True;  //error occurs after this
showmessage('1');
       end;
    end;

I am not sure how to select the cipher suite though.

Best regards,

Jan

[kjteng]

more detail

I have tried the following code with r6147. Still get error to connect with a sqlite database (encryted with aes256).
The same code compiled and run perfectly with zcomponent v7.14 :-

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var qry: string;
begin
  ZConn.Disconnect;
  ZConn.LibraryLocation:= 'C:\tmp\sqlite3_wx.dll';
  ZConn.Database :='C:\tmp\Chinook_aes256_12345.DB';
  ZConn.Properties.Values['encrypted'] := 'True';  //this line can be removed, wxsqlite doesnt use this property
  ZConn.Password := '12345'; //this line can be removed, wxsqlite doesnt use this property
  showmessage('1');
  ZConn.Connect;
  showmessage('2');
  if ZConn.Connected then
    begin
      qry := 'SELECT wxsqlite3_config(''cipher'', ''aes256cbc'');' ;  
      ZConn.ExecuteDirect(qry); // choose cipher method
      qry :=  'PRAGMA key=''12345'';';
      ZConn.ExecuteDirect(qry); // decrypt with password above
      ZConn.GetTableNames('', ListBox1.Items);
    end;
end;  

error.png

Perhaps the following information may be useful for you guys:
1. The new version of wxsqlite does not use the password and the 'encrypted=True' properties.
2. We select the cipher via 'SELECT wxsqlite3_config(''cipher'', ''aes256cbc'');' statement;
3. We set the password and decrpyt the database by running 'PRAGMA key=' <password''. This is works slightly different from the System.Data.SQLite version of dll.

[marsupilami]

Hello,

Zeos is prepared to use the functions sqlite3_key_v3 and sqlite_rekey_v2 as documented in the readme about the SQLite Encryption Extension. Everything else is currently unsupported. Your project most probably broke because we changed transaction support and foreign key support for SQLite in Zeos 7.2.

If we want to support wxSqlite, we need:

• a way to distinguish between SQLite and wxSQLite
• some good documentation on how to use wxSQLite

After some digging I found that wxSQLite exports some functions specific to wxSQLite. See https://github.com/utelle/wxsqlite3/issues/46:

• wxsqlite3_config
• wxsqlite3_config_cipher

We could use them to distinguish between SQLite and wxSQLite. But we also need some documentation on how to use them. In the end, the interface for using wxSQLite and SQLite should be the same. Zeos should hide implementation details.

Jan

[marsupilami]

addendum - this is documentation about the wxSQLite encryption support:
https://github.com/utelle/wxsqlite3/tre ... ite3secure