Sqlite-net: Improve support for SQLCipher

[praeclarum]

It's a common scenario to want to encrypt your database, so let's support it.

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

• [x] Add a -sqlcipher package that references SQLitePCLRaw.bundle_sqlcipher
• [x] REMOVED: Add SetKey() function to call PRAGMA KEY = 'text'
• [x] Verify that PRAGMA KEY = 'text' is run before WAL

[ericsink]

Things to be aware of:

1. As of 1.1.8, I have two different packages for SQLCipher. bundle_sqlcipher contains the builds maintained by Couchbase. bundle_zetetic contains no actual builds, but references the official builds from Zetetic, the company that maintains SQLCipher. Their builds are not freely distributable, even though the sqlcipher code is open source. In any case, some people prefer the more official builds with real support from Zetetic.

2. My pipeline of sqlcipher builds from Couchbase is changing. They are in the process of redoing their builds to use the crypto libraries from mbedTLS instead of OpenSSL. And the way they are managing and distributing those builds is changing. And because upcoming Couchbase Lite 2.0 is a major overhaul that no longer uses SQLitePCL.raw, their priorities are diverging from mine a little bit. I don't know for sure right now how bundle_sqlcipher will end up changing in the midst of this transition.

3. bundle_sqlcipher currently does not support UWP, because the Couchbase sqlcipher builds have not supported UWP. Requests for this are getting more common.

[praeclarum]

Ahhh, OK, so I'll re-consider whether or not to jump into that ring.

Really disappointed I have to admit though - thought all this had finally stabilized.

[ericsink]

Sorry for the hassle. If you want to go ahead and do the sqlcipher package, feel free. My goal is to fix up that bundle with a seamless transition. I just wanted you to be aware of the stuff shifting around.

[ndastur]

That is quite annoying :(

I have previously been able to make SQLite builds with encryption using the wxSqlite code base. @ericsink would you consider using that code base instead?

[ericsink]

@ndastur I don't know much about wxSqlite, which means I perceive it to be a greater risk.

[ericsink]

Minor update: After talking with the couchbase guy a bit more, I am more confident that I can incorporate new sqlcipher builds into bundle_sqlcipher, including UWP, in a very seamless transition.

[praeclarum]

@ericsink Yay!!!

[praeclarum]

I just release the new sqlite-net-sqlciper at 1.5.176-beta: https://www.nuget.org/packages/sqlite-net-sqlcipher/1.5.176-beta

Gonna test it out a bit before closing this. If you have the time, I would like to know if it works for any of you.

[wes566]

@praeclarum is the idea that I would reference sqlite-net-sqlcipher in my netstandard projects and app project? Or would I stick to using sqlite-net-pcl in netstandard projects and just sqlite-net-sqlcipher in my app project?

[wes566]

@praeclarum Ignore my previous question, figured out to reference sqlite-net-sqlcipher everywhere. I tried testing out your nuget package (1.5.176-beta) in a xamarin.mac project but it threw a DllNotFoundException when I called SetKey, I've opened https://github.com/ericsink/SQLitePCL.raw/issues/182 as the root issue seems to be in SqlitePCLraw.bundle_sqlcipher

[SamShanWang]

@praeclarum
The following statements exist in SQLiteConnection constructor.
if (openFlags.HasFlag (SQLiteOpenFlags.ReadWrite)) {
ExecuteScalar ("PRAGMA journal_mode=WAL");
}

This may result executing "PRAGMA journal_mode=WAL" before executing "PRAGMA key=pragmakey" (SetKey function).
1. It may create a non-encrypted sqlite database file after executing "PRAGMA journal_mode=WAL".
2. After executing "PRAGMA key=pragmakey", SQLite will think of the database file as an encrypted file. But in fact the database file is a non-encrypted file. Now it will throw exceptions like "SQLite.SQLiteException: file is encrypted or is not a database" when query or execute anything.

So, I suggest removing the sentence "ExecuteScalar ("PRAGMA journal_mode=WAL");" in SQLiteConnection constructor.
Otherwise the SetKey function may result SQLiteException exeception.

[JKennedy24]

@SamShanWang Have you seen this exception being thrown on a particular device / platform?

[JKennedy24]

I think I am getting the error described by @SamShanWang
The line in questions is: https://github.com/praeclarum/sqlite-net/blob/5603ee0c66276e2242f4bd5bf654afb9bfc91db9/src/SQLite.cs#L287

I am creating a SqliteConnection then running connection.Execute("PRAGMA Key = 'MyKey'"); on Android and every query after that I get:

SQLite.SQLiteException: file is encrypted or is not a database

I have managed to pull my database from the device and can confirm the database is in fact encrypted, so I'm not sure what is going on here

Can anyone else replicate?

[bijington]

@praeclarum I have attempted to use your beta version: sqlite-net-sqlciper at 1.5.176-beta however I am unable to reference the package from a PCL library, is this by design? I could in theory push my data access layer out to a .NET Standard library but I wanted to check first as it could be a fair amount of effort.

[bijington]

The reason I have looked to using the beta build is I am having issues relating to deploying a UWP build which I believe should be fixed as a result of this beta build? Basically when deploying it was complaining that SQLitePCLRaw.batteries_v2.dll is being copied from 2 places:

• SQLitePCLRaw.bundle_green
• SQLitePCLRaw.bundle_sqlcipher

Which I need sqlcipher for encryption and it seems that sqlite-net-pcl depends on the green bundle.

[JKennedy24]

@bijington see here on how to implement SqlCipher resolving conflicting dependencies:

https://github.com/Microsoft/AppCenter-SDK-DotNet/issues/569#issuecomment-356246613

[bijington]

@JKennedy24 thank you for the fast response. I can confirm that works. Although I did find that the bundle_green reference wasn't automatically added to the UWP project, adding it then fixed the issue.

I am fairly new to Xamarin but it does seem like there is a log of config involved.

Thanks again.

[praeclarum]

@bijington Two points: I am indeed moving away from PCL to favor .NET Standard. I know the tooling hasn't all caught up and this can be frustrating, but PCLs have been deprecated and I won't support them in the future. Second, you don't need bundle_green if using cipher - bundle_green is just a simple hack to get a bunch of packages pre-confugured.

I will update the README with the best way to use cipher.

Thanks for the feedback and sorry for the confusion!

[bijington]

@praeclarum Thanks for the response. No need to apologise I am fairly new so there has been plenty for me to learn :). I didn't choose to use bundle_green, it was the 'SQLitePCLRaw.bundle_sqlcipher' that I believe depends on bundle_green and brought it in. Does that mean by using your cipher package I can remove the need to use the pcl raw package as well?

