How to consume SQLCipher using Xamarin Forms Template

[jbravobr]

Reading the wiki from ericsink for SQLitePCL.raw, it's says that I have to install the sqlite-net-pcl only for the PCL project, for the App project (iOS and Android) I have to install only the SQLitePCLRaw.bundle_sqlcipher, by doing that my class that's servers as Dependency Service for my PCL to initiate the connection with the sqlite database file on Android App can't find the correct assembly, for sqlite-net-pcl.

[ericsink]

You might need to move that bit of code into a separate project. For example, create a Droid library project, move the droid dependency service stuff in there, reference it from your Droid app project, and reference sqlite-net-pcl from that droid library project.

[jbravobr]

Hey @ericsink ! Thanks for the quick answer! But into the Droid Library project should I use sql-net-pcl or just the SQLitePCLRaw.bundle_sqlcipher? Or should I use the sql-net-pcl in the Droid Library and the SQLitePCLRaw.bundle_sqlcipher in the Droid App?

[ericsink]

I think in the droid library you will need to use sqlite-net-pcl

and in the droid app, use bundle_sqlcipher (and not sqlite-net-pcl)

Let me know if this works. I'm not 100% sure about it.

[jbravobr]

@ericsink trying right now!

[jbravobr]

Didn't work @ericsink. The Droid App project claim for the SQLite-net assembly

[ericsink]

"The Droid App project claim for the SQLite-net assembly"

I don't understand what this means.

[acidbless]

I'm sorry if my question is an off topic but how to enable sqlcipher features in my project? I mean, where I have to enter a password when creating / opening of the database?

[ericsink]

@acidbless https://www.zetetic.net/sqlcipher/sqlcipher-api/#key

[acidbless]

Oh, thanks Eric!

[supratimpatra]

Hi @acidbless , This might help you.
https://github.com/supratimpatra/Sqlcipher_sqlite-net-pcl_Xamarin.Forms.git

I have tested in Android device & encryption is working properly. Sorry I could not add separate unit test.I am still wondering how to write unit test executable from device. Any help in this regard would be very helpful.

** if any chance if app crashes on launch in android emulator, please use real device to test.

[mawarnes]

@supratimpatra unfortunately that link is broken, I'm using the sqlite-net-pcl plugin for a xamarin forms cross platform app, but i'm totally stumped as to how to add sqlcipher?

What level of encryption does it implement? I must have missed it on the docs?

Do I need to add the SQLitePCLRaw.bundle_sqlcipher bundle to all the projects or just the ios/android and uwp ones? any starter for 10 greatly appreciated :-)

[ericsink]

Have you seen this?

https://github.com/ericsink/SQLitePCL.raw/wiki/How-to-use-SQLCipher-with-SQLite-net

[mawarnes]

@ericsink thanks for the quick reply.
yes I did see that, so therefore I need to add SQLitePCLRaw.bundle_sqlcipher to each of my app projects (ios, and android and UWP)....but then what, where do I initialise the db as encrypted....sorry to be so dumb.

Currently I initialise the db in the pcl library, using a new SQLiteConnection. This uses the dependency service to locate the target db file as follows
DBconn = new SQLiteConnection(DependencyService.Get<IFileHelper> ().GetLocalFilePath("mydbfile.db3"));
Is it possible to add in the necessary extra code to encrypt the data here?

Also I couldn't find any docs on the type of encryption used?

[ericsink]

Oh, yeah...

To actually use the encryption features, you'll need to use PRAGMA key. See this link:

https://www.zetetic.net/sqlcipher/sqlcipher-api/

[mawarnes]

okay so I've added reference sqlitepclraw.bundle_sqlcipher to each of my app projects (ios, and android and UWP).

now I can just go ahead and use the following code
DBconn = new SQLiteConnection(DependencyService.Get<IFileHelper> ().GetLocalFilePath("mydbfile.db3")); DBconn.Query<int>("PRAGMA key=pragmakey");
and every subsequent operation to the database will be encrytped?....seems to easy?!!...do you know if there is a way to independantly validate the data is encrypted?

