CODEWITHSUNDEEP
pythonsqliteencryption
user34537
user34537

Reputation:

Encrypted database file in Python

I have an SQLite database in Python. The app works but I want that no one can read from the database without a password. How can I do this in Python?

Upvotes: 14

Views: 40271

Answers (4)

Balthazar Floquet
Balthazar Floquet

Reputation: 71

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

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


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


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


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


if __name__ == '__main__':
    password = b'Sw0rdFish'
    name = 'PRODUCTS'
    conn = sqlite3.connect(':memory:')
    conn.execute(
        'CREATE TABLE PRODUCTS (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, PRICE REAL NOT NULL, TAXES 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()

Upvotes: 7

shellster
shellster

Reputation: 1131

My application may have multiple instances running at the same time so I can't just encrypt the SQLite database file. I also don't believe that encrypting the data in Python is a good idea as you can't do any data manipulation in the database with it in this state. I came up with:

  1. Use afore mentioned SQLCipher. I will have to write my own bindings for Python and compile it myself (or pay the fee).
  2. Use PyCrypto to encrypt the database file. I will implement an SQL server to decrypt the database file, then handle requests from clients. Whenever there are no outstanding requests it will re-encrypt the database. This will be slower and leave the database in temporary decrypted states.

SQLCipher I couldn't get to compile and it uses OpenSSL (which is pretty massive for simple AES 128). I found wxSQLite3 and how to separate the SQLite encryption. I got this to work with the latest version of SQLite3. wxSQLite3 supports AES 256. My next step is to compile PySQLite (the SQLite library built into Python) with the modified sqlite3.dll and tweak PySQLite to support the extended encryption from wxSQLite3's sqlite3.dll.

Upvotes: 8

bennomadic
bennomadic

Reputation: 71

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.

Upvotes: 5

Tim Lesher
Tim Lesher

Reputation: 6451

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.

Upvotes: 3

Related Questions