Зашифрованные базы данных SQLite с Python и SQLCipher

SQLCipher это библиотека с открытым исходным кодом, созданная компанией Zetetic, для прозрачного 256-битного AES шифрования баз данных SQLite.
Сегодня мы рассмотрим пример написания программы на Python, взаимодействующей с зашифрованной базой SQLite. А пользователям peewee ORM я покажу использование расширения sqlcipher. Также мы рассмотрим конвертацию баз данных SQLite в формат зашифрованных баз для SQLCipher.

Сборка SQLCipher

Начнём с клонирования свежей версии SQLCipher и установки её в системе:

$ git clone https://github.com/sqlcipher/sqlcipher
$ cd sqlcipher
Чтобы скомпилировать SQLCipher, нам нужно прилинковать OpenSSL’s libcrypto, поэтому проверьте перед этим, что у вас установлен OpenSSL. Также я рекомендую установить модуль полнотекстового поиска. А здесь есть полный список опций компиляции SQLite.

$ ./configure \
--enable-tempstore=yes \
CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS" \
LDFLAGS="-lcrypto"

$ make
$ sudo make install
Теперь можно запустить оболочку sqlcipher, которая по-умолчанию подключена к in-memory базе данных.

$ sqlcipher
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Знакомимся с SQLCipher

Для создания шифрованной базы данных мы воспользуемся оболочкой SQLCipher, задав ей ключ с помощью команды PRAGMA:

sqlite> .open testing.db
sqlite> PRAGMA key='testing';
sqlite> create table people (name text primary key);
sqlite> insert into people (name) values ('charlie'), ('huey');
sqlite> .quit
Посмотрев на данные в testing.db, мы найдём их полностью искажёнными:

$ hexdump -C testing.db
0000 04 37 1e 64 12 fb a2 0b 8d 88 2f 72 fd c6 4b e6 |.7.d....../r..K.|
0010 7f 80 14 ec 74 68 83 00 e9 d2 4f 2e 80 5d 05 da |....th....O..]..|
0020 f0 44 f3 83 23 5e 29 e4 73 fc 29 1b 2d 6a 1d bc |.D..#^).s.).-j..|
0030 be 94 e6 12 6e 7a 28 32 15 cd 7b 1e a5 3c f7 52 |....nz(2..{..<.R| 0040 1a 51 37 40 28 70 3e fe 5d d9 0f 06 cc 76 4c 98 |.Q7@(p>.]....vL.|
...
Открыв эту базу данных обычным клиентом SQLite или задав некорректный ключ, мы получим ошибку:

$ sqlite3 testing.db
SQLite version 3.8.7 2014-10-17 11:24:17
Enter ".help" for usage hints.
sqlite> .schema
Error: file is encrypted or is not a database
sqlite> .quit

$ sqlcipher testing.db
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma key='wrong';
sqlite> .schema
Error: file is encrypted or is not a database
SQLCipher также имеет множество команд кроме PRAGMA, полный список доступен здесь.

Сборка pysqlcipher

Выполните следующие команды для установки последней версии pysqlcipher:

$ git clone https://github.com/leapcode/pysqlcipher/
$ cd pysqlcipher
$ python setup.py build_sqlcipher # Build against the system libsqlcipher
$ sudo python setup.py install
Не устанавливайте pysqlcipher с помощью pip, в результате этого он будет установлен из другого места. А для того, чтобы установить pysqlcipher в virtualenv, необходимо склонировать исходники в virtualenv и там их собрать:

$ cd my_env && source bin/activate
$ git clone https://github.com/leapcode/pysqlcipher/
$ cd pysqlcipher
$ python setup.py build_sqlcipher
$ python setup.py install

Работаем с шифрованными базами из Python

>>> from pysqlcipher import dbapi2 as sqlcipher
>>> db = sqlcipher.connect('testing.db')
Для того, чтобы делать запросы к базе данных, необходимо задать ключ с помощью PRAGMA, и дополнительно задать итерации деривации ключа с помощью PRAGMA kdf_iter, который по-умолчанию имеет значение 64000.

>>> db.executescript('pragma key="testing"; pragma kdf_iter=64000;') >>> db.execute('select * from people;').fetchall()
[(u'charlie',), (u'heuy',)]
При коннекте с некорректным паролем мы получим ошибку DatabaseError:

>>> db = sqlcipher.connect('testing.db')
>>> db.execute('pragma key="wrong"') >>> db.execute('select * from people;')
Traceback (most recent call last):
File "", line 1, in
pysqlcipher.dbapi2.DatabaseError: file is encrypted or is not a database

Используем SQLCipher с Peewee ORM

Если у вас ещё не установлен peewee, самое время сделать это:

$ pip install peewee
Расширение peewee SQLCipher позволяет работать с шифрованными базами. Чтобы создать, к примеру, зашифрованный дневник, напишем следующий код:

import datetime

from playhouse.sqlcipher_ext import *

db = SqlCipherDatabase('diary.db', passphrase='my secret passphrase')

class Note(Model):
content = TextField()
timestamp = DateTimeField(default=datetime.datetime.now)

class Meta:
database = db
Если этот код — код модели в файле diary.py, то мы можем работать с ним в командной строке таким образом:

>>> from diary import Note
>>> Note.create_table(fail_silently=True)
>>> Note.create(content='Dear diary, today I had a good day!')
>>> Note.create(content='Dear diary, huey threw up on the floor.')
>>> for note in Note.select():
... print note.timestamp, note.content
...
2014-10-27 21:05:58.488291 Dear diary, today I had a good day!
2014-10-27 21:06:16.663230 Dear diary, huey threw up on the floor.
Прописывать ключ шифрования в коде программы это очень плохая идея, поэтому мы используем модуль getpass в стандартной библиотеке для запрашивания ключа у пользователя:

import datetime
import getpass

from playhouse.sqlcipher_ext import *

db = SqlCipherDatabase(None) # Defer initialization of the database.

class Note(Model):
content = TextField()
timestamp = DateTimeField(default=datetime.datetime.now)

class Meta:
database = db

passphrase = getpass.getpass('Enter the diary password: ')
db.init('cipher.db', passphrase=passphrase)
Также вы можете воспользоваться вместо запроса ключа переменными среды или взять библиотеку python-keyring.

Шифруем имеющуюся базу SQLite

Если у вас уже имеются базы данных SQLite, вам, вероятно, захочется сконвертировать их в формат SQLCipher. Документация по этим командам находится здесь.

$ sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'my password';
sqlite> SELECT sqlcipher_export('encrypted');
sqlite> DETACH DATABASE encrypted;
Вот и всё! Теперь в encrypted.db будет храниться зашифрованная копия данных из plaintext.db.

По материалам: «Encrypted SQLite Databases with Python and SQLCipher»