[ericsink]

I haven't used sqlcipher myself in a while, so my answers might be wrong-ish around the edges, but...

Yeah, it's basically that easy.

To confirm that encryption is really happening, I would grab the sqlite file out of your device or emulator, copy it to something like a unix system, and open it with regular sqlite. It should be unreadable. For extra fun, run Unix 'strings' on it and see that none of the strings you inserted as data are present in the output.

[mawarnes]

unbelievable!!! @ericsink you sir are a star!!

so I just ran it and tried opening the db file with DB browser for sqlite....no dice, invalid file format....

then deleted and re-ran and tried to open the database, this time without encryption, and I could see all the data.

....really realy impressed, thx very much for your work.

just to re-cap in case it helps anyone else

add the sqlite_net_pcl package to the PCL project
add the sqlitepclraw.bundle_sqlcipher to each app project(ios, and android and UWP).

add SQLitePCL.Batteries_V2.Init(); to the startup in each app project, i.e. android in MainActivity.cs

create a connection in you pcl...probably using the dependency service
DBconn = new SQLiteConnection(DependencyService.Get<IFileHelper().GetLocalFilePath("mydbfile.db3"));

now the crucial part which keys the database, using a PRAGMA, see docs here https://www.zetetic.net/sqlcipher/sqlcipher-api/

DBconn.Query<int>("PRAGMA key=pragmakey");

and every subsequent operation to the database will be encrytped, by my calculations with default 256bit AES cbc

[D4rkC1own187]

@mawarnes
So I added sqlite_net_pcl (by Frank Kreuger) to my PCL project and

added sqlitepclraw.bundle_sqlcipher to my app projects and

added SQLitePCL.Batteries.Init(); could not find SQLitePCL.Batteries_V2.Init(); and

In my PCL project:
created connection like so database = new SQLiteAsyncConnection(DependencyService.Get<IFileHelper>().GetLocalFilePath("Sample.db3"));
database.QueryAsync<int>("PRAGMA key=pragmakey");

but whenever I try to open in DB Browser for SQL Lite I get "Error changing data: attempt to write a readonly database and in SQLite Studio I try top open it as SQLCipher with the pass as "pragmakey" I get "could not add database"

Whenever I try to read from it I get:
"malformed database schema (<table name) - unrecognized token: ""�hg�cF�7[�O�?�>M�̈�u�ge�1���!�:�1�""

Edit:
This method is only for when your connection is not async. Having it async causes the data to return chunk value when trying to retrieve it. Changing the connection to SQLiteConnection solved my issue. Anyway this can work with SQLiteAsyncConnection

[dpusarla]

Michael,

Will you be able to post the sample code where Sqlcipher worked with Xamarin forms?

[supratimpatra]

Sorry @mawarnes I was not actively following this thread. Here is the repo with updated SQLCipher package. https://github.com/supratimpatra/Sqlcipher_sqlite-net-pcl_Xamarin.Forms.git
It will now only work with iOS project. I am frustratingly struggling with Android build to work as Xamarin Forms got updated & it's now won't build at all..

[praeclarum]

I am working on a better nuget package to make this easier in #597

For now, you can use the beta nuget package sqlite-net-base to simplify all of this.

[arjunkr92]

unbelievable!!! @ericsink you sir are a star!!

so I just ran it and tried opening the db file with DB browser for sqlite....no dice, invalid file format....

then deleted and re-ran and tried to open the database, this time without encryption, and I could see all the data.

....really realy impressed, thx very much for your work.

just to re-cap in case it helps anyone else

add the sqlite_net_pcl package to the PCL project
add the sqlitepclraw.bundle_sqlcipher to each app project(ios, and android and UWP).

add SQLitePCL.Batteries_V2.Init(); to the startup in each app project, i.e. android in MainActivity.cs

