[DBD-SQLite] Adding support for SQLCipher.

[Dmitri Tikhonov]

Hi Kenichi,

Thanks for the quick response.  You are right: I
don't need to patch DBD::SQLite to issue pragmas, I must have been
testing with the non-sqlcipher version of libsqlite.  That was a brain
fart on my part: I don't need to muck with patches after all.  Your
other points make me realize that DBD::SQLite also ships with a local
version of sqlite, which does not have sqlcipher patch...

Sorry
for unthought-through suggestion and thanks for pointing out the error
of my ways: I certainly am glad I don't have to modify DBD::SQLite! :)

  - Dmitri.


----- Original Message ----
> From: Kenichi Ishigaki <kishigaki at gmail.com>
> To: dbd-sqlite at lists.scsys.co.uk
> Sent: Tuesday, August 18, 2009 12:48:52 PM
> Subject: Re: [DBD-SQLite] Adding support for SQLCipher.
> 
> Hi, Dmitri,
> 
> sorry but I don't think it's a good idea to apply your patch
> right now. I haven't tested it yet, but SQLCipher's README
> says 1) building via 'amalgamation' isn't supported, 2) we must
> define SQLITE_HAS_CODEC, 3) we need to link against a OpenSSL's
> libcrypt iwth sha256 support. That means, if we want to enable
> it, we also need those libraries (i.e. we also need to patch
> Makefile.PL etc). And the SQLCipher's license looks like a bit
> obscure, private one, which might not fit for a CPAN module.
> 
> Besides, you can freely issue PRAGMA (re)key="something" without
> this patch. DBD::SQLite certainly ignores a password, but it's
> derived from the DBI's API. I don't think it's a good idea to
> use it in other way.
> 
> Thanks,
> 
> Kenichi
> 
> 
> On Tue, 18 Aug 2009 07:30:33 -0700 (PDT), Dmitri Tikhonov 
> wrote:
> 
> >Hello,
> >
> >I have been working with SQLCipher [1] and I have noticed that DBD::SQLite does 
> not work out of the box with it.  In order to use an encrypted database, the 
> first thing that should happen is to issue a pragma that specifies the key to 
> decrypt pages.  I modified the login function (the patch is attached) to use the 
> password, if set, as the key.
> >
> >I was hoping this (or a modified, but equivalent) change could be made to the 
> main line: it should not break existing code and will make DBD::SQLite work with 
> SQLCipher.
> >
> >  - Dmitri.
> >
> >1. "SQLCipher is an open source extension that provides transparent encryption 
> of SQLite databases. Data pages are encrypted before being written to storage 
> and decrypted on read."  http://www.zetetic.net/software/sqlcipher/
> >
> >
> >
> >
> 
> >Index: dbdimp.c
> >===================================================================
> >--- dbdimp.c    (revision 31)
> >+++ dbdimp.c    (revision 32)
> >@@ -106,6 +106,28 @@
> >
> >     sqlite3_busy_timeout(imp_dbh->db, SQL_TIMEOUT);
> >
> >+    if (pass) {
> >+        /* If password is set, issue pragma to decrypt the database.  Works
> >+         * with SQLCipher.
> >+         */
> >+        char pragma[0x100];
> >+        if (snprintf(pragma, sizeof(pragma), "PRAGMA key='%s'", pass) >=
> >+            sizeof(pragma))
> >+        {
> >+            sqlite_error(dbh, (imp_xxh_t*)imp_dbh, 1,
> >+                         strdup("password too long"));
> >+            return FALSE;
> >+        }
> >+
> >+        if ((retval = sqlite3_exec(imp_dbh->db, pragma, NULL, NULL, &errmsg))
> >+            != SQLITE_OK)
> >+        {
> >+            /*  warn("failed to set pragma: %s\n", errmsg); */
> >+            sqlite_error(dbh, (imp_xxh_t*)imp_dbh, retval, errmsg);
> >+            return FALSE;
> >+        }
> >+    }
> >+
> >     if ((retval = sqlite3_exec(imp_dbh->db, "PRAGMA empty_result_callbacks = 
> ON",
> >         NULL, NULL, &errmsg))
> >         != SQLITE_OK)
> 
> >_______________________________________________
> >DBD-SQLite mailing list
> >DBD-SQLite at lists.scsys.co.uk
> >http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
> 
> 
> 
> _______________________________________________
> DBD-SQLite mailing list
> DBD-SQLite at lists.scsys.co.uk
> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite