Encrypted SQLite Databases with Python and SQLCipher_"a = c.execute(\"select * from rcontact\") pysqlci

http://charlesleifer.com/blog/encrypted-sqlite-databases-with-python-and-sqlcipher/

SQLCipher, created by Zetetic, is an open-source library that provides transparent 256-bit AES encryption for your SQLite databases. SQLCipher is used by a large number of organizations, including Nasa, SalesForce, Xerox and more. The project is open-source and BSD licensed. Best of all, there are open-source python bindings.

In this post, I’ll show how to get started writing Python scripts that interact with encrypted SQLite databases. For users of the peewee ORM, I will demonstrate the usage of the sqlcipher playhouse module. Finally, I’ll show how to convert your existing SQLite databases into encrypted databases suitable for use with SQLCipher.

Building SQLCipher
Let’s get started by cloning the most recent version of the SQLCipher library and installing it on our system.

$ git clone https://github.com/sqlcipher/sqlcipher
$ cd sqlcipher
To compile SQLCipher, we will link against OpenSSL’s libcrypto, so make sure you have OpenSSL installed before proceeding. I’ve also specified that we want to enable the full-text search extension. For the adventurous, the SQLite documentation contains a comprehensive list of compile options.

$ export CFLAGS="-DSQLITE_DEFAULT_CACHE_SIZE=-8000
-DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_FTS3_PARENTHESIS
-DSQLITE_ENABLE_FTS4
-DSQLITE_ENABLE_FTS5
-DSQLITE_ENABLE_JSON1
-DSQLITE_ENABLE_STAT4
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT
-DSQLITE_SOUNDEX
-DSQLITE_USE_URI
-DSQLITE_HAS_CODEC
-O2"

$ ./configure --enable-tempstore=yes LDFLAGS="-lcrypto -lm"
$ make
$ sudo make install
You should now be able to fire up the sqlcipher shell, which by default is connected to an in-memory database:

$ sqlcipher
SQLCipher version 3.26.0 2018-12-01 12:34:55
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>
Kicking the tires on SQLCipher
To create an encrypted database, we can use the SQLCipher shell, specifying a key using a special PRAGMA command:

sqlite> .open testing.db
sqlite> PRAGMA key=‘testing’;
sqlite> create table people (name text primary key);
sqlite> insert into people (name) values (‘charlie’), (‘huey’);
sqlite> .quit
If we take a look at the data in testing.db, we’ll find that it is completely garbled:

$ hexdump -C testing.db
0000 04 37 1e 64 12 fb a2 0b 8d 88 2f 72 fd c6 4b e6 |.7.d…/r…K.|
0010 7f 80 14 ec 74 68 83 00 e9 d2 4f 2e 80 5d 05 da |…th…O…]…|
0020 f0 44 f3 83 23 5e 29 e4 73 fc 29 1b 2d 6a 1d bc |.D…#^).s.).-j…|
0030 be 94 e6 12 6e 7a 28 32 15 cd 7b 1e a5 3c f7 52 |…nz(2…{…<.R|
0040 1a 51 37 40 28 70 3e fe 5d d9 0f 06 cc 76 4c 98 |.Q7@(p>.]…vL.|
…
If we try to open the database using the normal SQLite client, or if we specify the incorrect key, the data will be unreadable:

$ sqlite3 testing.db
SQLite version 3.27.0 2018-12-17 22:19:57
Enter “.help” for usage hints.
sqlite> .schema
Error: file is encrypted or is not a database
sqlite> .quit

$ sqlcipher testing.db
SQLCipher version 3.26.0 2018-12-01 12:34:55
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> pragma key=‘wrong’;
sqlite> .schema
Error: file is encrypted or is not a database
SQLCipher supports a number of special commands besides PRAGMA key. For the full list, check out the API documentation.

Building pysqlcipher
Run the following commands to install the latest version of pysqlcipher3 globally on your system:

$ git clone https://github.com/rigglemania/pysqlcipher3
$ cd pysqlcipher3
$ python setup.py build # Build against the system libsqlcipher
$ sudo python setup.py install
Connecting to an encrypted database from Python
Let’s see how to use SQLCipher from a Python script. pysqlcipher3 implements the db-api 2.0 spec, so if you’ve worked with databases in Python before, you’ll feel right at home.

from pysqlcipher3 import dbapi2 as sqlcipher
db = sqlcipher.connect(‘testing.db’)
In order to actually make queries, we need to specify a passphrase using the PRAGMA key statement. Additionally, we need to specify the key derivation iterations using PRAGMA kdf_iter, which has a default value of 64000.

db.execute(‘pragma key=“testing”’)
<pysqlcipher3.dbapi2.Cursor object at 0x7f2a77be40a0>

db.execute(‘select * from people;’).fetchall()
[(u’charlie’,), (u’heuy’,)]
If we attempt to connect with the incorrect passphrase, we will receive a DatabaseError:

db = sqlcipher.connect(‘testing.db’)
db.execute(‘pragma key=“wrong”’)
<pysqlcipher3.dbapi2.Cursor object at 0x7f167ec2d0a0>

db.execute(‘select * from people;’)
Traceback (most recent call last):
File “”, line 1, in
pysqlcipher3.dbapi2.DatabaseError: file is encrypted or is not a database
Using SQLCipher with Peewee ORM
If you do not have peewee installed, feel free to install it now:

$ pip install peewee
The peewee SQLCipher extension allows you to use peewee with encrypted SQLite databases. To create an encrypted diary, we might write the following code:

import datetime

from playhouse.sqlcipher_ext import *

db = SqlCipherDatabase(‘diary.db’, passphrase=‘my secret passphrase’)

class Note(Model):
content = TextField()
timestamp = DateTimeField(default=datetime.datetime.now)

class Meta:
    database = db
1
2

If the above code is in a model named diary.py, we can interact with it from the command-line in the following way:

from diary import Note
Note.create_table()
Note.create(content=‘Dear diary, today I had a good day!’)
Note.create(content=‘Dear diary, huey threw up on the floor.’)
for note in Note.select():
… print note.timestamp, note.content
…
2014-10-27 21:05:58.488291 Dear diary, today I had a good day!
2014-10-27 21:06:16.663230 Dear diary, huey threw up on the floor.
Hard-coding the passphrase in your database might not be a good idea. To retrieve the passphrase at run-time, we can use the standard library getpass module to prompt the user:

import datetime
import getpass

from playhouse.sqlcipher_ext import *

db = SqlCipherDatabase(None) # Defer initialization of the database.

class Note(Model):
content = TextField()
timestamp = DateTimeField(default=datetime.datetime.now)

class Meta:
    database = db
1
2

passphrase = getpass.getpass('Enter the diary password: ')
db.init(‘cipher.db’, passphrase=passphrase)
Alternatively you can use environment variables or look into a library like python-keyring.

Encrypting an existing SQLite Database
If, like me, you have some existing SQLite databases you wish to convert over to SQLCipher, the following commands should get you started. These commands, and other examples, can be found in the SQLCipher documentation:

$ sqlcipher plaintext.db
sqlite> ATTACH DATABASE ‘encrypted.db’ AS encrypted KEY ‘my password’;
sqlite> SELECT sqlcipher_export(‘encrypted’);
sqlite> DETACH DATABASE encrypted;
That’s it! Now encrypted.db will contain an encrypted copy of the data in plaintext.db.