Using chacha20 and SQL logic error

[regs01]

When i'm trying to encrypt existing database using aes128cbc, aes256cbc and rc4 it goes smooth. But when trying to use chacha20 and sqlcipher i'm getting SQL logic error on PRAGMA rekey="key".

It going this way. Am i missing something about chacha20 cipher?

PRAGMA cipher="%s"
PRAGMA rekey="%s"

[utelle]

Unfortunately, the information you are giving is a bit unspecific. Which version of SQLite3 Multiple Ciphers are you using? Which are the exact steps to reproduce the problem?

When i'm trying to encrypt existing database using aes128cbc, aes256cbc and rc4 it goes smooth.

Is the existing database a plain unencrypted database or an encrypted database? Are you using the SQLite3mc shell application to perform the encryption process? If not, which application are you using? Are you able to reproduce the problem using the SQLite3mc shell application?

But when trying to use chacha20 and sqlcipher i'm getting SQL logic error on PRAGMA rekey="key".

It going this way. Am i missing something about chacha20 cipher?

PRAGMA cipher="%s"
PRAGMA rekey="%s"

I assume the format string "%s" is replaced by actual values. Have you verified that the resulting SQL command strings are correct?

In principle, the steps to encrypt a database with a certain cipher scheme and a certain passphrase are correct. Executing the following SQL pragmas will encrypt the database with cipher scheme chacha20 and passphrase passphrase:

PRAGMA cipher='chacha20';
PRAGMA rekey='passphrase';

I verified this using the SQLite3mc shell application. It worked flawlessly for unencrypted as well as encrypted existing database files.

Can you please show the exact sequence of SQL statements you used?

[regs01]

It seem to be indeed working via SQLite3mc shell.

I assume the format string "%s" is replaced by actual values. Have you verified that the resulting SQL command strings are correct?

It is indeed replaced with values, which turns into

PRAGMA cipher="chacha20";
PRAGMA rekey="passphrase";

or

PRAGMA cipher="aes256cbc";
PRAGMA rekey="passphrase";

etc

This is Pascal. I'm trying both Free Pascal with SQLdb and Delphi with Zeos 8 + ZDbcSqLite. I didn't look into Zeos realization, but i guess it's more or less same.

SQLdb is passing statement via sqlite3_prepare before executing. And sqlite3_prepare return 1 in case if cipher was explicitly set to chacha20 or did not, so using default chacha20. If before executing PRAGMA rekey="passphrase" PRAGMA cipher="aes256cbc" or aes128cbc, or rc4 was executed, then sqlite3_prepare return 0 on PRAGMA rekey="passphrase".

[utelle]

It seem to be indeed working via SQLite3mc shell.

Yes, it does. I'm unable to reproduce your issue using the SQLite3mc shell.

This is Pascal. I'm trying both Free Pascal with SQLdb and Delphi with Zeos 8 + ZDbcSqLite. I didn't look into Zeos realization, but i guess it's more or less same.

Sorry, I don't have experience with Free Pascal / Delphi. And I don't know how the component SQLdb handles the execution of the pragma statements internally.

SQLdb is passing statement via sqlite3_prepare before executing. And sqlite3_prepare return 1 in case if cipher was explicitly set to chacha20 or did not, so using default chacha20. If before executing PRAGMA rekey="passphrase" PRAGMA cipher="aes256cbc" or aes128cbc, or rc4 was executed, then sqlite3_prepare return 0 on PRAGMA rekey="passphrase".

I suspect that there is some sort of file lock in place, and the ciphers "chacha20" and "sqlcipher" need to access the database file in preparation of the encryption process.

However, IMHO this is not an issue of SQLite3 Multiple Ciphers, but of the Pascal interface.

[regs01]

very short example of code

program consoletest3;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}
  cthreads,
  {$ENDIF}
  Classes, SysUtils, SQLDB, SQLite3Conn, SQLDBLib;

var
  LDBLink: TSQLite3Connection;
  LDBTransaction: TSQLTransaction;
  LDBLibrary: TSQLDBLibraryLoader;
  sDatabaseName: String;
  sCipher: String;
  sKey: String;
  sLibraryName: String;
