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

MichaelDark commented 7 months ago

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): https://github.com/simolus3/drift/issues/1810

Potential fix: https://github.com/simolus3/drift/issues/1810#issuecomment-1119426006

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 https://github.com/simolus3/drift/issues/1480 https://discuss.zetetic.net/t/can-sqlite-and-sqlcipher-be-used-simultaneously/3609/2

simolus3 commented 7 months ago

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 commented 7 months ago

Original comment mentions a temporary solution, which can be untimatively resolved by https://github.com/dart-lang/sdk/issues/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](https://pub.dev/packages/sqflite_sqlcipher#if-using-sqflite-as-direct-or-transitive-dependency) [*] - [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](https://pub.dev/packages/sqflite_sqlcipher#if-using-sqflite-as-direct-or-transitive-dependency) [*] [*] Dependency override for 3rd party packages dependent on `sqflite` ```yaml dependency_overrides: ... sqflite: git: url: https://www.github.com/davidmartos96/sqflite_sqlcipher.git ref: fmdb_override path: sqflite ``` For usage without encryption: ```yaml dependencies: drift: any drift_sqlite3: any ``` For usage with encryption: ```yaml 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`](https://github.com/simolus3/drift/tree/develop/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 commented 7 months ago

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 commented 7 months ago

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

https://github.com/dart-lang/sdk/issues/50565 would be really a game-changer. Monitoring it now 👀

simolus3 / drift