python3 django==1.11 使用sqlcipher 加密sqlite3

SQLCipher在CentOS 上的编译安装

下载sqlcipher

git clone https://github.com/rigglemania/pysqlcipher3.git

安装依赖包与编译安装

cd sqlcipher
./configure
make && make install

可能会出现openssl的问题：

configure: error: OpenSSL Crypto library not found

解决办法：　yum -y install openssl-devel 

配置环境变量

# vim /etc/profile
追加如下内容
export PATH=$PATH:/usr/local/sqlcipher/bin
# source /etc/profile

验证sqlcipher

sqlcipher 
SQLCipher version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

非编译rpm安装:https://rhel.pkgs.org/8/epel-x86_64/sqlcipher-4.4.3-2.el8.x86_64.rpm.html

pysqlcipher3

下载pysqlcipher3源码

git clone https://github.com/leapcode/pysqlcipher.git

安装pysqlcipher3

cd pysqlcipher3
python3 setup.py install

可能出现的错误

src/python3/connection.h:33:10: 致命错误：sqlcipher/sqlite3.h：No such file or directory
 #include "sqlcipher/sqlite3.h"
出现这个错误是因为编译安装SQLCipher时使用了下面命令：

./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto" --prefix=/usr/local/sqlcipher

后来改成了　./configure　然后　make && make install　就可以了

验证安装

pip3 list 查看是否有　pysqlcipher3

[root@localhost /]# python3
Python 3.6.8 (default, Aug  9 2020, 01:34:50) 
[GCC 8.3.1 20190507 (Red Hat 8.3.1-4)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pysqlcipher3
>>> exit()

安装django-sqlcipher

git clone https://hub.fastgit.org/benjaoming/django-sqlcipher.git

 注意：要用到develop这个分支！！！！

否则会出现Error was: No module named backend.base　问题

Set database engine to sqlcipher.backend in Django · Issue #6 · codasus/django-sqlcipher · GitHub

安装django-sqlcipher

cd django-sqlcipher
python3 setup.py install

安装完需要进行下面操作

1.找到django_sqlcipher-0.1.2-py3.6.egg安装的位置

[root@localhost nfs-license-mainserver]# python3
Python 3.6.8 (default, Aug  9 2020, 01:34:50) 
[GCC 8.3.1 20190507 (Red Hat 8.3.1-4)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> print(sys.path)
['', '/usr/lib64/python36.zip', '/usr/lib64/python3.6', '/usr/lib64/python3.6/lib-dynload', '/usr/local/lib64/python3.6/site-packages', '/usr/local/lib64/python3.6/site-packages/pysqlcipher3-1.0.5-py3.6-linux-x86_64.egg', '/usr/local/lib/python3.6/site-packages', '/usr/local/lib/python3.6/site-packages/django_sqlcipher-0.1.2-py3.6.egg', '/usr/lib64/python3.6/site-packages', '/usr/lib/python3.6/site-packages']
>>> exit()

可以看到是在/usr/local/lib/python3.6/site-packages/django_sqlcipher-0.1.2-py3.6.egg

2.解压django_sqlcipher-0.1.2-py3.6.egg

得到django_sqlcipher-0.1.2-py3.6.egg_FILES文件夹，并进入该文件夹

3.将sqlcipher文件夹拷贝到　django/db/backends/　目录下

注意：如果django/db/backends/　下已经有了sqlcipher文件夹就不需要这一步

我的django目录在/usr/local/lib/python3.6/site-packages/

说明：django/db/backends/　目录下是　django可以用到的数据库引擎

4.修改sqlcipher/backend/base.py

vim /usr/local/lib/python3.6/site-packages/django/db/backends/sqlcipher/backend/base.py

将　from pysqlcipher import dbapi2 as Database　改为　from pysqlcipher3 import dbapi2 as Database　保存

这样　django-sqlcipher　就安装完了

django使用配置sqlcipher数据库引擎

vim settings.py

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'corsheaders',
    'rest_framework',
    'sqlcipher',  # 注册　sqlcipher
    
]

PRAGMA_KEY = '123456'　　＃　配置数据库密码

DATABASES = {
    'default': {
        #'ENGINE': 'django.db.backends.sqlite3',
　　　　　#'NAME': os.path.join(DB_PATH, 'mainDB.sqlite3'),
	　　'ENGINE': 'django.db.backends.sqlcipher.backend',　　＃　使用sqlcipher数据库引擎
        'NAME': r'/root/test3.db',
    }
}

正常启动django项目后去看下数据库

[root@localhost ~]# sqlcipher test3.db 
SQLite version 3.36.0 2021-06-18 18:36:39 (SQLCipher 4.5.0 community)
Enter ".help" for usage hints.
sqlite> .tables　　//　如果不输入配置的密码　无法查看
Error: file is not a database
sqlite> PRAGMA key='123456';　　//　输入配置密码
ok
sqlite> .tables
agent_agentserver           django_migrations         
auditlog_auditlog           django_session            
auth_group                  users_token       
auth_group_permissions      users_user
auth_permission                 
auth_user                       
auth_user_groups                       
auth_user_user_permissions                 
django_admin_log                            
django_content_type       
sqlite> .exit

可以看到，sqlite数据库已经加密了

脚本使用

vim test-sql.py

from pysqlcipher3 import dbapi2 as sqlite
conn = sqlite.connect('test3.db')
c = conn.cursor()
c.execute("PRAGMA key=''")
c.execute("PRAGMA cipher_compatibility = 3")
#c.execute('''create table user (date text, trans text, symbol text, qty real, price real)''')
#c.execute("""insert into user values ('2006-01-05','BUY','RHAT',100,35.14)""")
#conn.commit()
#c.close()

for i in c.execute("select * from user;"):
    print(i[0])
conn.commit()
c.close()

转载请标明出处