Encrypt an existing database

[Raluca1]

Hello,

We use a NativeDatabase with drift ^1.4.0 and sqlcipher_flutter_libs ^0.5.0. Is it possible to encrypt an existing database, that was created without a key? When I try to add the key:

 NativeDatabase(
    databaseFile,
    logStatements: false,
    setup: (rawDb) {
      if (key != null) {
          assert(rawDb.select('PRAGMA cipher_version;').isNotEmpty);
          rawDb.execute("PRAGMA key = '$key';");
      }
    },
  );

this error occurs the first time the database is accessed:

SqliteException(26): file is not a database, file is not a database (code 26)
  Causing statement: PRAGMA user_version;

    Here is the stack trace:
    ========================
    #0      throwException (package:sqlite3/src/impl/exception.dart:34:3)
#1      PreparedStatementImpl._selectResults (package:sqlite3/src/impl/statement.dart:118:7)
#2      PreparedStatementImpl.select (package:sqlite3/src/impl/statement.dart:92:12)
#3      DatabaseImpl.userVersion (package:sqlite3/src/impl/database.dart:82:25)
#4      _VmVersionDelegate.schemaVersion (package:drift/src/ffi/database.dart:253:58)
#5      DelegatedDatabase._runMigrations (package:drift/src/runtime/executor/helpers/engines.dart:321:42)
#6      DelegatedDatabase.ensureOpen.<anonymous closure> (package:drift/src/runtime/executor/helpers/engines.dart:303:13)
<asynchronous suspension>
#7      ServerImplementation._handleEnsureOpen (package:drift/src/remote/server_impl.dart:116:12)
<asynchronous suspension>
#8      DriftCommunication.setRequestHandler.<anonymous closure>.<anonymous closure> (package:drift/src/remote/communication.dart:152:13)
<asynchronous suspension>

Is it also possible to change the key a database was encrypted with?

Thank you

[simolus3]

This helpful article was linked on the SQLCipher docs: https://discuss.zetetic.net/t/how-to-encrypt-a-plaintext-sqlite-database-to-use-sqlcipher-and-avoid-file-is-encrypted-or-is-not-a-database-errors/868

Basically, what you need to do is:

• Open the original database
• Use the sqlcipher_export function to write the plain-text database into an encrypted file
• Then open the new file as a NativeDatabase with the key pragma.

If you want to rename the file in the process (e.g. use original.db for the existing plain-text db and original.db.enc for the new one), you can just check if the original.db file exists, perform the conversion in that case and finally delete it. You can put this logic in the async block of a LazyDatabase. You could use something like this for the encryption:

import 'package:sqlite3/sqlite3.dart';

void _encryptExisting(String existingPath, String encryptedPath) {
  sqlite3.open(existingPath)
    // Note: This assumes that there's no potential for SQL injections in the
    // path. Cautiously use prepared statements if needed ;)
    ..execute("ATTACH '$encryptedPath' AS encrypted KEY 'your_new_key';")
    ..select("SELECT sqlcipher_export('encrypted')")
    ..dispose();
}

[Raluca1]

Great, thank you - I encrypted an existing database and it worked as explained.