Encryption documentation (for encrypted_drift) (file is not a database)

[MichaelDark]

Problem

On IOS, SQLite (FMDB) and SQLCipher (FMDB/SQLCipher) are in conflict.
Avoid linking both of them in the project at the same time.
It will lead to unintended unpredictable behavior.

I am having the same issue as described here (file is not a database):
#1810

Potential fix:
#1810 (comment)

Use Case

Assuming the following use case:

• Flutter app uses drift as a DB
  • (which uses sqlite3 via NativeDatabase(...))
• Flutter app depends on the flutter_cache_manager
  • (which uses sqflite)
• Flutter app depends on Plugin that uses encrypted_drift
  • (which uses sqflite_sqlcipher via EncryptedExecutor(...))

pubspec.yaml in Flutter app:

dependency_overrides:
  ...
  sqflite:
    git:
      url: https://www.github.com/davidmartos96/sqflite_sqlcipher.git
      ref: fmdb_override
      path: sqflite

dependencies:
  flutter:
    sdk: flutter
  ...
  drift: ^2.12.1
  sqlite3: ^2.3.0
  sqlite3_flutter_libs: ^0.5.12

pubspec.yaml in Plugin:

dependencies:
  flutter:
    sdk: flutter
  ...
  drift: ^2.9.0
  sqlite3: ^2.1.0
  sqlite3_flutter_libs: ^0.5.15
  encrypted_drift:
    git:
      url: https://github.com/simolus3/drift.git
      path: extras/encryption

Proposition

@simolus3 @davidmartos96 Could you please update the documentation on what is the proper way to set up the described use case?

Especially:

• What is a proper way to set up dependencies for the specified use case?
• How can we verify that Podfile.lock does not contain conflicting libraries (FMDB and FMDB/SQLCipher at the same time)?
• What are possible caveats on all platforms, especially Android/IOS (and their potential workaround)?

More info on the SQLite vs SQLCipher

...you should only use one SQLite library version at a time.
If you are using SQLCipher you should ensure that SQLCipher is the only library
linked to your application. Once you do, it is perfectly fine to use SQLCipher
to open standard SQLite databases - it is fully compatible and operates exactly
like SQLite when a database is not encrypted. So, in your example,
you would need to make sure that your React native Asyncstorage used SQLCipher as linked.

If it includes a different version of SQLite itself,
resulting in two libraries being linked with the application,
then the behavior would best be considered “undefined”
(C) https://discuss.zetetic.net/t/can-sqlite-and-sqlcipher-be-used-simultaneously/3609/4

Additional links:
https://drift.simonbinder.eu/docs/platforms/encryption/#extra-setup-on-android-and-ios
https://pub.dev/packages/sqflite_sqlcipher#if-using-sqflite-as-direct-or-transitive-dependency
https://pub.dev/packages/sqlcipher_flutter_libs#incompatibilities-with-sqlite3-on-ios-and-macos
https://discuss.zetetic.net/t/encrypted-db-in-swift-ios-via-fmdb-can-not-open-in-sql-db-browser/4813/4
https://discuss.zetetic.net/t/important-advisory-sqlcipher-with-xcode-8-and-new-sdks/1688
https://discuss.zetetic.net/t/cannot-open-encrypted-database-with-sqlcipher-4/3654/4

More additional links:
https://github.com/sqlcipher/sqlcipher
https://pub.dev/packages/sqlite3_flutter_libs
https://pub.dev/packages/sqlcipher_flutter_libs
https://ccgus.github.io/fmdb/html/index.html
#1480
https://discuss.zetetic.net/t/can-sqlite-and-sqlcipher-be-used-simultaneously/3609/2

[simolus3]

Hopefully, the upcoming native-assets feature will fix this by giving us full control over the libraries we want to link.

Since we're using dynamic libraries, I also don't think there's much of a problem on Android. sqlite3 and sqlcipher can be loaded into the same process there, we're distinguishing symbols by looking them up through the library. iOS is problematic because we're typically linking everything into one bundle, so libraries with duplicate symbol names will cause a clash.

As far as I'm aware, there really is no way to use both sqlite3 and sqlcipher in the same project on iOS. So if you have a dependency on sqlcipher, I'd drop the dependency on sqlite3_flutter_libs and just have drift use sqlcipher instead (since sqlcipher works without encryption as well).

[MichaelDark]

