I have a sqlite3 db which i insert/select from in python. The app works great but i want to tweak it so no one can read from the DB without a password. How can i do this in python? note i have no idea where to start.
You can use SQLCipher.
Open Source Full Database Encryption for SQLite
SQLCipher is an SQLite extension that provides transparent 256-bit AES encryption of database files. Pages are encrypted before being written to disk and are decrypted when read back. Due to the small footprint and great performance it’s ideal for protecting embedded application databases and is well suited for mobile development.
- Blazing fast performance with as little as 5-15% overhead for
encryption on many operations
- 100% of data in the database file is encrypted Uses good security
practices (CBC mode, key derivation)
- Zero-configuration and application level cryptography Broad platform
- support: works with C/C++, Obj-C, QT, Win32/.NET, Java, Python,
Ruby, etc on Windows, Linux, iPhone/iOS…
I had the same problem. My application may have multiple instances running at the same time. Because of this, I can’t just encrypt the sqlite db file and be done with it. I also don’t believe that encrypting the data in python is a good idea, as you can’t do any serious data manipulation in the database with it in this state.
With those constraints in mind, I have come up with the following two solutions:
Use the before mentioned SQLCipher. The problems I see here, are that I will have to write my own bindings for Python, and compile it myself (or pay the fee). I might do this in either case as it would be a great solution for other Python developers out there. If I succeed, I will post back with the solution.
If option 1 is too difficult for me, or too time consuming, I will use this method. This method is not as secure. I will use pycrypto to encrypt the database file. I will implement a SQL “server” which will decrypt the database file, then handle requests from various clients. Whenever there are no outstanding requests, it will reencrypt the database. This will be slower, over all, and leave the database in temporary decrypted states.
Hope these ideas help the next guy.
I gave up on SQLCipher because I couldn’t seem to get it to compile, and the code base is trying to use OpenSSL, which while a sound library, is pretty massive of a code base for simple AES 128.
I found another option wxSQLite3, and I found out how to separate out just the SQLite encryption piece: https://github.com/shenghe/FreeSQLiteEncryption. I was able to get this to compile and work (with the latest version of SQLite3). wxSQLite3 also support AES 256 which is really cool. My next step is going to be to attempt to compile pysqlite (which is the sqlite library that comes built into python) with the modified sqlite3.dll. If that works, I’ll tweak pysqlite to support the extended, encryption piece of the wxSQLite3’s sqlite3.dll. In any case, I’ll try to update this thread with my results, and if successful, I’ll post the final code base, with build instructions, on Github.
As Frontware suggests, you can use sqlcipher.
pysqlcipher python package can make it easier to use since it uses the sqlcipher code amalgamation to compile the extension.
It should be just a matter of using pysqlcipher as you would use regular sqlite.dbapi2, just setting the right crypto pragmas.
SQLite databases are pretty human-readable, and there isn’t any built-in encryption.
Are you concerned about someone accessing and reading the database files directly, or accessing them through your program?
I’m assuming the former, because the latter isn’t really database related–it’s your application’s security you’re asking about.
A few options come to mind:
- Protect the db with filesystem permissions rather than encryption. You haven’t mentioned what your environment is, so I can’t say if this is workable for you or not, but it’s probably the simplest and most reliable way, as you can’t attempt to decrypt what you can’t read.
- Encrypt in Python before writing, and decrypt in Python after reading. Fairly simple, but you lose most of the power of SQL’s set-based matching operations.
- Switch to another database; user authentication and permissions are standard features of most multi-user databases. When you find yourself up against the limitations of a tool, it may be easier to look around at other tools rather than hacking new features into the current tool.
you can use pysqlitecipher module – https://github.com/harshnative/pysqlitecipher
You will loose the built in sqlite features but you can implement them directly in python such as queries etc. it handles encryption in the background so you don’t have to worry, also the encryption standard is great too.
problem solved by above module – Their is not a easy solution to encrypted sqlite database using python.
All the solutions available difficult to set up and if you do get , then converting the solution to executable is even more difficult.
But pysqlitecipher is build on core python , so it is easily convertable to exe using modules such as pyinstaller