加密版 SQLite3：pysqlcipher3

DB Browser for SQLite:SQLite3数据库浏览工具

SQLite3 和其他嵌入式数据库的简单比较:

• SQLite3非常稳定,数据库损坏概率低
• firebird 的用户相比较SQLite3 比较少
• Berkeley DB 5.3之后, Oracle修改了授权协议

windows10安装 pysqlcipher3

参考

• https://github.com/rigglemania/pysqlcipher3
• https://stackoverflow.com/questions/61718992/install-pysqlcipher3-windows
• 编译Windows 64bit平台pysqlcipher3 for Python3.7

a.安装 ActiveTcl8.6 https://www.activestate.com/products/tcl/downloads/

选 Download Builds 里的第一个(显示的是Linux,其实包是windows的): ActiveTcl-8.6.9.8609.2-MSWin32-x64-5ccbd9ac8.exe

默认安装到 C:\ActiveTcl

b.安装 Visual Studio 2019

社区版即可，只须安装“使用C++的桌面开发”相关工具集。

c.安装 OpenSSL , Win64 OpenSSL v1.1.1j MSI

这里假定安装到: C:\OpenSSL-Win64 ,注意注意需要将OpenSSL的dll安装到系统目录(安装程序默认的做法)，如果只是安装在OpenSSL的bin目录的话，使用的时候需要将bin目录加入系统PATH，或者自行拷贝相关dll到调用程序目录。

新建windows系统变量: OPENSSL_CONF C:\OpenSSL-Win64\bin\openssl.cfg

拷贝文件:

in folder C:\OpenSSL-Win64\lib

==================================
save copy 

libcrypto.def and libcrypto.lib

and rename at

libeay32.def and libeay32.lib

===================================

save copy
libssl.def and libssl.lib

and rename at
ssleay32.def and ssleay32.lib

=================================

copy folder
C:\OpenSSL-Win64\include\openssl

to
C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\MSVC\14.28.29333\include

d.下载 sqlcipher 的zip文件

假设这里解压到 D:\sqlcipher-4.4.2

修改 Makefile.msc:

TCC = $(TCC) -DSQLITE_TEMP_STORE=1

===> -I后面有空格吗?!

TCC = $(TCC) -DSQLITE_TEMP_STORE=2 -DSQLITE_HAS_CODEC -I"C:\OpenSSL-Win64\include"

# If ICU support is enabled, add the linker options for it.
#
!IF $(USE_ICU)!=0
LTLIBPATHS = $(LTLIBPATHS) /LIBPATH:$(ICULIBDIR)
LTLIBS = $(LTLIBS) $(LIBICU)
!ENDIF
# <</mark>>

# You should not have to change anything below this line

===>

# If ICU support is enabled, add the linker options for it.
#
!IF $(USE_ICU)!=0
LTLIBPATHS = $(LTLIBPATHS) /LIBPATH:$(ICULIBDIR)
LTLIBS = $(LTLIBS) $(LIBICU)
!ENDIF
# <</mark>>

LTLIBPATHS = $(LTLIBPATHS) /LIBPATH:"C:\OpenSSL-Win64\lib\VC\static"
LTLIBS = $(LTLIBS) libcrypto64MT.lib libssl64MT.lib ws2_32.lib shell32.lib advapi32.lib gdi32.lib user32.lib crypt32.lib


# You should not have to change anything below this line

x64 Native Tools Command Prompt for VS 2019

C:\Program Files (x86)\Microsoft Visual Studio\2019\Community>cd D:\sqlcipher-4.4.2

C:\Program Files (x86)\Microsoft Visual Studio\2019\Community>d:

D:\sqlcipher-4.4.2>

执行:

nmake /f Makefile.msc clean
nmake /f Makefile.msc

输出:

  正在创建库 sqlite3.lib 和对象 sqlite3.exp
        cl -nologo -W4 -DINCLUDE_MSVC_H=1   -DSQLITE_OS_WIN=1 -I. -I. -I.\src -fp:precise -MT -DNDEBUG -D_CRT_SECURE_NO_DEPRECATE -D_CRT_SECURE_NO_WARNINGS -D_CRT_NONSTDC_NO_DEPRECATE -D_CRT_NONSTDC_NO_WARNINGS -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_TEMP_STORE=2 -DSQLITE_HAS_CODEC -I"C:\OpenSSL-Win64\include"  -DSQLITE_MAX_TRIGGER_DEPTH=100  -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_GEOPOLY=1 -DSQLITE_ENABLE_JSON1=1 -DSQLITE_ENABLE_STMTVTAB=1 -DSQLITE_ENABLE_DBPAGE_VTAB=1 -DSQLITE_ENABLE_DBSTAT_VTAB=1 -DSQLITE_ENABLE_BYTECODE_VTAB=1 -DSQLITE_ENABLE_DESERIALIZE=1 -DSQLITE_ENABLE_COLUMN_METADATA=1   -O2 -Zi -Fesqlite3.exe  -DSQLITE_ENABLE_FTS4=1 -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1 -DSQLITE_ENABLE_OFFSET_SQL_FUNC=1 -DSQLITE_ENABLE_DESERIALIZE=1 -DHAVE_READLINE=0 shell.c sqlite3.c  /link /pdb:sqlite3sh.pdb /DEBUG   /NOLOGO /MACHINE:x64   /LIBPATH:"C:\OpenSSL-Win64\lib\VC\static" sqlite3res.lo   libcrypto64MT.lib libssl64MT.lib ws2_32.lib shell32.lib advapi32.lib gdi32.lib user32.lib crypt32.lib
