Example code to demonstrate SqlCipherDatabase peewee backend

README.md

Peewee playhouse.sqlcipher_ext example

You're prompted for a passphrase (should be at least 8 characters long. Real-life apps should enforce a more strict passphrase strength policy). If the file testsqlcipher.db doesn't exist, it will be created with this passphrase, otherwise - the passphrase should match the existing db (or it can't be decrypted [hopefully]).

You can also access the database with an iteractive sqlcipher shell,

sqlite> pragma key = 'alongenoughpassphrase';
sqlite> pragma kdf_iter = 64000;
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "person" ("name" VARCHAR(255) NOT NULL PRIMARY KEY, "age" INTEGER NOT NULL);
INSERT INTO "person" VALUES('alice',17);
INSERT INTO "person" VALUES('bob',23);
INSERT INTO "person" VALUES('carol',42);
COMMIT;

or import testpeeweesqlcipher into python and use a() and q().

>>> from testpeeweesqlcipher import *
Passphrase: 
That's not a good passphrase... Sorry.
Passphrase: 
Wrong passphrase... Sorry.
Passphrase: 
>>> a("ted",64)
('INSERT INTO "person" ("name", "age") VALUES (?, ?)', [u'ted', 64])
<Person: u'ted (age 64)'>

Prerequisites

• sqlcipher
• pysqlcipher
• This proposed version of peewee.

testpeeweesqlcipher.py

"See https://gist.github.com/thedod/11048875#file-readme-md"
import peewee
from playhouse.sqlcipher_ext import SqlCipherDatabase

from getpass import getpass
import logging

logger = logging.getLogger('peewee')
logger.setLevel(logging.INFO)
#logger.setLevel(logging.DEBUG)  # This prints SQL queries.
logger.addHandler(logging.StreamHandler())

db_proxy = peewee.Proxy()

def is_good_passphrase(p):
    """This should be less stupid (of course), but it's needed
if we don't want the code to fail when user enters a short passphrase.
*Forcing* developers to have such a function is a feature -- not a bug ;)
"""
    return len(p) > 8

def get_good_passphrase(prompt="Passphrase: "):
    """Prompt for a passphrase until it's "good enough"."""
    while True:
        p = getpass(prompt)
        if is_good_passphrase(p):
            return p
        print "That's not a good passphrase... Sorry."

class Model(peewee.Model):
    class Meta:
        database = db_proxy

class Person(Model):
    name = peewee.CharField(primary_key=True)
    age = peewee.IntegerField()
    def __unicode__(self):
        return u"{0} (age {1})".format(self.name,self.age)

def a(name,age):
    "Add a person. Helper for interactive shell."
    return Person.create(name=name,age=age)

def q():
    "Query all people. Helper for interactive shell."
    return list(Person.select())

# Initialize db even if we're imported in interactive python shell.
right_passphrase = False
while not right_passphrase:
    passphrase = None
    db = SqlCipherDatabase('testsqlcipher.db', passphrase=get_good_passphrase())
    try:  # Error only gets triggered when we access the db.
        db.get_tables()
        right_passphrase = True
    except peewee.DatabaseError as e:
        # We only allow a specific [somewhat cryptic] error message.
        if e.message != 'file is encrypted or is not a database':
            raise e  # That's a "real" error. Raise it.
        print "Wrong passphrase... Sorry."
db_proxy.initialize(db)

# If running as an application, [create and popluate db if needed, and] query.
if __name__ == '__main__':
    if not Person.table_exists():
        Person.create_table()
        a("alice",17)
        a("bob",23)
        a("carol",42)
    print(q())