How To encrypt a SQLite database in Tcl under Windows

2013-04-04: Nagu:

I could successfully build and do a small test of creating an encrypted sqlite db from a tcl script on Debian Linux with Tcl 8.5.8:

SQLCipher source code is available from:

    https://github.com/sqlcipher/sqlcipher.git%|%https://github.com/sqlcipher/sqlcipher.git%|% 

Just after running configure command using the flags suggested in README, I had to 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 

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

Then I could execute the following tcl command to create encrypted database:

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

Now try to access data without providing the key (and get an error):

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

SQLite does not support encryption or passwords. There are a few extensions that add the capability for encryption, among these are The SQLite Encrytion Extension (SEE), SQLite-Crypt, wxsqlite3 and SQLCipher. However none of these provides with the tcl bindings to be able to manipulate an SQLite database from tcl code.

I will show you how to achieve this, but it will take a bit of work. You need to download MinGW/MSYS, OpenSSL and the SQLCipher source code. After that follow these steps:

1. install MinGW/MSYS
2. install OpenSSL (requires Visual C++ 2008 Redistributables)
3. create an sqlcipher directory and place in it all the files from the SQLCipher zip file
4. copy openSSL/bin/libeay32.dll to sqlcipher directory.
5. copy openSSL/lib/MinGW to /MinGW/lib directory.
6. go to the sqlcipher directory and compile using the following command:
   ./configure CFLAGS="-DSQLITE_HAS_CODEC -I/c/openssl/include" LDFLAGS="-leay32 -L/c/openssl/lib"
7. type make
8. type make dll

These will create an sqlite3.exe and sqlite3.dll with tcl bindings.

When trying to encrypt a database just type:

 PRAGMA key = 'password'

That's all.