HTW 7: SQLCipher for Encrypted SQLite Database

I have been using SQLite as a format to distribute open data for a while. I also use it to share private data. I do encrypt before sending the private data. Its mostly by putting the sqlite file inside an encrypted container (GPG, TrueCrypt or 7zip encrypted archive). It works as a simple and good way to send the encrypted database. But once the user decrypts and extracts the SQLite file, its availabe to everyone who can access the file. I would prefer a solution where the user has to enter password everytime he opens the database. SQLCipher which is an open source extension to SQLite that provides transparent 256-bit AES encryption, was availabe for sometime now. Its been used by some of my favorite Gaurdain projects. zetetic the company behind SQLCipher sells the binary distribution of the same. Ofcourse nothing wrong with it, since the software itself was open source and anyone can build it for themselves. Except that it was difficult to build.

Now its become much easier. In three steps you can get it running. SQLCipher depends on OpenSSL's libcrypto and hence first step is to install dev version of libssl. Then clone/download the official SQLCipher project. Build it and install it. On my Ubuntu it looks like

Building SQLCipher

$ sudo apt-get install libssl-dev 
$ git clone https://github.com/sqlcipher/sqlcipher
$ cd sqlcipher
$ sudo ./configure --enable-tempstore=yes \
CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS"\ 
LDFLAGS="-lcrypto" 
$ make 
$make install  

Working with SQLCipher

Below you can see me creating a new database called test.sqlite and setting the password. You can also see I have created a table and inserted few rows for reference.

 thej@mukta  ~/Documents  sqlcipher 
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test_encry.sqlite
sqlite> PRAGMA key='mypassword';
sqlite> create table my_table (name text);
sqlite> insert into my_table(name) values('thejesh');
sqlite> insert into my_table(name) values('thejesh');
sqlite> select * from my_table;
thejesh
thejesh
sqlite> .quit

Lets do an hexdump of the same

 thej@mukta  ~/Documents  hexdump -C test_encry.sqlite 
00000000  ec 14 de 86 2d 1f c2 c4  49 83 d6 86 cd db a3 19  |....-...I.......|
00000010  6b 08 0f 3e d6 81 a9 3b  d8 46 53 93 95 66 ee ca  |k..>...;.FS..f..|
00000020  ba 38 15 5a 62 94 13 e1  fe db 5b 76 ad 00 de 34  |.8.Zb.....[v...4|
00000030  d5 0d 39 49 57 8b 33 54  e9 fb 5d ed c8 09 f5 df  |..9IW.3T..].....|
00000040  a6 44 a9 de 98 ea d6 4d  e6 79 c4 99 f2 d7 bc 02  |.D.....M.y......|
00000050  a3 62 6f 73 53 c5 14 13  9f 30 a1 5d 52 fa 6c 95  |.bosS....0.]R.l.|
00000060  13 60 91 5a d3 d7 6f 5b  ba 38 70 65 7b b5 a8 69  |.`.Z..o[.8pe{..i|
00000070  e6 8c 43 00 e4 58 8c 3e  05 e4 d4 68 6c 66 e0 c7  |..C..X.>...hlf..|
00000080  10 71 e5 59 ae 58 f0 5b  7c ed 5f 3c 9a 90 10 f9  |.q.Y.X.[|._<....|
00000090  da d2 c2 ce f4 c6 c8 5d  92 3b 0a 9b 36 45 21 ff  |.......].;..6E!.|
000000a0  76 0d be 3e 49 da 9e 7c  3e 87 dc 06 fa 45 ae bb  |v..>I..|>....E..|
000000b0  fe cd fe 42 c5 04 8d a5  99 19 f3 39 2c de e2 b5  |...B.......9,...|
000000c0  bb f5 f6 10 ec 69 2f 90  5d 62 1e b4 6f e4 48 f9  |.....i/.]b..o.H.|
000000d0  8a d1 4c 71 6e 5e fd d7  eb 7f 71 08 1b c6 7f c9  |..Lqn^....q.....|

Where as if I had done the same thing with normal sqlite3 I would have got

 thej@mukta  ~/Documents  hexdump -C test_normal.sqlite 
00000000  53 51 4c 69 74 65 20 66  6f 72 6d 61 74 20 33 00  |SQLite format 3.|
00000010  04 00 01 01 00 40 20 20  00 00 00 03 00 00 00 02  |.....@  ........|
00000020  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 04  |................|
00000030  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000050  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 03  |................|
00000060  00 2d e2 29 0d 00 00 00  01 03 c1 00 03 c1 00 00  |.-.)............|
00000070  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000003c0  00 3d 01 06 17 1d 1d 01  4f 74 61 62 6c 65 6d 79  |.=......Otablemy|
000003d0  5f 74 61 62 6c 65 6d 79  5f 74 61 62 6c 65 02 43  |_tablemy_table.C|
000003e0  52 45 41 54 45 20 54 41  42 4c 45 20 6d 79 5f 74  |REATE TABLE my_t|
000003f0  61 62 6c 65 20 28 6e 61  6d 65 20 74 65 78 74 29  |able (name text)|
00000400  0d 00 00 00 02 03 ea 00  03 f5 03 ea 00 00 00 00  |................|
00000410  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000007e0  00 00 00 00 00 00 00 00  00 00 09 02 02 1b 74 68  |..............th|
000007f0  65 6a 65 73 68 09 01 02  1b 74 68 65 6a 65 73 68  |ejesh....thejesh|

If you try and open an encrypted database with the regular sqlite3. It opens like any other database. But you will get an error when you try and query the database

 thej@mukta  ~/Documents  sqlite3 test_encry.sqlite 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
Error: file is encrypted or is not a database
sqlite> 

Or with a wrong password

 thej@mukta  ~/Documents  sqlcipher test_encry.sqlite
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
Error: file is encrypted or is not a database
sqlite> PRAGMA key='notmypassword';
sqlite> .schema
Error: file is encrypted or is not a database
sqlite> .quit

I have noticed that, PRAGMA key setting should be the first command after opening the encrypted database. Else it will throw error, even if you set the key later.

 thej@mukta  ~/Documents  sqlcipher test_encry.sqlite
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
Error: file is encrypted or is not a database
sqlite> PRAGMA key='mypassword';
sqlite> .schema
Error: file is encrypted or is not a database
sqlite> .quit

If everything goes well you can open and query the database

 thej@mukta  ~/Documents  sqlcipher test_encry.sqlite
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA key='mypassword';
sqlite> .schema
CREATE TABLE my_table (name text);
sqlite> .quit

In the next blog post we will explore encrypted database from Python.