sqlcipher support?

[emmanueltouzery]

sqlcipher: https://github.com/sqlcipher/sqlcipher has the sqlite C/C++ API but adds encryption support. To enable encryption support you must set a PRAGMA before opening a file. I tried to build persistent against sqlcipher, by modifying persistent-sqlite.cabal, adding a new flag:

+flag sqlcipher
+  description: Build against the system-wide sqlcipher library
+  default: False

and lower:

+    else if flag(sqlcipher)
+        extra-libraries: sqlcipher

Removing the sqlite-devel package, then pointing to that app using extra-lib-dirs & extra-include-dirs:

cabal install persistent-sqlite -fsqlcipher --extra-lib-dirs=/home/emmanuel/programs/sqlcipher-3.2.0-install/lib/ --extra-include-dirs=/home/emmanuel/programs/sqlcipher-3.2.0-install/include/

then when I open I do:

main = runSqlite "test1.db" $ do
    rawExecute "PRAGMA key = 'passphrase'" []
    ...

However the decryption doesn't work, it fails as though I did not set the password or as though it was linking against the normal sqlite. I assume it's the latter.

Well, I'd be happy if you have hints on how to make it work, and if I can make it work, I wonder whether it could be included upstream in persistent-sqlite. It's not that many projects that require such a feature, and I'm not sure how it would be handled: if it's a build flag, I don't think an application could depend on persistent-sqlcipher. It would have to require that the user first installs persistent-sqlite manually with the right flag. And if the app is paranoid about data storage, it would probably need a runtime way to check whether it was in fact built against a persistent-sqlite package built against sqlchipher and not the ordinary sqlite (otherwise all data would be stored as plain text, a disaster!). So would that mean a persistent-sqlite fork? Any idea?

[emmanueltouzery]

OK, I made it work locally.

I realized cabal doesn't support "else if"; I now have:

     if flag(systemlib)
         extra-libraries: sqlite3
-    else
+    if flag(sqlcipher)
+        extra-libraries: sqlcipher
+    if !flag(systemlib) && !flag(sqlcipher)
         c-sources:   cbits/sqlite3.c

Built with -fsqlcipher and it works. But still remains the questions that I opened, whether it makes sense to enable that directly in persistent-sqlite and in which way. For my personal needs I guess I can edit the persistent-sqlite cabal file and be disciplined when installing. It will make my app very difficult to install for others but then I'm not expecting thousands of downloads for a app I make in my free time for my own needs. If sqlcipher is a rare requirement, it might be the correct way to ignore it.

[meteficha]

IMHO, this should be a separate package, and not a flag, at least in theory.

In practice though, most of the code is common, so copy&paste is unfeasible. Extracting a common core looks difficult as well since FFI is involved.

A dirty solution that may work is using CPP for the few differences in Haskell code between both libraries and using a symlink for the rest. I don't know if cabal sdist would be able to handle the symlinks, though.

Just my 2 cents :).

[snoyberg]

I agree with @meteficha abouta separate package being ideal, but I'm optimistic that doing so might be trivial. What would happen if you have a persistent-sqlcipher package that adds sqlcipher as an extra lib and provides a function that automatically calls the PRAGMA in question? Can you try giving that a shot?

[emmanueltouzery]

Hmm @snoyberg I'm not sure exactly how to go forward with it. So I built persistent-sqlite by changing the .cabal as described and calling the pragma manually, and it works. I could create a persistent-sqlcipher .cabal file calling unconditionally sqlcipher as an extra lib, add the function for the pragma with CPP in the haskell source, and it would work. But then I don't know where to put that cabal file. I was googling the topic and playing a bit yesterday, and found that thread: https://www.haskell.org/pipermail/libraries/2011-January/015608.html

Bottom line is that you can't put two .cabal files in a folder. And it seems it will never be supported because then you need two Setup.hs files and so on. And two dist/ folders and so on, I should think. So I'm not sure exactly how to tackle it. I we could have persistent-sqlite.cabal then persistent-sqlcipher._cabal and manual rename or some install.sh script or some Setup.hs magic? But we must probably clean in-between builds. So I can't think of a clean solution for now. I you have an idea I'll be happy to give it a shot...

[snoyberg]

I wasn't recommending having two cabal files in the same package, but rather having a separate persistent-sqlcipher package that depends on the persistent-sqlite package.

[emmanueltouzery]

