Create a gist now

Instantly share code, notes, and snippets.

@thedod /README.md
Last active Aug 29, 2015

What would you like to do?
Example code to demonstrate SqlCipherDatabase peewee backend

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

"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())
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment