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