create a connection in you pcl...probably using the dependency service
DBconn = new SQLiteConnection(DependencyService.Get<IFileHelper().GetLocalFilePath("mydbfile.db3"));

now the crucial part which keys the database, using a PRAGMA, see docs here https://www.zetetic.net/sqlcipher/sqlcipher-api/

DBconn.Query<int>("PRAGMA key=pragmakey");

and every subsequent operation to the database will be encrytped, by my calculations with default 256bit AES cbc

I followed as suggested, Added sqlite-net pcl to my PCL project and added sqlitepclraw.bundle_sqlcipher to my iOS project, initialised "SQLitePCL.Batteries_V2.Init();" in appdelegate.cs, below is my code to create DB connection for the first time in PCL project.

public SQLiteConnection GetConnection(string path)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection(path, true);
                var temp = conn.Query<int>("PRAGMA key=123");
                return conn;
            }
            catch (SQLiteException)
            {
                return null;
            }

        }

But when I run the app, it can't retrieve any data from the database, results are 0. I pulled the database from the device to check if it is encrypted, opened it in "DB Browser for SQLite" on Mac and it does not ask for any password just opens fine.

var temp = conn.Query<int>("PRAGMA key=123");
This line returns the value 0, not sure where I'm going wrong.

[ericsink]

I think you need to give PRAGMA key a string literal:

PRAGMA key='123'

[arjunkr92]

I think you need to give PRAGMA key a string literal:

PRAGMA key='123'

Sorry I tried that, but it doesn't seems to work! I'm also using SQLiteNetExtensions in PCL project, The SQLiteNetExtensions has dependency on SQLite.Net-PCL (by Øystein Krog,Frank Krueger,Tim Heuer), I've sqqlite-net-pcl by @praeclarum installed, does this create any conflicts?

Edit:
Latest version of SQLiteNetExtensions (v2.1.0) have dependency on sqlite-net-pcl instead of SQLite.Net-PCL. But I'm unable to install it into my PCL project, it gives following error:

Could not install package 'SQLiteNetExtensions 2.1.0'. You are trying to install this package into a project that targets '.NETPortable,Version=v4.5,Profile=Profile259', but the package does not contain any assembly references or content files that are compatible with that framework. For more information, contact the package author.

[arjunkr92]

Ok, After so many trial and errors I got this working, here is some background about my App.
I had this Xamarin PCL project which was developed a year ago, The project had PCL and iOS parts.
Now PCL has become obsolete and the world has moved on to .Net Standard.

Step1: I used tutorial by James Montemagno to convert my existing PCL project into .NET Standard
https://montemagno.com/how-to-convert-a-pcl-library-to-net-standard-and-keep-git-history/

Step 2: Removed all SQLite related dependencies from my project, restarted Visual Studio.

Step 3: Now added sqlite-net-sqlcipher v1.6.258-beta into both iOS and Main projects. Here the version is very much important, better install v1.6.258-beta or above.

Step 4: This is optional if you are using SQLiteNetExtenions by twincoders for ORM, kindly use SQLiteNetCipherExtensions by same developers, I'm using v2.1.0

Step 5: Make sure SQLitePCLRaw.bundle_sqlcipher is installed in platform-specific projects (Android or iOS)

Step 6: Initialize SQLitePCL.Batteries_V2.Init(); in main pages of platform specific projects.
in iOS put it inside appdelegate.cs

Now all set, you can now open already encrypted database:
var options = new SQLiteConnectionString(fullPath, true, key: "123"); SQLiteConnection conn = new SQLiteConnection(options);

Or encrypt a plaintext database
SQLiteConnection conn = new SQLiteConnection(fullPath); conn.Execute("ATTACH DATABASE '" + path + "enc.db' AS encrypted KEY '123';"); conn.Execute("SELECT sqlcipher_export('encrypted');"); conn.Execute("DETACH DATABASE encrypted;"); conn.Close();