Ecrypt existing database => IOS

[CharlotteJu]

Hello Simon,
I'm using your great library for my app, thanks again for its development !
But now I don't know what to do. I already have an existing database and now I want to encrypt it.
I followed all the documentation and everything works on Android. However, on iOS it doesn't work and I don't understand why. I get the error
SqliteException(1): while executing, no such function: sqlcipher_export, SQL logic error (code 1)
Causing statement: SELECT sqlcipher_export('encrypted');, parameters:
I don't find how to solve it with Flutter because I'm not supposed to have a specific configuration for iOS.

Do you have any idea to resolve it ?

NB : I replaced confidential information with xxx

pubspec.yaml :
I put the whole file because I read that there could be conflicts, but I don't think I have any.

name: xxx
description: xxx
publish_to: 'none' # Remove this line if you wish to publish to pub.dev

version: 6.73.7+17

environment:
  sdk: '>=3.0.0 <4.0.0'


dependencies:
  flutter:
    sdk: flutter
  flutter_localizations:
    sdk: flutter
  cupertino_icons: ^1.0.2
  firebase_crashlytics: ^3.0.17
  firebase_core: ^2.8.0
  firebase_analytics: ^10.1.6
  phosphor_flutter: ^2.0.1
  go_router: ^14.6.0
  intl: any
  sizer: ^2.0.15
  riverpod: ^2.3.6
  flutter_riverpod: ^2.3.6
  riverpod_annotation: ^2.1.1
  shared_preferences: ^2.1.0
  json_annotation: ^4.8.1
  injectable: ^2.1.2
  get_it: ^7.6.0
  drift: ^2.19.0
  #sqlite3_flutter_libs: ^0.5.0
  path_provider: ^2.1.1
  path: ^1.8.3
  build_config: ^1.1.1
  stack_trace: ^1.11.0
  camera: ^0.10.5+4
  mobile_scanner: ^3.4.1
  http: ^1.2.0
  encrypt_shared_preferences: ^0.4.8
  table_calendar: ^3.0.9
  calendar_date_picker2: ^0.5.5
  flutter_expandable_fab: ^2.0.0
  flutter_cache_manager: ^3.4.1
  barcode_widget: ^2.0.4
  url_launcher: ^6.3.0
  nfc_manager: ^3.5.0
  geolocator: ^12.0.0
  geocoding: ^3.0.0
  connectivity_plus: ^6.0.5
  permission_handler: ^11.3.1
  package_info_plus: ^8.0.2
  file_selector: ^1.0.3
  #open_filex: ^4.6.0
  ## TODO : Reprendre la version normale lorsque la MAJ aura été publiée https://github.com/BlueBubblesApp/bluebubbles-app/issues/2896
  open_filex:
    git:
      url: https://github.com/mufassalhussain/open_filex
  image_picker: ^1.1.2
  mime: ^1.0.6
  encrypt: ^5.0.0
  flutter_osm_plugin: 1.2.0
  device_info_plus: ^10.1.2
  app_links: ^6.3.2
  native_exif: ^0.6.0
  share_plus: ^10.0.0
  flutter_markdown: ^0.7.5
  htmltopdfwidgets: ^1.0.9
  flutter_svg: ^2.0.17
  carousel_slider: ^5.0.0
  dart_ping: ^9.0.1
  flutter_local_notifications: ^19.0.0
  flutter_timezone: ^4.1.0
  timezone: ^0.10.0
  font_awesome_flutter: ^10.8.0
  sqlcipher_flutter_libs: ^0.6.5
  sqlite3: '>=1.8.0 <3.0.0'

dependency_overrides:
  vm_service: ^14.0.0

dev_dependencies:
  build_runner: ^2.4.4
  injectable_generator: ^2.1.6
  analyzer: ^6.7.0
  flutter_test:
    sdk: flutter


  flutter_lints: ^2.0.0
  custom_lint: ^0.6.4
  riverpod_lint: ^2.3.12
  riverpod_generator: ^2.2.1
  json_serializable: ^6.6.2
  drift_dev: ^2.7.0


flutter:
  uses-material-design: true
  generate: true

 
  assets:
    - assets/images/
    - assets/ca/
    - assets/docs/

My utils class to generate my database (I have 3 in total in my applications):
I'm performing database movements in isolates of my application, so I slightly modified the beginning of the method checkAndEncryptFile.

import 'dart:io';

import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:flutter/foundation.dart';
import 'package:flutter/services.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'package:sqlcipher_flutter_libs/sqlcipher_flutter_libs.dart';
import 'package:sqlite3/open.dart';
import 'package:sqlite3/sqlite3.dart';

