Encrypt/decrypt database with wsSQlite /Lazarus

[kjteng]

I asked a similar question two years ago but still cannot get a solution. Since then wxSqlite has changed in its encryption routine to cater for SQLite version 3.32.0. I would like to ask the same question again and hope that there will be a solution to the above.

My problem:-
Using zeoslib 7.1.14 with the new version of wxsqlite.dll, I am able to access an encrypted sqlite database as follows:
with zconnection1 do
begin
Database := 'Sample.db';
Connect;
ExecuteDirect('Pragma cipher="aes256cbc" '); //cpher method
ExecuteDirect('Pragma key="123123" '); //password is 123123
end;
However, with zeoslib 7.2.14 after, the above code will raise an error when 'connect' is called.
Any help?

[marsupilami]

Hello,

what kind of help do you expect? In Zeos 7.2 we implemented correct transaction support for SQLite. Most probably the start of the transaction support generated the errors you see because the database isn't decrypted (yet).

In all other aspects my answers from the other thread are still true: viewtopic.php?t=105825#p136985

It is possible to implement encryption support for wxsqlite but we will need somebody to do that. If you want to work on that job, I will behappy to support you. But currently I don't have the time to do the implementation myself.

Best regards,

Jan

[kjteng]

what kind of help do you expect?

I just want to know how to change my codes so that my program (using zeoslib 7.2.x) can read the encrypted database. Otherwise I will be stuck with the old version (v7.1.4).

In Zeos 7.2.14 we implemented correct transaction support for SQLite. Most probably the start of the transaction support generated the errors you see because the database isn't decrypted (yet).

Can you elaborate on this point? How do I run the following code before the transaction starts :
zConnection1.ExecuteDirect('Pragma cipher="aes256cbc"');
zConnection1.ExecuteDirect('Pragma key="123123"');

(using zeoslib 7.14, running the above two pragmas after "zConnection1.connect" will works.)

In all other aspects my answers from the other thread are still true: viewtopic.php?t=105825#p136985

There is a new implementation of encryption extension (SQLite3 Multiple Ciphers) since August 2020. In this new implementation, we do not call wxsqlite3_config and wxsqlite3_config_cipher but instead just run the pragmas as shown above.
Can you take a look at
https://utelle.github.io/SQLite3MultipleCiphers/
https://github.com/utelle/SQLite3MultipleCiphers
Would this makes any difference?

It is possible to implement encryption support for wxsqlite but we will need somebody to do that. If you want to work on that job, I will behappy to support you. But currently I don't have the time to do the implementation myself.

