SQLite Attach encrypted database

[ertank]

Hi,

Assume that we want to attach an encrypted (with UniDAC) database.

Code: Select all

UniConnection1.ExecSQL('attach "encrypted_sqlite.db" as source');

Seems to raise error code 11 which means it is encrypted.

Is it possible, or is it not possible to do that?

Thanks.

[AlexP]

Hello,

To attach an encrypted database, you should specify the key:

Code: Select all

ATTACH DATABASE 'file2.db' AS two KEY 'xyzzy';

See more detailed information at https://www.sqlite.org/see/doc/trunk/www/readme.wiki .

[ertank]

Hi,

I see that KEY needs readable string passed as a parameter. Is it possible to use non-readable string and provide it as a key?

For example assume following is our key here:

Code: Select all

const
  MyKEY : array of char = [#00,  #01, #02,  #03,  #04,  #05,  #06, #07];
begin
  UniConnection1.ExecSQL('attach database 'file2.db' as two KEY ' + QuotedStr(String(MyKEY)));
end;

In my test, using above line returns error code 11 even the provided Key is correct.

Thanks.

[AlexP]

Your sample works correctly. Please specify your versions of UniDAC and IDE

P.S. In addition, provide the "encrypted" password you are trying to use

[ertank]

UniDAC is 6.2.9
IDE Delphi 10 Seattle

I wanted to shorten example and post it without testing. My real Key which gets an error is below:

Code: Select all

const
      Key2564 : Array of Char = [#10,  #101, #12,  #103, #145, #150, #110, #109,
                                 #188, #129, #67,  #152, #100, #141, #187, #135,
                                 #01,  #11,  #12,  #31,  #10,  #17,  #62,  #78,
                                 #11,  #9,   #02,  #240, #230, #170, #206, #172,
                                 #188, #127, #207, #113, #44,  #77,  #11,  #15,
                                 #182, #193, #102, #109, #170, #127, #188, #195];

[AlexP]

Yes, you can use such key for an attached database, but with some conditions:
1) Such a key should be stored in a TBytes array
2) The key must be mapped to Hex, being previously converted from ANSI to UTF8
3) The key must be passed to the ATTACH command as a binary string
4) Before executing ATTACH, an encryption algorithm must be set for the attached database using the command: PRAGMA encryption=ХХ

A sample below demonstrates the behavior you need

Code: Select all

program Project4;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils,
  Uni,
  SQLiteUniProvider,
  LiteError;

const
      KeyBytes: TBytes = [10,  101, 12,  103, 145, 150, 110, 109,
                          188, 129, 67,  152, 100, 141, 187, 135,
                          01,  11,  12,  31,  10,  17,  62,  78,
                          11,  9,   02,  240, 230, 170, 206, 172,
                          188, 127, 207, 113, 44,  77,  11,  15,
                          182, 193, 102, 109, 170, 127, 188, 195];
var
  UniConnection: TUniConnection;
  i, ii: integer;
  str_key: AnsiString;
  hex_key: string;
  NewBytes: TBytes;
begin
  try
    str_key := TEncoding.ANSI.GetString(KeyBytes);
    hex_key := '';

    NewBytes := TEncoding.Convert(TEncoding.ANSI, TEncoding.UTF8, KeyBytes);
    for i := 0 to High(NewBytes) do
      hex_key := hex_key + IntToHex(NewBytes[i], 2);

    if FileExists('d:\main.db3') then
      DeleteFile('d:\main.db3');
    if FileExists('d:\attached.db3') then
      DeleteFile('d:\attached.db3');

    UniConnection := TUniConnection.Create(nil);
    try
      UniConnection.ProviderName := 'SQLite';
      UniConnection.Database := 'd:\main.db3';
      UniConnection.SpecificOptions.Values['Direct'] := 'True';
      UniConnection.SpecificOptions.Values['ForceCreateDatabase'] := 'true';
      UniConnection.Connect;
      UniConnection.ExecSQL('create table table1(id integer primary key, txt varchar(100))');
      UniConnection.ExecSQL('insert into table1(id, txt) values(1, ''test'')');
      UniConnection.Disconnect;
      UniConnection.Database := 'd:\attached.db3';
      UniConnection.SpecificOptions.Values['EncryptionKey'] := str_key;
      UniConnection.SpecificOptions.Values['EncryptionAlgorithm'] := 'leAES128';
      UniConnection.Connect;
      UniConnection.ExecSQL('create table table2(id integer primary key, txt varchar(100))');
      UniConnection.Disconnect;
      UniConnection.Database := 'd:\main.db3';
      UniConnection.SpecificOptions.Values['EncryptionKey'] := '';
      UniConnection.SpecificOptions.Values['EncryptionAlgorithm'] := 'leDefault';
      UniConnection.Connect;
      try
        UniConnection.ExecSQL('PRAGMA encryption=AES128');
        UniConnection.ExecSQL('ATTACH DATABASE ''d:\attached.db3'' AS two KEY X''' + hex_key + '''');
      except
        on E: ESQLiteError do
          Writeln(e.Message + ' ' + E.ErrorCode.ToString);
      end;
      UniConnection.ExecSQL('insert into two.table2 select * from main.table1');
      UniConnection.ExecSQL('select * from two.table2');
    finally
      UniConnection.Free;
    end;
  except
    on E: Exception do
      writeln(e.Message);
  end;
  readln;
end.

[ertank]

Thanks for the sample code.

[AlexP]

You are welcome. Feel free to contact us if you have any further questions.