hmm but then the usual persistent-sqlite would be built agains the regular sqlite? sqlcipher is shipped basically as a sqlite fork as I understand, not as a sqlite plugin per se (although I didn't really dig).

For instance in this commit: https://github.com/sqlcipher/sqlcipher/commit/801e036cd83c3605ffef32c2d1234c7231cb94f3

They merge SQLite upstream changes in their tree. So, yes the pure haskell part of persistent-sqlite would be reused in persistent-sqlcipher but the foreign C calls would have to be copy-pasted because the persistent-sqlite ones would be linked to the wrong C library?

But then I don't know the persistent architecture and I basically don't know the sqlcipher one so I'm maybe making wrong assumptions here...

[snoyberg]

OK, I didn't understand that, I thought it was a normal plugin. In that case, I'm not certain of the best way forward. It might make sense for you to keep the code as a fork of the main repo with a different package name, and then just merge upstream changes as necessary.

[emmanueltouzery]

I was thinking about it, I just won't have the discipline in the long or even medium term to publish new versions of this fork as you publish new versions of the normal persistent-sqlite package. So in my project I'll just include instructions how to fetch persistent-sqlite source, modify the .cabal file and install it so that you get that persistent-sqlcipher package.

Not very handy, but more or less future-proof.

[gregwebs]

Can you want to put your instructions on the wiki?

[emmanueltouzery]

If you're an application developer intending to use persistent with sqlcipher I think it's pretty simple to achieve. It's more getting past the inconvienience that it's not available out of the box. I have put short instructions on the readme of my project though (since it's early days the readme doesn't contain much more for now, but it'll expand). https://github.com/emmanueltouzery/projectpad

[junjihashimoto]

Hi,

We can find the difference between sqlite and sqlcipher. I think the difference is just encryption-function and sqlcipher is just a plugin.

I hope persistent-sqlite supports encryption-function. (I develop embedded system which uses yesod and needs encryption.)

[emmanueltouzery]

Well the compiled version of sqlcipher looks like that on my machine:

-rw-r--r--. 1 emmanuel emmanuel 1003416 Jan  5  2015 libsqlcipher.a
-rwxr-xr-x. 1 emmanuel emmanuel    1010 Jan  5  2015 libsqlcipher.la
lrwxrwxrwx. 1 emmanuel emmanuel      21 Feb 18  2015 libsqlcipher.so -> libsqlcipher.so.0.8.6
lrwxrwxrwx. 1 emmanuel emmanuel      21 Feb 18  2015 libsqlcipher.so.0 -> libsqlcipher.so.0.8.6
-rwxr-xr-x. 1 emmanuel emmanuel  881577 Jan  5  2015 libsqlcipher.so.0.8.6

$ ldd lib/libsqlcipher.so.0.8.6 
linux-vdso.so.1 (0x00007ffecd50d000)
libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f7212b0f000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f72128f3000)
libc.so.6 => /lib64/libc.so.6 (0x00007f7212532000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f721232e000)
libz.so.1 => /lib64/libz.so.1 (0x00007f7212118000)
/lib64/ld-linux-x86-64.so.2 (0x0000556b39c85000)

So it's a standalone .so file that does not link to any sqlite dependency. Therefore I think the solution of the separate persistent-supporting package is the only one (still using this solution with success on my project btw, although it makes it a pain to install).

[emmanueltouzery]

In addition my haskell executable links to sqlcipher.so but NOT to any sqlite SO file.

[junjihashimoto]

I recognize persistent-sqlite uses c-sources: cbits/sqlite3.c by default. If we do not use flag(systemlib), persistent-sqlite does not depend on dynamic library.

What do you think about using sqlcipher.c instead of sqlite3.c?

[junjihashimoto]

I have made https://github.com/yesodweb/persistent/pull/475 which uses sqlicipher's sqlite3.c. I would appreciate if you have any comments.

[emmanueltouzery]

I commented on that solution in my initial opening of this bug:

if it's a build flag, I don't think an application could depend on persistent-sqlcipher. It would have to require that the user first installs persistent-sqlite manually with the right flag. And if the app is paranoid about data storage, it would probably need a runtime way to check whether it was in fact built against a persistent-sqlite package built against sqlchipher and not the ordinary sqlite (otherwise all data would be stored as plain text, a disaster!)

As much as I'd love to see out of the box support for persistent-sqlcipher, I think adding it to the persistent-sqlite package is wrong. IMO we need a new persistent-sqlcipher package. Your application would what, depend on persistent-sqlite with a special mention in the README "warning you must build persistent-sqlite with the special flag 'no system lib'"? And if the user doesn't do that, he'll get an application which is obviously paranoid about data safety (since it's storing to encrypted storage) that'll store to a plain text DB? I think that's wrong.

So I think we need a separate persistent-sqlcipher package. However it would be enough to add a new cabal file in the persistent-sqlite source and have the persistent-sqlite maintainer upload both packages when releasing a new version of persistent-sqlite. That's assuming he is ready to invest the effort of course... But as of myself I use the same source, verbatim, no changes whatsoever. Sure, shipping also the functions to enter the password or change the password would be a plus, but it's really no big deal. They're just two raw SQL calls.

PS: note that I have a runtime check in my app whether the backend storage is in fact encrypted: https://github.com/emmanueltouzery/projectpad/blob/fe5320b2c55d57e2b5c56ddb9c744b98dca4afd6/src/Main.hs#L96-L98 it's pretty primitive, but better than nothing.

[junjihashimoto]

Thank you for your comments.

GHC can check encryption function exists or not, so README's warning may not be necessary. If we want to use see, should we make persistent-sqlite-see? Encryption-interface is not sqlcipher's original one.

[junjihashimoto]

To be honest, If we can use encryption function from hackage, I do not care about package name. `persistent-sqlcipher is OK.