Unfortunately I am just an 'end user' of zeoslib and do not have sufficient skills and knowledge to change the zeoslib component :-(

[marsupilami]

what kind of help do you expect?

I just want to know how to change my codes so that my program (using zeoslib 7.2.x) can read the encrypted database. Otherwise I will be stuck with the old version (v7.1.4).

Well - I am pretty sure, it currently isn't possible with Zeos 7.2 and Zeos 8.0.

In Zeos 7.2.14 we implemented correct transaction support for SQLite. Most probably the start of the transaction support generated the errors you see because the database isn't decrypted (yet).

Can you elaborate on this point? How do I run the following code before the transaction starts :
zConnection1.ExecuteDirect('Pragma cipher="aes256cbc"');
zConnection1.ExecuteDirect('Pragma key="123123"');

As far as I know it isn't currently possible with Zeos 7.2 / Zeos 8.0. Zeos has to be extended to support encryption with wxSqlite. In an ideal world I would expect things to work similar to this:

• Detect wether we use wxSqlite. This is where wxsqlite3_config and wxsqlite3_config_cipher come in handy. They are only found in wxSqlite libraries.
• Check if we have a password and maybe a cipher suite
• Execute the pragmas before starting

The detection of wxSqlite is necessary because Zeos already contains official support for the regular Sqlite way to start encryption.

Or we would need some way to specify sqls / pragmas / whatever that should be run directly after the connection is initiated. This way Zeos doesn't need to kow if it uses wxSqlite and it is all up to the programmer. Hmm...

(using zeoslib 7.14, running the above two pragmas after "zConnection1.connect" will works.)

Yes - I assume that this is true because we don't do anything after the initial connection in that version. This gives you a chance to execute pragmas before we do anything else. In newer versions we do some things after connecting and one of these things probably raises the error.

In all other aspects my answers from the other thread are still true: viewtopic.php?t=105825#p136985

There is a new implementation of encryption extension (SQLite3 Multiple Ciphers) since August 2020. In this new implementation, we do not call wxsqlite3_config and wxsqlite3_config_cipher but instead just run the pragmas as shown above.

As explained above - we would need some kind of extension to do that. Have a look at TZSQLiteConnection.Open in ZDbcSqlite.pas.

Can you take a look at
https://utelle.github.io/SQLite3MultipleCiphers/
https://github.com/utelle/SQLite3MultipleCiphers
Would this makes any difference?

Yes - it does:

Basically, the C API of the official SQLite Encryption Extension (SEE) is supported.

Zeos supports this API for doing encryption. The original SEE documentation has a way to specify the cipher suite as a part of the encryption key. If SQLite3 Multiple Ciphers implements this too, you can choose the encryption algorithm. With SEE your code in Zeos might look like this:

Code: Select all

  ZConnection.Properties.Add('encrypted=true');
  ZConnection.Password := 'aes256:mykey'; // choose AES265 with the key "mykey"
  ZConnection.Connect;

This only works if the sqlite3_key function is present in the client library. Otherwise it will be ignored. The example for the key is taken from the original SQLite SEE Documentation. So your mileage might wary with SQLite3 Multiple Ciphers. For the parts where SQLite3 Multiple Ciphers works identical to the SQLite SEE, you can use them as a drop in replacement and they should work with Zeos too.

It is possible to implement encryption support for wxsqlite but we will need somebody to do that. If you want to work on that job, I will behappy to support you. But currently I don't have the time to do the implementation myself.

Unfortunately I am just an 'end user' of zeoslib and do not have sufficient skills and knowledge to change the zeoslib component :-(

I was too at one time. And for many parts of Zeos I still am a user because I don't know them intimately. But honestly this is quite a nice project to get started because the changes to the code need to be done in some locations only and you don't need to worry how the rest of the code works.

Best regards,

Jan

[kjteng]

Thank you for your patience and detailed answer.
I have just done a quick and dirty test by inserting pragma statements into TZSQLiteConnection.Open procedure as follows:-

Code: Select all

procedure TZSQLiteConnection.Open;
...
begin
  if not Closed then
    Exit;
  ...
  ...
  Stmt := TZSQLiteStatement.Create(GetPlainDriver, Self, Info, FHandle);
  
 // two lines added for testing purpose 
  Stmt.ExecuteUpdate('PRAGMA cipher=''aes256cbc''');
  Stmt.ExecuteUpdate('PRAGMA key=''password''');
  
   ...
 end;
   

It works !
Certainly this is a good start and I know I have to put in more effort to perfect it. Kindly advise if There is anything that I need to watch out when modifying the codes in zDbcsqlite.pas
Many many thanks for your help.

[marsupilami]

Hello kjteng,

Thank you for your patience and detailed answer.
I have just done a quick and dirty test by inserting pragma statements into TZSQLiteConnection.Open procedure as follows:-

Code: Select all

procedure TZSQLiteConnection.Open;
...
begin
  if not Closed then
    Exit;
  ...
  ...
  Stmt := TZSQLiteStatement.Create(GetPlainDriver, Self, Info, FHandle);
  
 // two lines added for testing purpose 
  Stmt.ExecuteUpdate('PRAGMA cipher=''aes256cbc''');
  Stmt.ExecuteUpdate('PRAGMA key=''password''');
  
   ...
 end;
   

It works !
Certainly this is a good start and I know I have to put in more effort to perfect it. Kindly advise if There is anything that I need to watch out when modifying the codes in zDbcsqlite.pas

Currently there is not much advice to give. Maybe it makes sense to have a github clone of Zeos so it is possible to see what things you change?

One thing I noticed though: Stmt should not be of the type TZSQLiteStatement. The DBC layer uses interfaces as much as possible to get memory management done correctly. So maybe your example should look like this:

Code: Select all

procedure TZSQLiteConnection.Open;
...
begin
  if not Closed then
    Exit;
  ...
  ...
  Stmt := TZSQLiteStatement.Create(GetPlainDriver, Self, Info, FHandle) as IZStatement; // <-- Note the IZStatement interface here
  
 // two lines added for testing purpose 
  Stmt.ExecuteUpdate('PRAGMA cipher=''aes256cbc''');
  Stmt.ExecuteUpdate('PRAGMA key=''password''');
  
   ...
 end;

Another option might be to use ExecuteImmediat, like we do for the other PRAGMAs:

Code: Select all

procedure TZSQLiteConnection.Open;
...
begin
  if not Closed then
    Exit;
  ...

 // two lines added for testing purpose 
  ExecuteImmediat('PRAGMA cipher=''aes256cbc''', lcExecute); 
  ExecuteImmediat('PRAGMA key=''password''', lcExecute); 
  
   ...
 end;

ExecuteImmediat takes care of all the necessary handlings of statements, so you don't have to worry about the interfaces and things like that.

Best regards,

Jan

[kjteng]

Hello marsupilami,

I have problem to compile the following statements. Is there any typo error?
ExecuteImmediat('PRAGMA cipher=''aes256cbc''', lcExecute);
ExecuteImmediat('PRAGMA key=''password''', lcExecute);

[kjteng]

... ​Maybe it makes sense to have a github clone of Zeos so it is possible to see what things you change?

How do I make a clone in github? Sorry I may have asked a stupid question because I am not familiar with this
feature of github.

[marsupilami]

Hello kjteng,

I have problem to compile the following statements. Is there any typo error?
ExecuteImmediat('PRAGMA cipher=''aes256cbc''', lcExecute);
ExecuteImmediat('PRAGMA key=''password''', lcExecute);

There is no typo there. ExecuteImmediat only exists in Zeos 8.0. If you modify Zeos 7.2 you have to stick to creating an IZStatement interface. Sorry for misleading you there.

How do I make a clone in github? Sorry I may have asked a stupid question because I am not familiar with this
feature of github.

I meant for you to create a fork of Zeos on Github. There is a copy of Zeos on https://github.com/marsupilami79/zeoslib. If you are more used to Subversion you could create a patch file from time to time and attach it to a post on the forum.

Best regards,

Jan

[kjteng]

I have modified TZSQLiteConnection.Open as follows :-

Code: Select all

i.   Execute the following pragma only if the properties.values['Cipher'] is not blank.
     ( Cipher supported by wxSqlite: chacha20, aes256cbc, aes128cbc, sqlcipher, rc4 )
ii.  Do not set any value for properties.values['Cipher'] if we are not using wxsqlite3mc
iii. If password = '', wxSqlite will take the database as unencrypted
}

  Stmt := TZSQLiteStatement.Create(GetPlainDriver, Self, Info, FHandle) as IZStatement; // <-- Note the IZStatement interface here
  S := Info.Values['Cipher'];
  if S > '' then
    begin
      Stmt.ExecuteUpdate('PRAGMA cipher=' +QuotedStr(S));
      Stmt.ExecuteUpdate('PRAGMA key='+ QuotedStr(password));
    end;    

Now the question is: should I make the following change (to skip those lines if info.Value['Cipher']>'' ) :-

Code: Select all

{ Turn on encryption if requested }
  if (info.Value['Cipher']= '' )  //not using wssqlite3mc
     and StrToBoolEx(Info.Values['encrypted']) and (Password <> '') then
  begin
    SQL := {$IFDEF UNICODE}UTF8String{$ENDIF}(Password);
    CheckSQLiteError(FPlainDriver, FHandle,
      GetPlainDriver.Key(FHandle, Pointer(SQL), Length(SQL)),
      lcConnect, 'SQLite.Key', ConSettings, FExtendedErrorMessage);
  end;     

[kjteng]

My patch file for ZDBCSqlite.pas is available at github now:
https://github.com/kjteng/zeoslib/tree/main/ZDbcSqLite

[marsupilami]

Hello kjteng,

My patch file for ZDBCSqlite.pas is available at github now:
https://github.com/kjteng/zeoslib/tree/main/ZDbcSqLite

Thank you for making this file available

Now the question is: should I make the following change (to skip those lines if info.Value['Cipher']>'' ) :-

I think we need some way to tell the SQLite driver to either use the wxSqlite way of doing things - like using the pragmas or to use the SQLite way of things - like using the SEE API.
So yes - I think your code should be something like

Code: Select all

  if (info.Value['Cipher'] <> ) then begin
    // initialize wxSQLite encryption here
  end else begin
    // have the original code that uses SSE and the encryption parameter here
  end;

Best regards,

Jan

[kjteng]

OK. I have made further changes to cater for SEE Sqlite. User for SEE Squlite should leave the Info.value['Cipher'] empty and include the cipher type as part of the key e.g. aes256:mykey, rc4:mykey etc to comply with SEE format.
My problem now: SEE extension of Sqlite is not public dormain (i.e. need to pay for the license) so I am unable to test it. Anybody can help me to test?

Code: Select all

  S := Info.Values['Cipher'];
  if S > '' then  // use wxSqlite3mc if Cipher is specified
    begin                                                  
      ExecuteImmediat('PRAGMA cipher=' +QuotedStr(S), lcExecute);
      ExecuteImmediat('PRAGMA key='+ QuotedStr(password), lcExecute);
    end
  else
    if Password > ''  then
      ExecuteImmediat('PRAGMA key='+ QuotedStr(password), lcExecute)
    else
      begin
        { Set busy timeout if requested }
        TmpInt := StrToIntDef(Info.Values[ConnProps_BusyTimeout], -1);
        if TmpInt >= 0 then
          FPlainDriver.sqlite3_busy_timeout(FHandle, TmpInt);
        FUndefinedVarcharAsStringLength := StrToIntDef(Info.Values[DSProps_UndefVarcharAsStringLength], 0);
        FSQLiteIntAffinity := StrToBoolEx(Info.Values[DSProps_SQLiteIntAffinity], false);
      end;

[miab3]

Hi kjteng,

I'm not well versed but, have you paid attention to this design?:

https://github.com/utelle/SQLite3Multip ... s/releases
https://github.com/utelle/SQLite3MultipleCiphers
https://utelle.github.io/SQLite3MultipleCiphers/

Michał

[marsupilami]

OK. I have made further changes to cater for SEE Sqlite. User for SEE Squlite should leave the Info.value['Cipher'] empty and include the cipher type as part of the key e.g. aes256:mykey, rc4:mykey etc to comply with SEE format.
My problem now: SEE extension of Sqlite is not public dormain (i.e. need to pay for the license) so I am unable to test it. Anybody can help me to test?

Nice Unfortunately I don't have a license for SEE. So all we can do for now is to hope that we get everything right.

Some questions though:
- Is this change part of your file on github?
- Any chance for you to clone Zeos on Github and make the changes there?

Best regards,

Jan