« Previous  |  Next »

NBSQLite3 with SQLcipher

24 December 2015

SQLcipher "is an open source extension to SQLite that provides transparent 256-bit AES encryption of database files." SQLcipher provides the same API as the SQLite Encryption Extension (SEE) by D Richard Hipp, the original developer of SQLite.

I've added SQLcipher/SEE's API to NBSQLite3. By convention, on Unix, SQLcipher produces a shared library named libsqlcipher.so, while the SQLite shared library is named libsqlite3.so. NBSQLite3 switches between the two libraries based on the messages #beSQLcipher and #beSQLite to the NBSQLite3FFI class.

Here's a demonstration code snippet using the keying pragma in SQL:

| dbf db rs row |

Transcript open; clear.	
NBSQLite3FFI beSQLcipher.
dbf := FileReference newTempFilePrefix: 'cipher-' suffix: '.cdb'.

db := NBSQLite3Connection openOn: dbf fullName.
Transcript show: 'Creating an encrypted database with some data.'; cr.
[   db basicExecute: 'pragma key = "test"'.
    db basicExecute: 'create table if not exists x (xk integer primary key, iv integer, tv text);'.
    db beginTransaction.
    [  rs := db execute: 'insert into x values (NULL, ?, ?)' with: #(1 'two') ] ensure: [ rs close ].
    db commitTransaction
] ensure: [ db close ].

db := NBSQLite3Connection openOn: dbf fullName.
Transcript show: 'Opening the encrypted database.'; cr.
[   db basicExecute: 'pragma key = "test"'.
    [   rs := db execute: 'select * from x'.
        row := rs next.
        Transcript show: (row at: 'xk'); cr.
        Transcript show: (row at: 'iv'); cr.
        Transcript show: (row at: 'tv'); cr.
    ] ensure: [ rs close ]
] ensure: [ db close ].

dbf delete.

NBSQLcipherExample class>>examplePragma contains a longer version of the above snippet that includes reopening the encrypted database file without the keying pragma and using the SQLite library.

Tested on Linux Mint. Code updated in Smalltalk Hub. Some refactoring to be expected, because the above snippet using the keying pragma is the only test I've done.

I've placed a copy of libsqlcipher.so here; it is built on my Linux Mint 17.x laptop from the source here, linking in LibreSSL 2.2.4's libcrypto.a statically.

% openssl sha256 libsqlcipher.so
SHA256(libsqlcipher.so)= 441cbc559a4f38a018121c6d86caa0cf0fb2c5b2a57c353cc09a4e048ec8ebe8

% ldd libsqlcipher.so
    linux-gate.so.1 =>  (0xf77da000)
    libdl.so.2 => /lib/i386-linux-gnu/libdl.so.2 (0xf7569000)
    libpthread.so.0 => /lib/i386-linux-gnu/libpthread.so.0 (0xf754d000)
    libc.so.6 => /lib/i386-linux-gnu/libc.so.6 (0xf739e000)
    /lib/ld-linux.so.2 (0xf77dd000)

For good measure, I've also put up a copy of sqlcipher built at the same time. It requires readline.

% openssl sha256 sqlcipher
SHA256(sqlcipher)= 4ccb3cf2064d41675406a55c8404a8877a40541dd9830009f4c0e203468e3d7b

% ldd sqlcipher
    linux-gate.so.1 =>  (0xf770a000)
    libreadline.so.6 => /lib/i386-linux-gnu/libreadline.so.6 (0xf76a7000)
    libdl.so.2 => /lib/i386-linux-gnu/libdl.so.2 (0xf76a2000)
    libpthread.so.0 => /lib/i386-linux-gnu/libpthread.so.0 (0xf7685000)
    libc.so.6 => /lib/i386-linux-gnu/libc.so.6 (0xf74d7000)
    libtinfo.so.5 => /lib/i386-linux-gnu/libtinfo.so.5 (0xf74b5000)
    /lib/ld-linux.so.2 (0xf770d000)


<