iOS configuration issues

[elvenprogrammer]

Hi @ericsink I'm having issues on configuring a brand new VS2019 Xamarin template project. To isolate the problem I'm now referencing SQLitePCL.raw only in the iOS project and trying to bootstrap in AppDelegate.cs

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, key: "password");

                var db = new SQLiteConnection(opt);

                var version = db.ExecuteScalar<string>("PRAGMA cipher_version");
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString());
            }

I've tried both the bundles: green and e_sqlite3 without luck.

It seems the problem is when sqlite-net tries to set the key (without setting the key in the connection string everything works fine and on an android or console everything works as expected even with the key parameter).

With 2.0.4 I get a result of SQLite.SQLite3+Result.Row when executing pragma key = 'password' which then return a not an error error. I was expecting the issue to lie within the green bundle and that native iOS SQLite wa behaving differently, but now I'm completely lost.

And I'm not even near to add sqlcipher to the equation.

Do you have a working by any chance a working project/example to share?

[ericsink]

If you're looking for a sample showing how to use a key when not using sqlcipher, I don't have that, because you shouldn't be doing that. :-)

If I gave you a sample project that worked fine, it would do so by omitting the key from the connection string but you are already know that.

One other tidbit: I seem to recall that there was a recent change involving the question of whether pragma key returns a row or not, and how some wrapper handles that. I don't remember the details, but your situation rings that bell in my mind.

[sjlombardo]

@elvenprogrammer this is the same issue you reported in https://github.com/praeclarum/sqlite-net/issues/1041. When you provide the key without using SQLCipher it is being picked up by the undocumented encryption function included in recent versions of iOS, per https://github.com/praeclarum/sqlite-net/issues/986#issuecomment-697967001. PRAGMA key is returning "ok" in the result set. There does exist a PR to account for that (https://github.com/praeclarum/sqlite-net/pull/1026). You can use it, however, you would then be using an undefined and unsupported encryption method at that point.

[elvenprogrammer]

@ericsink , @sjlombardo thanks for feedbacks

If you're looking for a sample showing how to use a key when not using sqlcipher, I don't have that, because you shouldn't be doing that. :-)

Indeed and it's not my aim, just trying to troubleshoot top-down. I started with the full solution described here -> not an error

It's worth nothing saying that the same code on a .net core 3.1 console application or android application works fine :)

If I gave you a sample project that worked fine, it would do so by omitting the key from the connection string but you are already know that.

The context was meant to be a sample iOS project working with SQLCipher :)

One other tidbit: I seem to recall that there was a recent change involving the question of whether pragma key returns a row or not, and how some wrapper handles that. I don't remember the details, but your situation rings that bell in my mind.

I think I've read it as well somewhere, anyway I managed the correct return type. Now no more not an error errors, but PRAGMA cipher_version return null

And as @sjlombardo reported on the other issue:

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.

So again I'm probably doing something really stupid on the setup of the project, but I can't really find out what. This is my setup as of now:

[elvenprogrammer]

I'm going round in circles. I eliminated any sqlite-net from the equation.

I tried with bundle_e_sqlcipher or with the single packages.

I tried with SQLitePCLRaw.lib.e_sqlcipher or with SQLitePCLRaw.lib.e_sqlcipher.ios

And now I get: System.IO.FileNotFoundException: Could not load file or assembly 'SQLitePCLRaw.lib.sqlcipher

The best result I could obtain is getting a null with the code below:

//SQLitePCL.Batteries.Init();
                SQLitePCL.Batteries_V2.Init();
                //SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_e_sqlcipher());

                var databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "dh-test.db3");
                if (File.Exists(databasePath))
                    File.Delete(databasePath);

                sqlite3 handle;
                SQLitePCL.raw.sqlite3_open(databasePath, out handle);
                sqlite3_stmt stmt;
                SQLitePCL.raw.sqlite3_prepare_v2(handle, "PRAGMA cipher_version", out stmt);
                int r = SQLitePCL.raw.sqlite3_step(stmt);

                if (r == 100)
                {
                    var version = SQLitePCL.raw.sqlite3_column_text(stmt, 0).utf8_to_string();
                    Debug.WriteLine($"cipher_version: {version}");
                }

Any help is really appreciated.

[ericsink]

First reminder is that the e_sqlcipher builds are unofficial and unsupported.

Second reminder is that the best way to get support for SQLCipher is to buy official supported builds from Zetetic (with whom I have no business affiliation).

That said, I would definitely suggest staying with bundle_e_sqlcipher, not the individual packages. For example, if you're on iOS with individual packages, you need provider_internal, not provider_e_sqlcipher. And the initialization steps are different. The bundle handles all that for you.

