[windows] Support sqlcipher for windows platform

[gibutm]

Presently the plugin has support for Android, iOS and macos but does not have support for windows.

Is there a plan to add windows support in the future? I have done some preliminary investigation and experimenting with this concept. My approach involved modifying the sqflite_common_ffi and sqflite_common packages to add support for an optional password field which can be passed down to the underlying openDatabse implementation. So far it looks promising, the changes will create/open a encrypted sqlcipher DB if the bundled library is sqlcipher or will create/open an unencrypted sqlite DB if the bundled library is sqlite.

It would be great to get @davidmartos96 and @tekartik thoughts on this. I can raise a PR so that you can have a review and start a discussion going on this.

[davidmartos96]

This package is no more than a wrapper to handle the platform channel calls from sqflite to the native side, so that is why it's only for Android, iOS and macos. I'm working on a desktop app which uses SQLCipher with FFI, so I'm not using this package. What I did is use moor and create a custom DatabaseExecutor https://github.com/simolus3/moor/issues/451#issuecomment-602062594

---

In any case, using SqlCipher with sqflite_common_ffi should be possible without changing too much the package. @tekartik There seems to be a bug in sqflite_common_ffi when overriding the library opener from sqlite3. Because a separate isolate is being used, changes to the open variable from the main isolate are not visible in the separate isolate because memory is not shared. Should that be fixed? Is this line from your source code a workaround? Because if it is, I believe is not working as expected. https://github.com/tekartik/sqflite/blob/6c8ec72f9deb2a8d03928f58dae3acaa2494ca37/sqflite_common_ffi/lib/src/windows/setup.dart#L25 What I did below for it to work is create a container class SqfliteFFIOpener with an static method that is then passed to the isolate as an argument. I don't know if there is a better solution, but this is something similar to what moor does with its isolate setup. https://github.com/simolus3/moor/blob/1cca22bc56e74772b8bccdc4aa6401904b725d6c/moor/lib/src/runtime/isolate/moor_isolate.dart#L82

This code below works with the changes mentioned above. Tested on Linux

Future<void> main() async {
    // Needs to be static so that the Sqflite FFI isolate can use it
    SqfliteFFIOpener.opener = opener;

    var db = await databaseFactoryFfi.openDatabase(
      "example_pass_1234.db",
      options: OpenDatabaseOptions(
        onConfigure: (db) async {
          await db.rawQuery("PRAGMA KEY='1234'");
        },
      ),
    );
    print(await db.rawQuery("PRAGMA cipher_version"));
    print(await db.rawQuery("SELECT * FROM sqlite_master"));

    await db.close();
  });
}

SqfliteFFIOpenerOverrides opener() {
  final overrides = SqfliteFFIOpenerOverrides();
  overrides.overrideForAll(sqlcipherOpen);
  return overrides;
}

DynamicLibrary sqlcipherOpen() {
  // Taken from https://github.com/simolus3/sqlite3.dart/blob/e66702c5bec7faec2bf71d374c008d5273ef2b3b/sqlite3/lib/src/load_library.dart#L24
  if (Platform.isLinux || Platform.isAndroid) {
    try {
      return DynamicLibrary.open('libsqlcipher.so');
    } catch (_) {
      if (Platform.isAndroid) {
        // On some (especially old) Android devices, we somehow can't dlopen
        // libraries shipped with the apk. We need to find the full path of the
        // library (/data/data/<id>/lib/libsqlite3.so) and open that one.
        // For details, see https://github.com/simolus3/moor/issues/420
        final appIdAsBytes = File('/proc/self/cmdline').readAsBytesSync();

        // app id ends with the first \0 character in here.
        final endOfAppId = max(appIdAsBytes.indexOf(0), 0);
        final appId = String.fromCharCodes(appIdAsBytes.sublist(0, endOfAppId));

        return DynamicLibrary.open('/data/data/$appId/lib/libsqlcipher.so');
      }

      rethrow;
    }
  }
  if (Platform.isIOS) {
    return DynamicLibrary.process();
  }
  if (Platform.isMacOS) {
    // TODO: Unsure what the path is in macos
    return DynamicLibrary.open('/usr/lib/libsqlite3.dylib');
  }
  if (Platform.isWindows) {
    // TODO: This dll should be the one that gets generated after compiling SQLcipher on Windows
    return DynamicLibrary.open('sqlite3.dll');
  }

  throw UnsupportedError('Unsupported platform: ${Platform.operatingSystem}');
}