class DatabaseUtils {
  static const _databaseKey = ‘xxxx’;

  static Future<void> setupSqlCipher() async {
    await applyWorkaroundToOpenSqlCipherOnOldAndroidVersions();
    open.overrideFor(OperatingSystem.android, openCipherOnAndroid);
  }

  static String escapeString(String source) {
    return source.replaceAll('\'', '\'\'');
  }

  static LazyDatabase openConnection(
      {required String decryptedName, required String encryptedName}) {
    // the LazyDatabase util lets us find the right location for the file async.
    return LazyDatabase(() async {
      // put the database file, called db.sqlite here, into the documents folder
      // for your app.
      if (kDebugMode) {
        print("open $encryptedName");
      }
      final dbFolder = await getApplicationDocumentsDirectory();
      final encryptedPath = p.join(dbFolder.path, encryptedName);
      final token = RootIsolateToken.instance;
      return NativeDatabase.createInBackground(
        File(encryptedPath),
        isolateSetup: () async {
          await checkAndEncryptFile(decryptedName, encryptedName, token);
        },
        setup: (rawDb) {
	  assert(rawDb.select('PRAGMA cipher_version;').isNotEmpty);
          // Chiffrement
          rawDb.execute("PRAGMA key = '$_databaseKey';");
          // Option recommandée
          rawDb.config.doubleQuotedStringLiterals = false;
        },
      );
    });
  }

  static checkAndEncryptFile(String decryptedName, String encryptedName,
      RootIsolateToken? token) async {

      await setupSqlCipher();
      if (token == null) return;
      BackgroundIsolateBinaryMessenger.ensureInitialized(token);
      final dbFolder = await getApplicationDocumentsDirectory();
      final encryptedPath = p.join(dbFolder.path, encryptedName);
      final decryptedPath = p.join(dbFolder.path, decryptedName);
      final existing = File(decryptedPath);
      final encrypted = File(encryptedPath);
      if (await existing.exists() && !await encrypted.exists()) {
        final plaintextDb = sqlite3.open(decryptedPath)
          ..execute("ATTACH DATABASE '${escapeString(encryptedPath)}' "
              "AS encrypted KEY '${escapeString(_databaseKey)}';")
          ..execute("SELECT sqlcipher_export('encrypted');");

// --------------- The crash occurs at this line. -------------- //

        // sqlcipher_export doesn't apply the user_version pragma used by drift
        // to implement migrations. The version of the encrypted database must
        // match the previous state.
        final userVersion =
            plaintextDb.select('PRAGMA user_version;').first.columnAt(0) as int;
        plaintextDb
          ..execute('PRAGMA encrypted.user_version = $userVersion;')
          ..execute('DETACH DATABASE encrypted;')
          ..dispose();

	assert(await encrypted.exists())
  }
}

Flutter :

[✓] Flutter (Channel stable, 3.29.2, on macOS 15.3.2 24D81 darwin-arm64, locale fr-FR)
[✓] Android toolchain - develop for Android devices (Android SDK version 34.0.0)
[!] Xcode - develop for iOS and macOS (Xcode 16.2)
    ! CocoaPods 1.15.2 out of date (1.16.2 is recommended).
        CocoaPods is a package manager for iOS or macOS platform code.
        Without CocoaPods, plugins will not work on iOS or macOS.
        For more info, see https://flutter.dev/to/platform-plugins
      To update CocoaPods, see https://guides.cocoapods.org/using/getting-started.html#updating-cocoapods
[✓] Chrome - develop for the web
[✓] Android Studio (version 2024.2)
[✓] VS Code (version 1.98.2)
[✓] Connected device (5 available)
[✓] Network resources

Best,

[simolus3]

Hi, thanks for the report! I'm also not sure what's going on here, the setup looks good to me. Can you try running the cipher_version pragma in checkAndEncryptFile too? Basically, I'm trying to find out whether SQLCipher was linked at all.

      if (await existing.exists() && !await encrypted.exists()) {
        final plaintextDb = sqlite3.open(decryptedPath);
        assert(rawDb.select('PRAGMA cipher_version;').isNotEmpty);
        plaintextDb
          ..execute("ATTACH DATABASE '${escapeString(encryptedPath)}' "
          // ...

If that's missing, a common problem may be that one of the other dependencies is linking the regular sqlite3 library from the system in some way (I know firebase might to do this). Does adding this to the podfile help?

[CharlotteJu]

Thanks for your feedback.

Indeed, your command* revealed that I didn't have SQLCiffer.

But the addition to the podFile worked !!

*If this code is ever used by someone else, just remember to replace rawDb with the plaintextDb, because we don't have the rawDb in the isolateSetup ;)