How to open Kotlin encrypted database on Flutter side?

[outdoorapps]

I am trying to open an encrypted database created with Room and SQLCipher 4.14 on Flutter but so far have no success. For unencrypted database I got it to work, just not the encrypted one.

I tried both the SQLite3MultipleCiphers approach and the encrypted_drift approach on https://drift.simonbinder.eu/platforms/encryption/. Both of them gives me code 26 exception. I tried the unecrypted version and it works, so it must be something able the encryption. I have checked the hexKey and the byteArray and ensured it was correctly converted. Can anyone tell me what am I missing here?

net.zetetic.database.sqlcipher.SQLiteNotADatabaseException: file is not a database (code 26): , while compiling: SELECT COUNT(*) FROM sqlite_schema;

The Kotlin code:

val factory = SupportOpenHelperFactory(key) // Key is a ByteArray
                val instance = Room.databaseBuilder(
                    appContext, RoomManager::class.java, "app_db.sqlite"
                ).openHelperFactory(factory).build()

SQLite3MultipleCiphers approach

final hexKey = hex.encode(key ?? []);
driftDatabase(
      name: 'app_db',
      native: DriftNativeOptions(
        databaseDirectory: () async {
          final dir = await getApplicationDocumentsDirectory();
          return join(dir.parent.path, 'databases');
        },
        setup: (database) {
          database.execute("PRAGMA cipher = 'sqlcipher';");
          database.execute("PRAGMA legacy = 4;");
          database.execute("PRAGMA key = \"x'$hexKey'\";");
          
          final check = database.select("SELECT count(*) FROM sqlite_master;");
          debugPrint("Decryption successful. Found ${check.length} tables.");
        },
      ),
    );

encrypted_drift approach

final hexKey = hex.encode(key ?? []);
EncryptedExecutor.inDatabaseFolder(
      path: path,
      password: hexKey,
    );

[simolus3]

I think the SQLite3MultipleCiphers example should work. I've tried to reproduce this by opening a database with the sqlcipher CLI:

$ sqlcipher test.db
sqlite> PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'";
sqlite> pragma user_version = 10;

Then, I ran this Dart program:

import 'package:sqlite3/sqlite3.dart';

void main() {
  final db = sqlite3.open('test.db');
  print(db.select('pragma cipher'));

  db.execute("pragma cipher = 'sqlcipher';");
  db.execute("pragma legacy = 4;");
  db.execute(
    "pragma key = \"x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'\"",
  );

  print(db.select('pragma user_version'));
}

Which was able to read the user version and decrypt the database.

However, I see you get the exception in Kotlin and not in Dart. What pragmas are you running on the Kotlin side? Can you obtain a copy from the on-device database created with Kotlin to inspect it and make sure it's encrypted?

---

Finally, I'll note that linking and loading two copies of SQLite with your app is generally a bad idea. In your case, your Kotlin code is using the native Android SQLCipher library while the sqlite3 Dart package would ship an independent build of SQLite3MultipleCiphers. Because SQLite uses file locking for database operations (which operates on a per-process level), accessing a SQLite database with multiple SQLite libraries might corrupt it!

For this reason, I would recommend the encrypted_drift approach if you need to access the same database from Kotlin and Dart. That currently doesn't support hexkeys, but I can look into that. Either way though, it would be good to figure out why Kotlin can't read the database after the Dart pragmas ran on it.

[outdoorapps]

This is a sample database created with Kotlin. It will open in kotlin but not in flutter (not the sqlite3 code you used nor encrypted_drift). The key is a3472d6a9d91d46a3ce2c84405a07678410d1fe3b218d1ba42fe0e78ed236b22
test_db.sqlite.zip

I didn't use any pragma, I just use the SupportOpenHelperFactory from net.zetetic.database.sqlcipher

val factory = SupportOpenHelperFactory(key)
                val instance = Room.databaseBuilder(
                    appContext, RoomManager::class.java, "test_db.sqlite"
                ).openHelperFactory(factory).build()

So far:
Create in Kotlin -> open in Kotlin - ok
Create in Flutter -> open in Flutter - ok
Crossover (created by one platform and read from another) - Does not work

The reason the app was designed this way is because there are native background works that need database access. If there is no solution at the end, I can always go with method channel, just more verbose.

[outdoorapps]

I figured it out. Use PRAGMA hexkey instead of PRAGMA key

database.execute("PRAGMA cipher = 'sqlcipher';");
database.execute("PRAGMA legacy = 4;");
database.execute("PRAGMA hexkey = '$hexKey';"); // Use hexkey instead of key