[davidmartos96]

@gibutm A new version of sqflite_common_ffi has been released with capabilities for loading SQLCipher. Here is a sample project that you can try yourself. Note that you will need to compile the SQLCipher library yourself for Windows. Some more information in this issue on how to do that. #20 Let me know if you have any issue.

I used the Dart version that comes with Flutter Beta version (1.24.0-10.2.pre)

1. dart create sqlcipher_ffi

2. pubspec.yaml

   
   name: sqlcipher_ffi
   description: A simple command-line application.
   # version: 1.0.0
   # homepage: https://www.example.com

environment: sdk: '>=2.12.0-0 <3.0.0'

dependencies: sqflite_common_ffi: ^2.0.0-nullsafety

dev_dependencies: pedantic: ^1.9.0

3. `bin/main.dart`
```dart
import 'dart:ffi';
import 'dart:io';
import 'dart:math';
import 'package:sqflite_common/sqlite_api.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:sqlite3/open.dart';

Future<void> main(List<String> arguments) async {
  final dbFactory = createDatabaseFactoryFfi(ffiInit: ffiInit);

  final db = await dbFactory.openDatabase(
    Directory.current.path + "/db_pass_1234.db",
    options: OpenDatabaseOptions(
      version: 1,
      onConfigure: (db) async {
        // This is the part where we pass the "password"
        await db.rawQuery("PRAGMA KEY='1234'");
      },
      onCreate: (db, version) async {
        db.execute("CREATE TABLE t (i INTEGER)");
      },
    ),
  );
  print(await db.rawQuery("PRAGMA cipher_version"));
  print(await db.rawQuery("SELECT * FROM sqlite_master"));
  print(db.path);
  await db.close();
}

void ffiInit() {
  open.overrideForAll(sqlcipherOpen);
}

DynamicLibrary sqlcipherOpen() {
  // Taken from https://github.com/simolus3/sqlite3.dart/blob/e66702c5bec7faec2bf71d374c008d5273ef2b3b/sqlite3/lib/src/load_library.dart#L24
  if (Platform.isLinux || Platform.isAndroid) {
    try {
      return DynamicLibrary.open('libsqlcipher.so');
    } catch (_) {
      if (Platform.isAndroid) {
        // On some (especially old) Android devices, we somehow can't dlopen
        // libraries shipped with the apk. We need to find the full path of the
        // library (/data/data/<id>/lib/libsqlite3.so) and open that one.
        // For details, see https://github.com/simolus3/moor/issues/420
        final appIdAsBytes = File('/proc/self/cmdline').readAsBytesSync();

        // app id ends with the first \0 character in here.
        final endOfAppId = max(appIdAsBytes.indexOf(0), 0);
        final appId = String.fromCharCodes(appIdAsBytes.sublist(0, endOfAppId));

        return DynamicLibrary.open('/data/data/$appId/lib/libsqlcipher.so');
      }

      rethrow;
    }
  }
  if (Platform.isIOS) {
    return DynamicLibrary.process();
  }
  if (Platform.isMacOS) {
    // TODO: Unsure what the path is in macos
    return DynamicLibrary.open('/usr/lib/libsqlite3.dylib');
  }
  if (Platform.isWindows) {
    // TODO: This dll should be the one that gets generated after compiling SQLcipher on Windows
    return DynamicLibrary.open('sqlite3.dll');
  }

  throw UnsupportedError('Unsupported platform: ${Platform.operatingSystem}');
}

[gibutm]

Thanks @davidmartos96 for a quick response and for coordinating the changes for the packages. Much appreciated!