shell.c
shell.c(18365): warning C4819: 该文件包含不能在当前代码页(0)中表示的字符。请将该文件保存为 Unicode 格式以防止数据丢失
shell.c(18378): warning C4819: 该文件包含不能在当前代码页(0)中表示的字符。请将该文件保存为 Unicode 格式以防止数据丢失
sqlite3.c
正在生成代码...
LINK : 没有找到 sqlite3.exe 或上一个增量链接没有生成它；正在执行完全链接

e.下载 pysqlcipher3

解压到 D:\pysqlcipher3-master, 修改目录名为 pysqlcipher3, 在该目录下创建 amalgamation

从 sqlcipher拷贝如下两个文件到 amalgamation 目录下

sqlite3.c sqlite3.h

下载 sqlite-amalgamation,下载文件 sqlite-amalgamation-3340100.zip

创建 /pysqlcipher3/src/python3/sqlcipher, copy 4 files from sqlite-amalgamation-3340100.zip to /pysqlcipher3/src/python3/sqlcipher

shell.c sqlite3.c sqlite.h sqliteext.h

x64 Native Tools Command Prompt for VS 2019

D:\pysqlcipher3> 

python setup.py clean  

python setup.py build_amalgamation

python setup.py install  

输出:

creating dist
creating 'dist\pysqlcipher3-1.0.3-py3.8-win-amd64.egg' and adding 'build\bdist.win-amd64\egg' to it
removing 'build\bdist.win-amd64\egg' (and everything under it)
Processing pysqlcipher3-1.0.3-py3.8-win-amd64.egg
creating c:\python38_64\lib\site-packages\pysqlcipher3-1.0.3-py3.8-win-amd64.egg
Extracting pysqlcipher3-1.0.3-py3.8-win-amd64.egg to c:\python38_64\lib\site-packages
Adding pysqlcipher3 1.0.3 to easy-install.pth file

Installed c:\python38_64\lib\site-packages\pysqlcipher3-1.0.3-py3.8-win-amd64.egg
Processing dependencies for pysqlcipher3==1.0.3
Finished processing dependencies for pysqlcipher3==1.0.3

测试 pysqlcipher3

# pysqlcipher3test.py

from pysqlcipher3 import dbapi2 as sqlite
conn1 = sqlite.connect("test.db")
c1 = conn1.cursor()
c1.execute("PRAGMA key='123456'")
c1.execute("""create table stocks (date text, trans text, symbol text, qty real, price real)""")
c1.execute("""insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)""")
conn1.commit()
c1.close()
conn2 = sqlite.connect("test.db")
c2 = conn2.cursor()
c2.execute("PRAGMA key='123456'")
print(c2.execute("""select * from stocks""").fetchall())
c2.close()

在 peewee 中使用

Sqlcipher backend 参考 http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#sqlcipher-ext 和 https://charlesleifer.com/blog/encrypted-sqlite-databases-with-python-and-sqlcipher/

peewee 使用加密的 sqlite3依赖于上述安装的 pysqlcipher3

# -*- coding: utf-8 -*-
from playhouse.sqlcipher_ext import SqlCipherDatabase # pysqlcipher3 只是 playhouse.sqlcipher_ext 依赖的库
import peewee

# 如果数据库不存在创建数据库
# https://gist.github.com/thedod/11048875#file-readme-md
# https://gist.github.com/thedod/11048875
db = SqlCipherDatabase('my.db', passphrase=p,pragmas={
    'journal_mode': 'wal',  # WAL-mode.
    'cache_size': -64 * 1000,  # 64MB cache.
    'synchronous': 0})

配置参数,参考: http://docs.peewee-orm.com/en/latest/peewee/database.html

pragmas={
    'journal_mode': 'wal',
    'cache_size': -1 * 64000,  # 64MB
    'foreign_keys': 1,
    'ignore_check_constraints': 0,
    'synchronous': 0}