iOS + SQLCipher = SQLite.SQLiteException: not an error

[elvenprogrammer]

Hello everyone,
I'm having an issue when trying to enable SQLCipher in iOS (Android seems to be working fine).
Probably I'm missing some obvious step.

Steps to reproduce:

1. Create a new Xamarin project with Visual Studio 2019 Enterprise (latest version)
2. Add reference to both sqlite-net-pcl and sqlite-net-sqlcipher (1.7.335) on both .net standard common project and iOS project
3. Add the following in App.xaml.cs (.net standard common project)

        protected override void OnStart()
        {
            try
            {
                var databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "dh-test.db3");
                File.Delete(databasePath);
                //var options = new SQLiteConnectionString(databasePath, true);
                var options = new SQLiteConnectionString(databasePath, true, key: "password");
                //var options = new SQLiteConnectionString(databasePath, true, key: "password", postKeyAction: c => { c.Execute("PRAGMA cipher_compatibility = 3"); });

                var encryptedDb = new SQLiteConnection(options);

                encryptedDb.Execute("create table Stock(Symbol varchar(100) not null)");
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString());
            }
        }

4. Run on iPhone 12 Pro (iOS 14.4.2) with Hot restart
5. Stare at the brilliant "not an error" exception threw by SQLiteConnection

SQLite.SQLiteException: not an error
  at SQLite.SQLiteCommand.ExecuteNonQuery () <0x103847570 + 0x00142> in <b7f631d5fc1944c0bd63336596c11967>:0 
  at SQLite.SQLiteConnection.Execute (System.String query, System.Object[] args) <0x103830280 + 0x0006c> in <b7f631d5fc1944c0bd63336596c11967>:0 
  at SQLite.SQLiteConnection.SetKey (System.String key) <0x10382fb38 + 0x0003c> in <b7f631d5fc1944c0bd63336596c11967>:0 
  at SQLite.SQLiteConnection..ctor (SQLite.SQLiteConnectionString connectionString) <0x103840d08 + 0x001e2> in <b7f631d5fc1944c0bd63336596c11967>:0 
  at SqlCipher.App.OnStart () [0x0002b] in C:\Users\EugenioFavalli\source\repos\SqlCipher\SqlCipher\SqlCipher\App.xaml.cs:33 }

Any help is appreciated, I can share the project source if needed.

Thanks in advance

[elvenprogrammer]

@praeclarum or @sjlombardo any hint on this issue?

[sjlombardo]

Hello @elvenprogrammer:

1. you should not add both sqlite-net-pcl and sqlite-net-sqlcipher, use one or the other.
2. Capture the result of PRAGMA cipher_version. If it returns 0 rows, you are not using SQLCipher (see SQLiteConnection throws error in ctor on iOS 14 #986 (comment))
3. If it returns the expected result and you have a dependency on SQLitePCL.raw 2.0.5, use this patch use ExecuteScalar<string> to handle 'ok' result from PRAGMA key #1026

[elvenprogrammer]

Hi @sjlombardo thanks for the precise answer.

you should not add both sqlite-net-pcl and sqlite-net-sqlcipher, use one or the other.

Makes sense, I just kept slite-net-sqlcipher

Capture the result of PRAGMA cipher_version. If it returns 0 rows, you are not using SQLCipher

Not sure, but I tried:
var opt = new SQLiteConnectionString(databasePath, true, key: "password"); var db = new SQLiteConnection(opt); var version = db.ExecuteScalar<string>("PRAGMA cipher_version");

which again throws the not an error error. If I remove the key than I get a null which I think is to be expected?

If it returns the expected result and you have a dependency on SQLitePCL.raw 2.0.5, use this patch

Not sure again, as far as I can understand it seems to me slite-net-sqlcipher is bringing in SQLitePCL.raw.bundle_e_sqlcipher 2.0.3

I don't know if it makes sense but I tried to isolate more the problem by including the packages only in the iOS project.
I tried with both sqlite-net-sqlcipher and SQLitePCL.raw.bundle_e_sqlcipher, or adding them alone one at a time.

Tested quite a lot of combinations inside FinishedLaunching, without success, I think I'm doing something really stupid, but I can't figure out what.
Any example or better a quickstart example would be really appreciated.

public override bool FinishedLaunching(UIApplication app, NSDictionary options)
        {
            SQLitePCL.Batteries_V2.Init();

            try
            {
                var databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "dh-test.db3");
                if (File.Exists(databasePath))
                    File.Delete(databasePath);
                //var opt = new SQLiteConnectionString(databasePath, true);
                var opt = new SQLiteConnectionString(databasePath, true, key: "password");
                //var opt = new SQLiteConnectionString(databasePath, true, key: "password", postKeyAction: c => { c.Execute("PRAGMA cipher_compatibility = 3"); });

                var db = new SQLiteConnection(opt);

                //db.Execute("create table Stock(Symbol varchar(100) not null)");
                var version = db.ExecuteScalar<string>("PRAGMA cipher_version");
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString());
            }

            global::Xamarin.Forms.Forms.Init();
            LoadApplication(new App());

            return base.FinishedLaunching(app, options);
        }

[sjlombardo]

Hello @elvenprogrammer

If I remove the key than I get a null which I think is to be expected?

That is actually not expected. Regardless of whether a key is provided, if the SQLCipher is properly linked and loaded at runtime then PRAGMA cipher_version will return a version string. The fact that it is not indicates that SQLCipher is not properly linked in your app.

I don't think your code is at fault, rather there is likely something in your project introducing another non-SQLCipher SQLite library (the system libsqlite). Are you using any other nuget packages that could be introducing a transitive dependency on sqlite-net-pcl?

[elvenprogrammer]

Hi @sjlombardo
this is all I get:

And the repro project attached for you convenience.

SqlCipher.zip