Using pysqlcipher with SqlAlchemy?

Question

I'm trying to add some code to my program to encrypt the sqlite database I use. I'm trying to prompt the user for password, and use that password to create a new encrypted database if it doesn't exist, or decrypt and load an existing DB. There just doesn't seem to be a whole lot of documenation that I could find and I'm not sure how to do this. My code follows:

if encryption is True:
   print("***PYPER TIMESHEET UTILITY***")
   print("\nEnter encryption password below:")
   key = getpass.getpass()
   DB_NAME = ".timesheet.db"
   engine = create_engine('sqlite:///{}'.format(DB_NAME), module=sqlite)

else:

   print("WARNING: Unencrypted session. Install pysqlcipher3 to enable encryption\n")
   DB_NAME = ".timesheet.db?cipher=aes-256-cfb&kdf_iter=64000"
   engine = create_engine('sqlite:///{}'.format(DB_NAME))
   DBSession = sessionmaker(bind=engine)
   session = DBSession()

EDIT: forgot to give some more info.

I've tried what's listed at sqlalchemy. In the example above, I realized I left out an important line,

from pysqlcipher import dbapi 2 as sqlite

Link to full code

what did you try? how did it fail? Don't expect this to be like Rent-A-Coder for free. — knitti, May 21 '15 at 12:52
I don't - this is just a personal project for learning and I'm getting frustrated. I updated the post with more info. — Ross Wardrup, May 21 '15 at 13:45
"which doesn't work" is no info at all. How does it fail? What behaviour do you expect? What do you get? — knitti, May 21 '15 at 13:51
It runs, and asks for a password, but does not encrypt the table. It's still plaintext. — Ross Wardrup, May 21 '15 at 14:01
Ah, now we are getting somewhere. Compare `DB_NAME` for your encrypted session with the unencrypted side. Also, think about where you'd have to insert `key`. Read the example again. — knitti, May 21 '15 at 14:05
@RossWardrup, you mean, the line `from ... import ...` doesn't work? What error does it give you? — ForceBru, May 21 '15 at 14:13
The import works. The DB encryption doesn't work. I changed the create_engine line to: engine = create_engine('sqlite+pysqlcipher:///{0}?cipher=aes-256-cfb&kdf_iter=64000'.format(DB_NAME)) The code creates an empty DB, but will not query or insert anything into the table. I'm pretty sure this is because I'm now not implementing the key but I don't now where to do that (@knitti). That's a question I've been wondering - it seems I need to write something such as "PRAGMA key=PASSWORD", but I don't now how to use that in conjunction with Sqlalchemy. I added link to code in main text. — Ross Wardrup, May 21 '15 at 14:21

knitti · Accepted Answer · 2015-05-21T14:36:29.850

6

You forgot to include the key into your DB connection, as the example said:

'sqlite+pysqlcipher://:testing@/foo.db?cipher=aes-256-cfb&kdf_iter=64000'

(the key in the example is "testing"), so try this after having obtained key:

engine = create_engine(
    'sqlite+pysqlcipher://:{0}@/{1}?'
    'cipher=aes-256-cfb&kdf_iter=64000'.format(key, DB_NAME))

edited May 21 '15 at 14:36

answered May 21 '15 at 14:33

knitti

6,817
31
42

Thanks, @knitti.. I'm not trying to get you guys to do this for me. I just wanted a little nudge. So , in 'testing@/foo.db,' the @ portion is the key? I didn't realize this. I'm not adept at SQL so I missed that. – Ross Wardrup May 21 '15 at 14:35
I saw that, else I'd voted for closing the question. – knitti May 21 '15 at 14:36
Runs, creates DB but am unable to query the db. I'll accept this answer as is and see if I can figure out what I'm missing in the queries. Thanks for your help! (Can't award bounty until tomorrow) – Ross Wardrup May 21 '15 at 14:43
do you getting your session object from this engine? – knitti May 21 '15 at 14:51
Yes sir/ma'am. a gist is here, showing an example.. https://gist.github.com/minorsecond/93706951d2dbdecf0c44 – Ross Wardrup May 21 '15 at 14:58
Wild guess: as per https://github.com/leapcode/pysqlcipher/ try `python setup.py build_sqlcipher`; after making sure that you've got the headers of openssl accessible (probably a package openssl-dev or something like this) – knitti May 21 '15 at 15:02
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78441/discussion-between-knitti-and-ross-wardrup). – knitti May 21 '15 at 15:04

Using pysqlcipher with SqlAlchemy?

1 Answers1