[blimkemann]

While the cipher bundle runs fine on UWP, the app store rejects it since it uses encryption libraries that they don't support. Any news on this from @praeclarum, @ericsink or Couchbase?

[ericsink]

@blimkemann See ericsink/SQLitePCL.raw#193

[blimkemann]

@ericsink forgive my ignorance here, but that package is not directly installed in my project - I only see sqlite-net-sqlcipher. Do I need to replace that with sqlite-net and then YOUR sqlcipher? The relationship between SQL Cipher and SQLite-net has always confused me...

[ericsink]

No worries. :-)

The SQLitePCLRaw.lib.sqlcipher.windows is not directly installed in your project, but it gets brought in as dependency.

So, leave all your other packages the same, and just add SQLitePCLRaw.lib.sqlcipher.windows, being careful to specify version 1.1.10-pre20180130092526. It looks like you're adding a new package, but what you are really doing is updating it.

[blimkemann]

@ericsink thank you very much! that makes sense.
For consistency should I use that same SQLCipher in my iOS and Android projects as well? Or is there no need since they already work.
Finally, since I added the SQLitePCLRaw.lib.sqlcipher.windows NuGet, does that replace what was being used before so that it will pass the Microsoft Store?

[ericsink]

You should not need to change anything for iOS and Android if those are already working. That SQLitePCLRaw.lib.sqlcipher.windows package is windows-specific anyway.

And yes, your project should pass the store verification now, but please let me know if it does not. Your post above doesn't specify exactly what error messages you are seeing and how you got them, so I made an assumption that you are experiencing the same problem as seen in ericsink/SQLitePCL.raw#193, but if that assumption was wrong, well, that package update won't help. :-)

[blimkemann]

Yes, I am getting those errors…

Thank you for the assistance!

