Press "Enter" to skip to content

Using SQLCipher to encrypt SQLite database in Python (Windows 64bits)

Using SQLite encrypted database with SQLCipher

Step 1 – Compile our own sqlite3.dll which will be used by Python

Gather all the stuff needed to build SQLCipher in Windows :

  • MSVC compiler which could be found on Microsoft website here.
  • ActiveTCL which could be found on ActiveTCL website here.
  • OpenSSL for windows which could be downloaded here (doesn’t download the light version). Install-it in a directory without special characters.
  • The source code of SQLCipher from Github. It’s recommended to clone the repository in a directory without special characters.
git clone https://github.com/sqlcipher/sqlcipher.git
Here it is !

Configuration of the build (edit Makefile.msc)

Change

# Flags controlling use of the in memory btree implementation
#
# SQLITE_TEMP_STORE is 0 to force temporary tables to be in a file, 1 to
# default to file, 2 to default to memory, and 3 to force temporary
# tables to always be in memory.
#
TCC = $(TCC) -DSQLITE_TEMP_STORE=1
RCC = $(RCC) -DSQLITE_TEMP_STORE=1

Into

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

And add after this :

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

the two lines :

# 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:\Dev\Tools\OpenSSL-Win64\lib\VC\static"
LTLIBS = $(LTLIBS) libcrypto64MT.lib libssl64MT.lib ws2_32.lib shell32.lib advapi32.lib gdi32.lib user32.lib crypt32.lib

Build SQLCipher

In start menu launch the x64 Native Tools Command Prompt

Go to the appropriate directory and launch

nmake /f Makefile.msc

It will build two new files

In order to install in Python the new version of sqlite3 (which support encryption) you will need to copy these two files in your environment directory (Python\Library\bin).

Copy / Replace the existing sqlite3.dll and sqlite3.exe

Step 2 – Use it in Python

connection = sqlite3.connect(path)
connection.cursor().execute("PRAGMA key='your_password'")