Encrypting & Accessing SQLite Database From Tcl Scripts

SQLCipher

SQLCipher is an open source extension to SQLite that provides transparent 256-bit AES encryption of database files. It provides a Tcl API interface that enables Tcl SQLite commands to create and access encrypted SQLite database files transparently.

Building SQLCipher

The SQLCipher source code is hosted at github. Issue a 'git clone' command to obtain a local copy of the source code and follow the following instructions. I have tested this on Debian Linux 3.2.0-0.bpo.3-686-pae with Tcl 8.5.8.

The build instructions provided in README file are simple and easy to follow. Just after running configure command using the flags suggested in README, apply the following patch to avoid an undefined symbol error about sqlite3ErrStr (as suggested by one of the posts at sqlcipher users forum):

make sqlite3.h
echo "SQLITE_API const char *sqlite3_sqlite3ErrStr(int);" >> sqlite3.h
echo "const char *sqlite3_sqlite3ErrStr(int err) { return sqlite3ErrStr(err); }" >> src/main.c
sed -i 's/sqlite3ErrStr/sqlite3_sqlite3ErrStr/g' src/tclsqlite.c
make
make install

As I used dynamic linking option. Exported LD_LIBRARY_PATH with the path where new libsqlite3.so is installed.

Creating & Accessing Encrypted DB From Tcl Scripts:

Execute following tcl commands to create encrypted database:

$ tclsh
% package require sqlite3
3.7.14.1
% sqlite3 db /tmp/test2.db
% db eval { PRAGMA key='your key' }
% db eval { create table t1(a,b); insert into t1 values('test1', 'test2'); }
% db eval { select * from t1; }
test1 test2
% db close

To make sure that the database file is encrypted, you can issue the following hexdump command and see that there are no readable strings in the dump:

hexdump -C /tmp/test2.db

Now try to access data without providing the key and confirm that you get an error that file is encrypted or is not a database. Thats what happens when someone without the access to 'you key' tries to access data in the database file.

% sqlite3 db /tmp/test2.db
% db eval { select * from t1; }
file is encrypted or is not a database 

There are other SQLCipher commands like PRAGMA rekey etc. You can read the SQLCipher API documentation for further description.