Adds sqlcipher for encrypted SQLite databases

[angerman]

There are two SQLite encryption options:

• SQLite SEE
• SQLCipher

SEE can be bought from the original authors, while SQLCipher is an open source option. This change adds the SQLCipher amalgamation (./configure && make sqlite3.c) from the SQLCipher v3.4.2 release source code. The stat_local patch (#47) has NOT be applied, as this works fine without on macOS Sierra.

The SQLCipher engine can be compiled against commoncrypto (apple), OpenSSL or LibTomCrypt. If any of the respective -flags are enable, the SQLCipher SQLite3 version is build.

With SQLCipher the PRAGMA key and PRAGMA rekey pragmas can be used:

PRAGMA key = 'passphrase'; -- start with the existing database passphrase
PRAGMA rekey = 'new-passphrase'; -- rekey will reencrypt with the new passphrase

and need to be issued as first command when working with encrypted databases.

Together with the sqlite-simple package we can then build something simple like the following encrypted databases access

{-# LANGUAGE LambdaCase        #-}
{-# LANGUAGE OverloadedStrings #-}
module Main where
import qualified Database.SQLite.Simple as DB
import qualified Database.SQLite.Simple.FromRow as DB
import Data.Monoid ((<>))
import qualified Data.Text as T
import Data.String (fromString)
import Control.Exception

main :: IO ()
main = DB.withConnection "test.db" $ \conn -> do
  version <- getUserVersion' conn $ do
    putStrLn "Please provide the passphrase"
    getLine
  case version of
    0 -> do putStrLn "Please provide initial passphrase"
            passphrase <- getLine
            setKey conn passphrase
            initDb conn
            return ()
    _ -> putStrLn "TODO: Do something with the database..."
  return ()

setKey :: DB.Connection -> String -> IO ()
setKey conn passphrase = DB.execute_ conn . fromString $ "PRAGMA key = '" <> passphrase <> "'"

getUserVersion :: DB.Connection -> IO Int
getUserVersion conn = head <$> DB.query_ conn "PRAGMA user_version" >>= \case
  DB.Only n -> return n

-- | Tries to get the userVersion, and uses the handle to
-- request the password, until it succeeds.
getUserVersion' :: DB.Connection -> IO String -> IO Int
getUserVersion' conn getPassphrase = handleJust exFilter exHandle (getUserVersion conn)
  where exFilter :: DB.SQLError -> Maybe ()
        exFilter (DB.SQLError DB.ErrorNotADatabase _ _) = Just ()
        exFilter _ = Nothing
        exHandle :: () -> IO Int
        exHandle _ = do passphrase <- getPassphrase
                        setKey conn passphrase
                        getUserVersion' conn getPassphrase

-- | Initialize or migrate the database
initDb :: DB.Connection -> IO Int
initDb conn = getUserVersion conn >>= \case
  0 -> DB.execute_ conn "PRAGMA user_version = 1" >> return 1
  n -> return n

[angerman]

@nurpax, @IreneKnapp any change this could get merged?

[nurpax]

Hi @angerman, sorry for not getting back to you on this earlier.

I'm a bit on the fence with this change. I'm not too eager to include another full copy of the sqlite3 cbits into the repo.

Whenever a new sqlite3 version is integrated into direct-sqlite, the sqlcipher version would have to be updated too. So rather than just dropping in a new version of sqlite3.c (and the .h files), one would need to generate the sqlcipher cbits too.

Would there be another way to express the same that doesn't require another full copy of the cbits in direct-sqlite? E.g., if you require direct-sqlite to be built with special flags anyway in your app, could you instead clone the sqlcipher source into your own repository and then pass the right parameters to direct-sqlite build to make it use your sqlcipher version?

[angerman]

@nurpax I've been thinking about this. On the one hand I could fork direct-sqlite to just ship with sqlcipher, but that would mean that I need to also fork sqlite-simple and other packages that depend on direct-sqlite, which seems like a bad thing to do; I guess backpack is supposed to give us some implementational freedom here.

sqlcipher is (as far as I understand) a superset of sqlite, as such sqlcipher is sqlite + cipher bits. And sqlcipher can be used to read/write unencrypted sqlite files just fine. As such I guess one could go with "just" sqlcipher; I didn't want to step onto anyones toes and as such added sqlcipher separately.

This arose, because I wanted to publish an Google Authenticator / Authy like command line application written with brick and sqlite-simple. But without sqlcipher, and hence database encryption support, that would be rather weak.

Of course I would like to see sqlite-direct just use sqlcipher, without a need for a flag, and having automatic support for encrypted databases where needed. But again I didn't want to force this on anyone and hence opted for the alternative behind a flag solution.

[nurpax]

@angerman I think I'd prefer an sqlcipher only cbits if the sqlcipher upstream is always up to date with the latest sqlite releases.

Even better would be to turn this feature also on by default in direct-sqlite. The less build config bits, the less hassle. Haskell binaries are so big anyway that including some tens of KBs more in the built doesn't matter. However, I assume that in order to build the sqlcipher enabled sqlite, you need to build it against some crypto library? This most likely becomes a problem on Windows, and probably even on Linux (ie., if you don't have the right dev libs installed, the build will break.). I'd like direct-sqlite to require minimal (ie., nothing) system dependencies, so by default building with sqlcipher enabled probably won't work.

Are there any other sqlite variants like sqlcipher out there? If we anticipate sqlcipher to be the only other source variant, then I don't see a problem in including that as the only cbits variant. If we expect there to be other sqlite variants (I don't know if those exist), then we'd have to go with something like your original patch.

[angerman]

@nurpax as far as I know sqlcipher, they are not always in sync with the sqlite release. E.g. right now SQLite is at 3.22.0, but sqlcipher is based on 3.20.0.

I'm not absolutely sure how identical sqlcipher and sqlite are, I do however believe that everything is hidden behind some ifdefs and compiling it without the SQLITE_HAS_CODEC should also not depend on any encryption library.

I will have to try this out though.

Sorry for the delay, I've been terribly sick for the last two weeks.

[ip1981]

IMHO this all better be part of an external (system) SQLite library, if somebody wants it.

[nurpax]

Agreed. I’m closing this one without merging it. If you need this functionality, fork or use a system lib.