Original comment mentions a temporary solution, which can be untimatively resolved by dart-lang/sdk#50565

Original comment

But the `drift` itself depends on the `sqlite3`. Even though it is not linking any libraries to drift, it might be very misleading because:

sqlite3.so != package:sqlite3
sqlite3.so == package:sqlite3_flutter_libs

One of possible long-term solutions to prevent any issues with the setup is to split drift in multiple packages.

• drift containing the runtime classes and DelegatedDatabase
  • no sqlite3 reference
• drift_sqlite3 containing native/wasm implementation
  • depends on sqlite3
  • depends on sqlite3_flutter_libs
  • contains Sqlite3DriftDatabase (DelegatedDatabase impl)
• drift_sqflite_sqlcipher containing Method Channel implementation for SQLCipher
  • depends on sqflite_sqlcipher
  • contains SqfliteSqlCipherDriftDatabase (DelegatedDatabase impl) (before: EncryptedExecutor)
  • in doc specifies to override sqflite for 3rd party (like flutter_cache_manager) with link to source [*]
• [FUTURE] drift_sqlcipher containing FFI implementation for SQLCipher
  • depends on sqlcipher_flutter_libs
  • contains SqlCipherDriftDatabase (DelegatedDatabase impl)
  • in doc specifies to override sqflite for 3rd party (like flutter_cache_manager) with link to source [*]

[*] Dependency override for 3rd party packages dependent on sqflite

dependency_overrides:
  ...
  sqflite:
    git:
      url: https://www.github.com/davidmartos96/sqflite_sqlcipher.git
      ref: fmdb_override
      path: sqflite

For usage without encryption:

dependencies:
  drift: any
  drift_sqlite3: any

For usage with encryption:

dependency_overrides:
  sqflite:
    git:
      url: https://www.github.com/davidmartos96/sqflite_sqlcipher.git
      ref: fmdb_override
      path: sqflite

dependencies:
  drift: any
  drift_sqflite_sqlcipher: any
  # OR
  # drift_sqlcipher: any

As a result, the usage will be more straightforward and will not require any additional setup. And this will eliminate almost all problems that might be cause by the database linking. Do you see any caveats with this approach?

P.S. With this approach, drift_dev depending on sqlite3 will still be a bit misleading 😅

P.P.S. One of examples in the repo might be outdated. drift#examples/encryption example, that just overrides linux/windows libaries, needs to be updated with a remark that on IOS that does not work.

P.P.P.S. Do I understand correctly that if I modify EncryptedExecutor to accept nullable password, and open non-encrypted database with null password, drift will still be able to read/write the database normally, right? If yes, then the suggested approach, in case if someone decided to switch to encrypted database, will just need to change drift_sqlite3 to drift_sqlcipher.

[MichaelDark]

I'd drop the dependency on sqlite3_flutter_libs and just have drift use sqlcipher instead

@simolus3 Do I have to simply remove sqlite3_flutter_libs or replace it with sqlcipher_flutter_libs?

To use SQLCipher only:

• replace sqlite3_flutter_libs with sqlcipher_flutter_libs
• make sure that sqflite override and sqlcipher_flutter_libs are using the same SQLCipher version
  • For example, SQLCipher 4.5.4 is used by:
    • current sqflite_sqlcipher fmdb_override branch (6be83a5)
    • sqlcipher_flutter_libs: 0.5.7
• override sqlite3.open as described in this example

main.dart in Flutter app:

import 'package:sqlcipher_flutter_libs/sqlcipher_flutter_libs.dart';
import 'package:sqlite3/open.dart';

void main() {
  // No need to override IOS 
  // because `sqlite3.framework` is already replaced with SQLCipher implementation
  open.overrideFor(OperatingSystem.android, openCipherOnAndroid);

  WidgetsFlutterBinding.ensureInitialized();
  // ...
  runApp(...);
}

pubspec.yaml in Flutter app:

dependency_overrides:
  sqflite: # fmdb_override

dependencies:
  drift: 
  sqlite3: 
  sqlcipher_flutter_libs:

pubspec.yaml in Plugin:

dependencies:
  drift:
  sqlite3:
  sqlcipher_flutter_libs:
  encrypted_drift: # extras/encryption

[MichaelDark]

Hopefully, the upcoming native-assets feature will fix this by giving us full control over the libraries we want to link.

dart-lang/sdk#50565 would be really a game-changer.
Monitoring it now 👀