Clearly, something was going wrong for you with the bundle, and that would need to be figured out, but trying to do the work of the bundle yourself is definitely the more difficult path.

[elvenprogrammer]

First reminder is that the e_sqlcipher builds are unofficial and unsupported.

Yep, noted :)

Second reminder is that the best way to get support for SQLCipher is to buy official supported builds from Zetetic (with whom I have no business affiliation).

Indeed that could be the preferred option, once we are sure we will reach the production stage. In the meanwhile we're trying low budget discoveries.

That said, I would definitely suggest staying with bundle_e_sqlcipher, not the individual packages. For example, if you're on iOS with individual packages, you need provider_internal, not provider_e_sqlcipher. And the initialization steps are different. The bundle handles all that for you.

That was my starting point. In a console app or a Xamarin Forms Android project it works as expected and I get a 4.2.0-community cipher_version. In a Xamarin Forms iOS project I do get a null (Done) cipher_version. My first guess was I misconfigured something and that the project was loading a non cipher version of the lib. That's why I asked for an example to make sure I configured everything correctly and I'm not missing a piece. In the meanwhile I switched to the individual packages to try to make a (poor) attempt to force the correct lib, but I must admit I'm missing some understanding of the internal working.

Clearly, something was going wrong for you with the bundle, and that would need to be figured out, but trying to do the work of the bundle yourself is definitely the more difficult path.

This is where I could use some help for a confirmation of the configuration of the project (I've tried to look for some other Xamarin Forms project using the packages, but without luck) or what else to investigate to troubleshoot my issue. If someone else is using them without problems then it must be something on my side and should be easy to find on a new template VS19 Xamarin Forms project.

Really sorry to flood your issue tracker I just need to close my investigation task.

[elvenprogrammer]

Some updates, still on the issue.

First attempt I added the following according to SQLCipher documentation:

SQLitePCL.lib.embedded.Init(); /* required on iOS to link SQLCipher library */
SQLitePCL.Batteries_V2.Init();

No changes, here's what gets loaded.

Loaded assembly: /private/var/mobile/Containers/Data/Application/F974F4E6-28F1-48C4-9E65-C9B4F46E6D0F/Documents/SqlCipher.iOS.content/SQLitePCLRaw.core.dll [External] Loaded assembly: /private/var/mobile/Containers/Data/Application/F974F4E6-28F1-48C4-9E65-C9B4F46E6D0F/Documents/SqlCipher.iOS.content/SQLitePCLRaw.batteries_v2.dll [External] Loaded assembly: /private/var/mobile/Containers/Data/Application/F974F4E6-28F1-48C4-9E65-C9B4F46E6D0F/Documents/SqlCipher.iOS.content/SQLitePCLRaw.provider.internal.dll [External] Loaded assembly: /private/var/mobile/Containers/Data/Application/F974F4E6-28F1-48C4-9E65-C9B4F46E6D0F/Documents/SqlCipher.iOS.content/SqlCipher.dll Loaded assembly: /private/var/mobile/Containers/Data/Application/F974F4E6-28F1-48C4-9E65-C9B4F46E6D0F/Documents/SqlCipher.iOS.content/SQLitePCLRaw.lib.e_sqlcipher.ios.dll [External]

So I followed your suggestion and directed my attention to official zetetic builds. Still the same error.

Loaded assembly: /private/var/mobile/Containers/Data/Application/89EC1602-07E1-4032-B223-A5337AB5C927/Documents/SqlCipher.iOS.content/SQLitePCLRaw.core.dll [External] Loaded assembly: /private/var/mobile/Containers/Data/Application/89EC1602-07E1-4032-B223-A5337AB5C927/Documents/SqlCipher.iOS.content/SQLitePCLRaw.batteries_v2.dll [External] Loaded assembly: /private/var/mobile/Containers/Data/Application/89EC1602-07E1-4032-B223-A5337AB5C927/Documents/SqlCipher.iOS.content/SQLitePCLRaw.lib.sqlcipher.dll [External] Loaded assembly: /private/var/mobile/Containers/Data/Application/30991C7C-CEC9-47AF-BC8C-4D2537415D69/Documents/SqlCipher.iOS.content/SQLite-net.dll [External] Loaded assembly: /private/var/mobile/Containers/Data/Application/30991C7C-CEC9-47AF-BC8C-4D2537415D69/Documents/SqlCipher.iOS.content/SQLitePCLRaw.provider.internal.dll [External] Loaded assembly: /private/var/mobile/Containers/Data/Application/30991C7C-CEC9-47AF-BC8C-4D2537415D69/Documents/SqlCipher.iOS.content/SqlCipher.dll

Can you spot something wrong?

[sjlombardo]

@elvenprogrammer if you are using official packages from Zetetic please contact us privately at support@zetetic.net.