From: Eric Sink [mailto:[email protected]]
Sent: Thursday, February 1, 2018 1:56 PM
To: praeclarum/sqlite-net [e-mail]
Cc: Brian Limkemann [e-mail]; Mention [e-mail]
Subject: Re: [praeclarum/sqlite-net] Improve support for SQLCipher (#597)

You should not need to change anything for iOS and Android if those are already working. That SQLitePCLRaw.lib.sqlcipher.windows package is windows-specific anyway.

And yes, your project should pass the store verification now, but please let me know if it does not. Your post above doesn't specify exactly what error messages you are seeing and how you got them, so I made an assumption that you are experiencing the same problem as seen in ericsink/SQLitePCL.raw#193https://github.com/ericsink/SQLitePCL.raw/issues/193, but if that assumption was wrong, well, that package update won't help. :-)

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/praeclarum/sqlite-net/issues/597#issuecomment-362365870, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AJ6LVYYRMnePFHDY6ywHXY0I2hO2yfO7ks5tQgiugaJpZM4OuU97.

[dompham]

@JKennedy24 I have the same thing, it thinks that the DB is encrypted, but I can pull it off and see full contents. Also on the beta sqlcipher. Any ideas @praeclarum ?

Thanks!

[ericsink]

@dompham I would suggest opening a new issue and including complete details about the problem you are having.

[dompham]

I'll do that :)

[JKennedy24]

(sorry for posting the reply on this thread)
@dompham as soon as you run Pragma Key it will return the file is encrypted or not a database even if the file is not encrypted.

My problem was libraries not correctly installed to my projects. If you run Pragma Cipher_Version it will return what version of the cipher you are using, if it returns null or empty string (I can't remember which) it means you aren't using sqlcipher and your libs haven't been installed correctly

[praeclarum]

@SamShanWang thank you I'm working on a fix now.

[rraallvv]

The encrypted database opens just fine following the steps in this guide:

How to encrypt a plaintext SQLite database to use SQLCipher (and avoid “file is encrypted or is not a database” errors)

I've tested iOS and Android, both with version 1.5.176-beta and .Net standard 2.0

Keep up the good work!

[sjlombardo]

Hello @praeclarum - Thanks for all your work on sqlite-net. I was wondering if you plan to release the sqlite-net-base package available on NuGet in the near future? We are very much hoping to use sqlite-net-base as an API for interfacing with SQLCipher using SQLitePCLRaw.bundle_zetetic (which @ericsink recently published) for an upcoming version of SQLCipher. It would be great if we could depend on a non-prerelease version of sqlite-net-base.

[praeclarum]

I have removed the SetKey functions in favor of providing the key to the constructor. This means that the pragmas will execute in the right order.

[sjlombardo]

Hello @praeclarum - Providing the key in the constructor looks like a good option to ensure that pragma key is called prior to enabling WAL mode. However, I wanted to mention that are also other pragma settings that are frequently used with SQLCipher. These would also need to be executed before setting WAL mode. These pragmas can control numerous SQLCipher settings including key derivation, HMAC settings, page sizes, performance optimizations, data migrations, etc. In order accommodate this common case, what would you think about providing an "initialization" callback that could be used to execute statements like that prior to enabling WAL? Or, alternately, deferring the enablement of WAL mode until immediately before connection use?

Also, I would really like to get some feedback from you regarding the packaging. As @ericsink mentioned earlier in the thread there are different ways to include SQLCipher. One of those is SQLitePCLRaw.bundle_sqlcipher, but Zetetic (the maintainers of SQLCipher, for whom I work) also provide commercially supported SQLCipher packages, specialized builds (e.g. FIPS 140-2 validated cryptographic modules) and other customizations which work in conjunction with SQLitePCLRaw.bundle_zetetic. Would you consider simply providing this interface change as part of sqlite-net-base (so that users can bring in their own dependency on either bundle_sqlcipher or bundle_zetetic), instead of having a completely separate sqlite-net-sqlcipher package? This would ultimately provide so much more flexibility for users since they could choose whichever package is most appropriate for their use (or even provide their own packages if they have special needs).

[praeclarum]

I have moved the key to the constructor in 1.5.

@sjlombardo Yes I'm open to changing the constructor interface to help you out. Would you please open a new issue with your proposal?

[Samrak]

Hello,
Unfortunately, there is not enough information in this area. Which algorithm is used to ensure database encryption? aes256 , aes128 ?

[developernotes]

Hi @Samrak

SQLCipher uses 256-bit AES in CBC mode by default. With our latest release of 4.0.0 there are a large number of compatibility changes to to be aware of.