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

Authorship

what did you try? how did it fail? Don't expect this to be like Rent-A-Coder for free. – Raisaraise

I don't - this is just a personal project for learning and I'm getting frustrated. I updated the post with more info. – Authorship

"which doesn't work" is no info at all. How does it fail? What behaviour do you expect? What do you get? – Raisaraise

It runs, and asks for a password, but does not encrypt the table. It's still plaintext. – Authorship

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. – Raisaraise

@RossWardrup, you mean, the line from ... import ... doesn't work? What error does it give you? – Apperception

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. – Authorship

updated the chat, have a look... – Raisaraise

Answer 1

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))

Raisaraise

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. – Authorship

I saw that, else I'd voted for closing the question. – Raisaraise

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) – Authorship

do you getting your session object from this engine? – Raisaraise

Yes sir/ma'am. a gist is here, showing an example.. gist.github.com/minorsecond/93706951d2dbdecf0c44 – Authorship

Wild guess: as per 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) – Raisaraise

Let us continue this discussion in chat. – Raisaraise