export() to convert unencrypted to encrypted programmatically

[JReally]

Is there an equivalent to:

sqlcipher_export() Convenience function that can duplicate a database contents to an attached database with different settings.

I'm attempting to convert an unencrypted to encrypted DB programmatically. I have everything working but the sqlcipher_export() I used previously avoided all the detailed programming.

Thanks for any insights.

J

[utelle]

Is there an equivalent to sqlcipher_export() convenience function that can duplicate a database contents to an attached database with different settings.

Yes. The VACUUM INTO command of SQLite (see VACUUM command documentation) allows to create a copy of a database. By using an URI filename for the target of the VACUUM command you can specify and configure the cipher scheme via URI parameters.

I'm attempting to convert an unencrypted to encrypted DB programmatically. I have everything working but the sqlcipher_export() I used previously avoided all the detailed programming.

SQLite3 Multiple Ciphers allows to encrypt a plain unencrypted database in place by using a PRAGMA REKEY command. If you want to use a non-default cipher scheme, you will have to issue additional PRAGMA commands to select (and to configure) the cipher scheme.

[JReally]

Thanks Ulrich... took a few steps to implement but got it to work. On the other hand the rekey on unencrypted db never seems to work. The SQLcipher website confirms this and indicates that it's necessary to copy the db.

Here's a Lazarus code snippet for anyone trying to use "vacuum into" (no error checking tho')

procedure TForm1.btnVacuumIntoClick(Sender: TObject);
var
  lSqlite3Connection : TSqlite3Connection;
  lSQLTransaction    : TSQLTransaction;

  lDBPathAndNameSrc,
  lDBPathAndNameDst : string;

  lPwdSrc,
  lPwdDst : string;
begin
 lDBPathAndNameSrc :=  'c:\lazarus_projects\test_Vacuum\data\DataDst.db';
 lDBPathAndNameDst :=  'c:\lazarus_projects\test_Vacuum\data\DataDst2.db';

  lPwdSrc := '';   // important that this password matches the config of src DB
  lPwdDst := '123';

  lSQLite3Connection := TSQLite3Connection.Create(nil);
  lSQLTransaction    := TSQLTransaction.Create(nil);
  try
    lSQLite3Connection.Transaction  := lSQLTransaction;
    lSQLTransaction.Options         := [stoUseImplicit];   // Note this option for vacuum
    lSQLite3Connection.DatabaseName := lDBPathAndNameSrc;
    lSQLite3Connection.ExecuteDirect('PRAGMA cipher=''sqlcipher''');
    lSQLite3Connection.ExecuteDirect('PRAGMA Legacy=4');
    if lPwdSrc <> '' then lSQLite3Connection.ExecuteDirect('PRAGMA Key=' + lPwdSrc);

    lSQLite3Connection.Connected := true;

    lSQLite3Connection.ExecuteDirect('VACUUM main into ' +
      QuotedStr('file:' + lDBPathAndNameDst +'?cipher=sqlcipher&legacy=4&key='+ lPwdDst));
 finally
   lSQLite3Connection.Connected := false;
   lSQLite3Connection.Free;
   lSQLTransaction.Free;
   end;
end;               

[utelle]

On the other hand the rekey on unencrypted db never seems to work.

This is simply not true. For example, if I enter the following SQL commands in the SQLite shell application (precompiled binaries provided with each release of SQLite3 Multiple Ciphers) for an unencrypted database I get a database file properly encrypted with the latest legacy SQLCipher encryption scheme:

PRAGMA cipher='sqlcipher';
PRAGMA legacy=4;
PRAGMA rekey='passphrase';

On opening the encrypted database file the following SQL commands have to be executed:

PRAGMA cipher='sqlcipher';
PRAGMA legacy=4;
PRAGMA key='passphrase';

The SQLcipher website confirms this and indicates that it's necessary to copy the db.

I don't know why Zetetic, the creators of SQLCipher, chose that approach. Actually, the SQLite3 Multiple Ciphers project manages to get away without copying the database file by using a modified vacuum procedure to encrypt/recrypt/decrypt the database file in place.

[JReally]

Hmmm! my reply did not appear so I'll try again.

Ulrich,
You are quite right about this. I was connecting unencrypted and then trying to apply rekey. Since it didn't work I accepted the Zetetic solution.

Now I follow you solutions... everything is A-OK.

Thanks,
J