1# sqlite/pysqlcipher.py 2# Copyright (C) 2005-2019 the SQLAlchemy authors and contributors 3# <see AUTHORS file> 4# 5# This module is part of SQLAlchemy and is released under 6# the MIT License: http://www.opensource.org/licenses/mit-license.php 7 8""" 9.. dialect:: sqlite+pysqlcipher 10 :name: pysqlcipher 11 :dbapi: pysqlcipher 12 :connectstring: sqlite+pysqlcipher://:passphrase/file_path[?kdf_iter=<iter>] 13 :url: https://pypi.python.org/pypi/pysqlcipher 14 15 ``pysqlcipher`` is a fork of the standard ``pysqlite`` driver to make 16 use of the `SQLCipher <https://www.zetetic.net/sqlcipher>`_ backend. 17 18 ``pysqlcipher3`` is a fork of ``pysqlcipher`` for Python 3. This dialect 19 will attempt to import it if ``pysqlcipher`` is non-present. 20 21 .. versionadded:: 1.1.4 - added fallback import for pysqlcipher3 22 23 .. versionadded:: 0.9.9 - added pysqlcipher dialect 24 25Driver 26------ 27 28The driver here is the 29`pysqlcipher <https://pypi.python.org/pypi/pysqlcipher>`_ 30driver, which makes use of the SQLCipher engine. This system essentially 31introduces new PRAGMA commands to SQLite which allows the setting of a 32passphrase and other encryption parameters, allowing the database 33file to be encrypted. 34 35`pysqlcipher3` is a fork of `pysqlcipher` with support for Python 3, 36the driver is the same. 37 38Connect Strings 39--------------- 40 41The format of the connect string is in every way the same as that 42of the :mod:`~sqlalchemy.dialects.sqlite.pysqlite` driver, except that the 43"password" field is now accepted, which should contain a passphrase:: 44 45 e = create_engine('sqlite+pysqlcipher://:testing@/foo.db') 46 47For an absolute file path, two leading slashes should be used for the 48database name:: 49 50 e = create_engine('sqlite+pysqlcipher://:testing@//path/to/foo.db') 51 52A selection of additional encryption-related pragmas supported by SQLCipher 53as documented at https://www.zetetic.net/sqlcipher/sqlcipher-api/ can be passed 54in the query string, and will result in that PRAGMA being called for each 55new connection. Currently, ``cipher``, ``kdf_iter`` 56``cipher_page_size`` and ``cipher_use_hmac`` are supported:: 57 58 e = create_engine('sqlite+pysqlcipher://:testing@/foo.db?cipher=aes-256-cfb&kdf_iter=64000') 59 60 61Pooling Behavior 62---------------- 63 64The driver makes a change to the default pool behavior of pysqlite 65as described in :ref:`pysqlite_threading_pooling`. The pysqlcipher driver 66has been observed to be significantly slower on connection than the 67pysqlite driver, most likely due to the encryption overhead, so the 68dialect here defaults to using the :class:`.SingletonThreadPool` 69implementation, 70instead of the :class:`.NullPool` pool used by pysqlite. As always, the pool 71implementation is entirely configurable using the 72:paramref:`.create_engine.poolclass` parameter; the :class:`.StaticPool` may 73be more feasible for single-threaded use, or :class:`.NullPool` may be used 74to prevent unencrypted connections from being held open for long periods of 75time, at the expense of slower startup time for new connections. 76 77 78""" # noqa 79 80from __future__ import absolute_import 81 82from .pysqlite import SQLiteDialect_pysqlite 83from ... import pool 84from ...engine import url as _url 85 86 87class SQLiteDialect_pysqlcipher(SQLiteDialect_pysqlite): 88 driver = "pysqlcipher" 89 90 pragmas = ("kdf_iter", "cipher", "cipher_page_size", "cipher_use_hmac") 91 92 @classmethod 93 def dbapi(cls): 94 try: 95 from pysqlcipher import dbapi2 as sqlcipher 96 except ImportError as e: 97 try: 98 from pysqlcipher3 import dbapi2 as sqlcipher 99 except ImportError: 100 raise e 101 return sqlcipher 102 103 @classmethod 104 def get_pool_class(cls, url): 105 return pool.SingletonThreadPool 106 107 def connect(self, *cargs, **cparams): 108 passphrase = cparams.pop("passphrase", "") 109 110 pragmas = dict((key, cparams.pop(key, None)) for key in self.pragmas) 111 112 conn = super(SQLiteDialect_pysqlcipher, self).connect( 113 *cargs, **cparams 114 ) 115 conn.execute('pragma key="%s"' % passphrase) 116 for prag, value in pragmas.items(): 117 if value is not None: 118 conn.execute('pragma %s="%s"' % (prag, value)) 119 120 return conn 121 122 def create_connect_args(self, url): 123 super_url = _url.URL( 124 url.drivername, 125 username=url.username, 126 host=url.host, 127 database=url.database, 128 query=url.query, 129 ) 130 c_args, opts = super( 131 SQLiteDialect_pysqlcipher, self 132 ).create_connect_args(super_url) 133 opts["passphrase"] = url.password 134 return c_args, opts 135 136 137dialect = SQLiteDialect_pysqlcipher 138