Encrypted file or db in python

Question

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.

Answer 1

You can use SQLCipher.

http://sqlcipher.net/

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.

1. Blazing fast performance with as little as 5-15% overhead for encryption on many operations
2. 100% of data in the database file is encrypted Uses good security practices (CBC mode, key derivation)
3. Zero-configuration and application level cryptography Broad platform
4. support: works with C/C++, Obj-C, QT, Win32/.NET, Java, Python, Ruby, etc on Windows, Linux, iPhone/iOS…

Answer 2

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:

1. 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.

2. 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.

EDIT 1/13/2013

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.

Answer 3

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.

Answer 4

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:

1. 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.
2. 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.
3. 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.

Answer 5

You can use a database stored in memory (RAM) and only save the crypted version you obtain using the cryptography module. Then, you can access it back by decrypting what you stored and recreating the database in memory.

##imports

from cryptography.fernet import Fernet

from cryptography.hazmat.primitives.kdf.pbkdf2 import PBKDF2HMAC

from cryptography.hazmat.primitives import hashes

from cryptography.hazmat.backends import default_backend

import base64

from os import getcwd

import sqlite3

import gzip

##creating key

def key_creation(password):

    kdf=PBKDF2HMAC(algorithm = hashes.SHA256(), salt=b'\xfaz\xb5\xf2|\xa1z\xa9\xfe\xd1F@1\xaa\x8a\xc2', iterations=1024, length=32, backend=default_backend())

    key=Fernet(base64.urlsafe_b64encode(kdf.derive(password)))

    return key


## encryption

def encryption(b, password):
    f=key_creation(password)
    safe=f.encrypt(b)
    return safe

## decryption

def decryption(safe, password):
    f=key_creation(password)
    b=f.decrypt(safe)
    return b


##

def open_cdb(name,password):

f=gzip.open(getcwd()+name+'_crypted.sql.gz','rb')
safe=f.read()
f.close()

content=decryption(safe,password)
content=content.decode('utf-8')

con=sqlite3.connect(':memory:')
con.executescript(content)

return con

##

def save_cdb(con,name,password):

fp=gzip.open(getcwd()+name+'_crypted.sql.gz','wb')
b=b''

for line in con.iterdump():
    b+=bytes('%s\n','utf8') % bytes(line,'utf8')

b=encryption(b,password)
fp.write(b)
fp.close()


##test
if __name__=='__main__':

    password=b'Sw0rdFish'
    name='PRODUCTS'



    conn = sqlite3.connect(':memory:')
    conn.execute('CREATE TABLE PRODUCTS (ID INT PRIMARY KEY     NOT NULL,\nNAME           TEXT    NOT NULL,\nPRICE            REAL     NOT NULL,\nTAXES        REAL    NOT NULL);')

    save_cdb(conn,name,password)

    conn.close()



    conn = open_cdb(name,password)

    cursor = conn.execute('select * from ' + name)
    headers = list(map(lambda x: x[0], cursor.description))
    print(headers)
    for x in cursor:
        for j in range(len(x)):
            print(headers[j]+' ',x[j])
        print('\n')


    conn.close()