begin

  sDatabaseName := IncludeTrailingPathDelimiter(ExtractFilePath(ParamStr(0))) + 'test.s3db';
  sCipher := 'chacha20';
  //sCipher := 'aes256cbc';
  sKey := '1234';
  sLibraryName := 'sqlite3mc_icu.dll';

  LDBLink := TSQLite3Connection.Create(Nil);
  LDBTransaction := TSQLTransaction.Create(LDBLink);
  LDBLink.Transaction := LDBTransaction;
  LDBLink.DatabaseName := sDatabaseName;

  LDBLibrary := TSQLDBLibraryLoader.Create(LDBLink);
  LDBLibrary.ConnectionType := 'SQLite3';
  LDBLibrary.LibraryName := sLibraryName;
  LDBLibrary.LoadLibrary;

  try

    LDBLink.Open;

    if not LDBLink.Connected then
      raise Exception.Create('Cannot open database');

    LDBLink.ExecuteDirect(Format('PRAGMA cipher="%s"', [sCipher]));
    LDBLink.ExecuteDirect(Format('PRAGMA rekey="%s"', [sKey])); // <- error 1 in sqlite3_prepare if cipher is not aes256cbc, aes128cbc or rc4
    LDBLink.Close;

  finally
    LDBLibrary.Free;
    LDBTransaction.Free;
    LDBLink.Free;
  end;

end.

[utelle]

After a quick check of the SQLdb implementation I most likely found the cause of the problem. As far as I can tell the method ExecuteDirect starts internally a transaction. However, PRAGMA rekey must not be enclosed in a transaction - it is simply mere chance that it works for some cipher schemes.

This behaviour can be reproduced in the SQLite3mc shell:

BEGIN; -- Start transaction
PRAGMA cipher='chacha20';
PRAGMA rekey='passphrase';

results in the message Parse error: SQL logic error.

Unfortunately, the SQLdb package doesn't seem to offer a method to execute SQL statements without starting a transaction. However, creating a transaction object, setting its options to [stoUseImplicit], and then passing explicitly this transaction object to method ExecuteDirect should do the trick.

[regs01]

Setting transaction to implicit did work.

And with Zeos I was actually starting transaction. Commenting out lines did the trick.

  if ZConnection1.Connected then
  begin
    //ZConnection1.StartTransaction;
    ZConnection1.ExecuteDirect(Format('PRAGMA cipher="%s"', [ComboBox1.Text]));
    ZConnection1.ExecuteDirect(Format('PRAGMA rekey="%s"', [Edit1.Text])) ;
    //ZConnection1.Commit;
  end;

Would really be great to denote it here for newbies
https://utelle.github.io/SQLite3MultipleCiphers/docs/configuration/config_sql_pragmas/

[utelle]

Setting transaction to implicit did work.

IMHO it is a deficiency of SQLdb to forcefully start a transaction for SQL statements executed via method ExecDirect. Setting the transaction to implicit is just a workaround to overcome this obstacle.

And with Zeos I was actually starting transaction. Commenting out lines did the trick.

  if ZConnection1.Connected then
  begin
    //ZConnection1.StartTransaction;
    ZConnection1.ExecuteDirect(Format('PRAGMA cipher="%s"', [ComboBox1.Text]));
    ZConnection1.ExecuteDirect(Format('PRAGMA rekey="%s"', [Edit1.Text])) ;
    //ZConnection1.Commit;
  end;

For many pragma commands that modify the behaviour of SQLite it makes no sense to enclose them with a transaction. Usually their effect can't be undone by issuing a ROLLBACK command - although there are exceptions (like PRAGMA user_version which is undone by a ROLLBACK). However, with few exceptions the SQLite documentation does not contain information, how pragmas behave within a transaction.

Would really be great to denote it here for newbies https://utelle.github.io/SQLite3MultipleCiphers/docs/configuration/config_sql_pragmas/

I will add a note that cipher related pragmas should not be used within transactions. However, most pragmas could be used within transactions, although their effect will not be influenced by a ROLLBACK.