Problem with encryption in 2.5.17

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
KajC
Posts: 6
Joined: Sun 30 Jun 2013 10:38

Problem with encryption in 2.5.17

Post by KajC » Thu 30 Jul 2015 15:18

Hi,
Until now I've been using C++Builder6 and SQLite 1.6.5 for a project, but I'm now upgrading it to XE7 and SQLite 2.5.17.

I'm having problems with the encryption of databases. It has to do with the key containing Swedish characters, and it gets translated somewhere. See code below.

Code: Select all

void __fastcall TForm1::Button3Click(TObject *Sender)
{
  TFileStream       *fs     = NULL;
  TSQLiteConnection *con    = NULL;
  UnicodeString     encKey  = L"abc123Ä"; // Will result in corruption of 'Db2.sqlite'


  // Create 2 empty database files
  // -----------------------------

  UnicodeString db1 = ExtractFilePath( ParamStr(0) ) + L"Db1.sqlite";
  UnicodeString db2 = ExtractFilePath( ParamStr(0) ) + L"Db2.sqlite";

  DeleteFile( db1 );
  fs = new TFileStream( db1, fmCreate | fmShareExclusive );
  delete( fs );
  fs = NULL;

  DeleteFile( db2 );
  fs = new TFileStream( db2, fmCreate | fmShareExclusive );
  delete( fs );
  fs = NULL;


  // Connect to database 'Db1' with blowfish encryption
  // --------------------------------------------------

  con = new TSQLiteConnection;

  con->SetProp( prDatabase,       db1 );
  con->SetProp( prStaticLibrary,  true );         // Always use static library
  con->SetProp( prBusyTimeout,    1000 );         // Add 1000ms busy timeout

  con->SetProp( prEncryptionAlgorithm, leBlowfish);
  con->SetProp( prEncryptionKey,  encKey );

  con->Connect( L"" );


  // Create a table in Db1
  // ---------------------

  con->ExecCommand( "CREATE TABLE Table1 ( Id INTEGER PRIMARY KEY, Field1 TEXT, Field2 TEXT );" );


  // Attach database Db2
  // -------------------

  UnicodeString attachCmd = UnicodeString::Format(  L"ATTACH DATABASE '%s' AS '%s' KEY '%s';",
                                            ARRAYOFCONST((
                                              db2,
                                              L"Db2",
                                              encKey )) );

  con->ExecCommand( attachCmd );

  // Create a table in Db2
  // ---------------------

  con->ExecCommand( L"CREATE TABLE Db2.Table2 ( Id INTEGER PRIMARY KEY, Field1 TEXT, Field2 TEXT );" );


  // Close databases and destroy connection object
  // ---------------------------------------------

  con->Disconnect();
  delete( con );
  con = NULL;


  // Verify databases by opening them
  // --------------------------------

  bool success = false;

  for( int idx=0; idx<2; idx++ ){

    UnicodeString dbFile = ( idx == 0 ) ? db1 : db2;

    con = new TSQLiteConnection;

    con->SetProp( prDatabase,       dbFile );
    con->SetProp( prStaticLibrary,  true );         // Always use static library
    con->SetProp( prBusyTimeout,    1000 );         // Add 1000ms busy timeout

    if( encKey.IsEmpty() == false ){
      con->SetProp( prEncryptionAlgorithm, leBlowfish );
      con->SetProp( prEncryptionKey, encKey );
    }

    try{
      con->Connect( L"" );

      // Db is successfully opened!

      ShowMessage( UnicodeString::Format( L"Success opening database %s!",
                                          ARRAYOFCONST((
                                            ExtractFileName( dbFile )
                                          )) ) );
    }
    catch( ... ){
      ShowMessage( UnicodeString::Format( L"Failure opening database %s!",
                                          ARRAYOFCONST((
                                            ExtractFileName( dbFile )
                                          )) ) );
    }

    delete( con );
    con = NULL;
  }
}
The main database is fine with whatever encryption key I use, but the attached database is corrupted if key holds any Swedish characters. If I change to specify the key as a BLOB constant, then it will work.
ATTACH DATABASE '../Db2.sqlite' AS 'Db2' KEY X'616263313233C5';

Issue 2:
In previous version I didn't need to specify the KEY word with the 'Attach'-command. It automatically used the encryption specified for the master database. If I exclude the KEY word, then the database is not encrypted at all.
See: Using the attach command at https://www.sqlite.org/see/doc/trunk/www/readme.wiki

Bug or?

MaximG
Devart Team
Posts: 1019
Joined: Mon 06 Jul 2015 11:34

Re: Problem with encryption in 2.5.17

Post by MaximG » Fri 31 Jul 2015 10:13

Thank you for the information. We have reproduced the problem and fixed the bug. The fix will be included in the next LiteDAC build. Currently, as a workaround, don't use national characters in the encryption key.
Ye, you are right. In the current version of LiteDAC, the behavior of ATTACH was somewhat changed due to extended functionality of LiteDAC for work with attached databases. Currently, if the KEY phrase is omitted, then the attached database is not encrypted.

KajC
Posts: 6
Joined: Sun 30 Jun 2013 10:38

Re: Problem with encryption in 2.5.17

Post by KajC » Sun 02 Aug 2015 12:51

Happy to hear that you could confirm it, and will fix it in next release :)

MaximG
Devart Team
Posts: 1019
Joined: Mon 06 Jul 2015 11:34

Re: Problem with encryption in 2.5.17

Post by MaximG » Tue 22 Sep 2015 15:13

The new LiteDAC version 2.6.19 is released including fix for «Bug with attaching a database containing international characters in the encryption key is fixed». The complete list of fixes is available at:

https://www.devart.com/litedac/revision_history.html

You can download LiteDAC 2.6.19 from our website: https://www.devart.com/litedac/download.html

Post Reply