Compiling SQLCipher for PHP
Table of Contents
Mydex CIC explains SQLCipher, a version of SQLite with an encryption layer, in the specific context of compiling it for use as a drop-in replacement for the PHP SQLite (and PDO driver) library.
#
Introduction
##
What is SQLCipher?
SQLCipher is an open source extension to SQLite, made and maintained by the organisation Zetetic.
Essentially, the SQLCipher product is a copy of SQLite, but with an encryption layer added on top.
This provides an encrypted SQLite database which must be ‘unlocked’ with a secret value (through the use of a PRAGMA statement), in order to then use as if it was a normal SQLite database. Without unlocking the database with the secret value, the data is unreadable and cannot be used by a traditional SQLite tool.
##
How does Mydex CIC use SQLCipher?
Mydex CIC provides Personal Data Stores to individuals which are free for them to use, within which they can store personal information in a safe and secure fashion.
They can also consent to sharing that information (and what level of access, e.g read-only or write) to third parties (‘subscribers’) who integrate into the Mydex CIC platform via its APIs.
Crucially, this places the individual at the centre of a consent pattern that ensures they are in control over who has access to their data and what they can do with it.
At a low, technical level, these Personal Data Stores make use of the SQLCipher software (among other things) to keep the data encrypted at rest.
##
What is this article about?
SQLCipher is very popular in the mobile app ecosystem, but historically, Zetetic also provided instructions on how to integrate it with PHP, e.g for web application use. Mydex CIC had been using those instructions since PHP 5.3 days in 2012!
At some point in 2017 or later, Zetetic removed their public PHP-related documentation, as they felt they couldn’t handle support requests about it. However, it still works great, even with PHP 8.2 and earlier!
Searching information about how to do it has been challenging - not just for us, but others too. You can see that the community frequently asks for it or expresses confusion as to why the documentation was pulled:
- SQLCipher on Windows with PHP
- SQLCipher integration into PHP
- Any plans for PHP 8
- PHP SQLCipher page is 404 error
- SQLCipher with PHP
We thought we’d aid the community by writing up how we do it. If you’re reading this infrastructure blog, we assume you are an engineer, familiar with at least Docker, if not also PHP, and ideally also able to understand the scripts we’ll show you that compile and generate a binary package for easy installation. Hopefully it helps you!
#
Compiling SQLCipher into PHP
Most operating systems provide an already-compiled copy of PHP. At Mydex CIC, we use Ubuntu for some of our infrastructure, and specifically the PHP packages provided by the terrific independent provider Sury.
Those compiled versions of PHP provide all the usual extensions that you’d need - including the standard SQLite3 extension for PHP. However, to use SQLCipher instead of the ’native’ SQLite, we first need to recompile the SQLite extension with the SQLcipher-enabled alternative.
##
Using Docker
To make it easier and keep our environment clean and repeatable, we do our compilation/build steps in Docker. The Dockerfile looks like this:
1FROM ubuntu:20.04
2
3# Set timezone to prevent interactive build
4ENV TZ=Europe/London
5RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone
6RUN apt-get update
7RUN apt-get install -y tzdata
8
9# Copy a local configs folder (containing 'control and 'config.m4') into the image
10COPY configs /usr/local/src/sqlcipher-configs
11
12# Copy a local tests folder (containing 'test_sqlcipher_write.php'
13# and 'test_sqlcipher_read.php') into the image
14COPY tests /usr/local/src/sqlcipher-tests
15
16# Copy the compile script over
17COPY scripts/compile-sqlcipher.sh /scripts/
18
19# Parametise SQLCipher version
20ARG SQLCIPHER_VERSION
21ENV SQLCIPHER_VERSION=$SQLCIPHER_VERSION
22
23# Compile sqlcipher as part of the image build
24RUN /scripts/compile-sqlcipher.sh
25
26# Copy the build script for making the deb package
27COPY scripts/build-sqlcipher-deb.sh /scripts/
28
29# Run the deb build script every time a container starts
30ENTRYPOINT /scripts/build-sqlcipher-deb.sh
As you can see, we are building against the Ubuntu 20.04 base image and copying in a bunch of config files and scripts, the details of which which we’ll talk about next.
##
Breaking down the compile script
Let’s first talk about the script compile-sqlcipher.sh. It looks like this:
1#!/bin/bash
2
3set -e
4
5export WORKING_DIR="/usr/local/src/sqlcipher-working"
6export WORKING_DEB_DIR="/usr/local/src/sqlcipher-deb-package"
7export PHP8_LOCAL_DIR="/usr/local/lib/php8"
8export SQLCIPHER_SRC="${WORKING_DIR}/sqlcipher"
9export SQLITE_LIBS="${SQLCIPHER_SRC}/.libs/"
10export SQLCIPHER_LIBDIR_ARG="${SQLITE_LIBS}sqlite3.o"
11export STANDARD_SQLCIPHER_CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_TEMP_STORE=2 -DSQLITE_MAX_VARIABLE_NUMBER=250000"
12export STANDARD_SQLCIPHER_LDFLAGS="-lcrypto -L${SQLITE_LIBS}"
13export SQLITE_CFLAGS="-I${SQLCIPHER_SRC} ${STANDARD_SQLCIPHER_CFLAGS}"
14
15# Clean working directory
16rm -rf "${WORKING_DIR}" "${WORKING_DEB_DIR}" && mkdir -p "${WORKING_DIR}"
17
18# Install build dependencies
19echo ">>> Ensuring all dependencies are installed"
20apt-get update
21apt-get install -y software-properties-common git bc
22DEBIAN_FRONTEND=noninteractive apt-add-repository -y -s ppa:ondrej/php && apt-get update
23DEBIAN_FRONTEND=noninteractive apt-get -y install build-essential php8.2 php8.2-fpm php8.2-cli php8.2-dev libicu-dev libreadline-dev libssl-dev tcl-dev libsqlite3-dev apt-transport-https apt-utils
24
25# Clone SQLcipher
26echo ">>> Fetching SQLCipher and compiling it"
27git clone --branch "v${SQLCIPHER_VERSION}" --single-branch https://github.com/sqlcipher/sqlcipher.git "${SQLCIPHER_SRC}"
28
29# Compile SQLcipher itself. We don't use the compiled libraries ourselves,
30# but the PHP sqlite3 extension depends on the resulting data in the .libs
31# dir that gets created
32builtin cd "${SQLCIPHER_SRC}"
33./configure --enable-tempstore=yes CFLAGS="${STANDARD_SQLCIPHER_CFLAGS}" LDFLAGS="-lcrypto"
34make
35
36# Fetch PHP source
37echo ">>> Fetching PHP source and recompiling SQLite3 module against SQLcipher source"
38builtin cd "${WORKING_DIR}"
39DEBIAN_FRONTEND=noninteractive apt-get -y source php8.2
40PHP_SRC_DIR=$(find "${WORKING_DIR}" -type d -name php8.2-*)
41builtin cd "${PHP_SRC_DIR}/ext/sqlite3"
42
43# Compile SQLite3
44mv config0.m4 config.m4
45phpize
46./configure CFLAGS="${STANDARD_SQLCIPHER_CFLAGS}" LDFLAGS="${STANDARD_SQLCIPHER_LDFLAGS}" --libdir="${SQLCIPHER_LIBDIR_ARG}"
47make
48
49# Fix the pic_object in sqlite3.so so that it links the sqlcipher version of sqlite3 correctly
50sed -i s/"pic_object='.libs\/sqlite3.o'"/"pic_object='.libs\/sqlite3.o\ \/usr\/local\/src\/sqlcipher\-working\/sqlcipher\/.libs\/sqlite3.o'"/g "${PHP_SRC_DIR}/ext/sqlite3/sqlite3.lo"
51
52# Now recompile
53make
54
55#
56# Now we need our version of SQLite's PDO driver in PHP to be SQLcipher-capable.
57#
58# Fetch PDO SQLcipher code
59echo ">>> Cloning PDO SQLcipher"
60builtin cd "${WORKING_DIR}"
61git clone https://github.com/abbat/pdo_sqlcipher.git
62
63cd "${WORKING_DIR}/pdo_sqlcipher"
64mkdir -p build
65# This copies the native PHP pdo_sqlite source code to the PDO sqlite build dir,
66# as well as the SQLcipher version of sqlite3 source code.
67PDO_SQLITE="${PHP_SRC_DIR}/ext/pdo_sqlite"
68cp "${PDO_SQLITE}/"*.c "${PDO_SQLITE}"/*.h build/
69cp "${SQLCIPHER_SRC}/sqlite3.c" build/sqlite3.c
70cp "${SQLCIPHER_SRC}/sqlite3.h" build/sqlite3.h
71
72# The PDO SQLcipher project assumes you want to make PDO connections
73# using a driver named 'sqlcipher' instead of sqlite3. We actually want
74# the latter so that our PHP code doesn't look too unusual, I guess..
75#
76# We need to therefore make our own config.m4 that ensures this code
77# compiles as the 'pdo_sqlite' driver as seen from PHP's perspective.
78cp /usr/local/src/sqlcipher-configs/config.m4 build/config.m4
79
80# Compile the SQLCipher version of PDO SQLite
81echo ">>> Compiling the SQLcipher version of PDO SQLite"
82cd build
83phpize --clean
84phpize
85./configure CFLAGS="${STANDARD_SQLCIPHER_CFLAGS}" LDFLAGS="${STANDARD_SQLCIPHER_LDFLAGS}" --libdir="${SQLCIPHER_LIBDIR_ARG}"
86make
87
88echo ">>> Checking that the compiled sqlite3.so has sqlcipher strings in it"
89strings "${PHP_SRC_DIR}/ext/sqlite3/modules/sqlite3.so" | grep -q sqlcipher
90
91echo ">>> Checking that the compiled pdo_sqlite.so has sqlcipher strings in it"
92strings modules/pdo_sqlite.so | grep -q sqlcipher
We can summarise this compile script as follows:
- Set some environment variables for reuse in the script, as well as important variables needed for compiling SQLCipher itself in the way we want it (such as the CFLAGS and LDFLAGS)
- Install PHP 8.2 and other build dependencies, as well as installing the PHP 8.2 ‘source code’ itself from Sury (with apt-get source php8.2)
- Clone the SQLcipher codebase from Github, and checkout the tag we want (e.g the latest version number)
- Compile the SQLite extension for PHP 8.2, but using the SQLCipher source code for that SQLite base.
- PHP has a separate set of libraries for use with PDO, the database abstraction library. So we also clone a separate Github project called PDO SQLCipher, which compiles SQLCipher as a PDO extension. By default the PDO SQLCipher project adds an ‘sqlcipher://’ driver for interacting with PDO, but for obscure reasons, we modify it such that it totally replaces the ‘sqlite://’ driver with the SQLCipher copy, without having to use a different driver name.
This modification is what the ‘configs/config.m4’ file is used for, which the Dockerfile copies over in advance. It’s a modified version of the one that the PDO SQLCipher git repository provides. That config.m4 looks like this, for us:
dnl $Id$
dnl config.m4 for extension pdo_sqlcipher
dnl vim:et:sw=2:ts=2:
PHP_ARG_ENABLE(pdo_sqlcipher, whether to enable pdo_sqlcipher support,
[ --enable-pdo_sqlcipher Enable pdo_sqlcipher support])
if test "$PHP_PDO_SQLCIPHER" != "no"; then
if test "$PHP_PDO" = "no" && test "$ext_shared" = "no"; then
AC_MSG_ERROR([PDO is not enabled! Add --enable-pdo to your configure line.])
fi
AC_MSG_CHECKING([for PDO includes])
if test -f $abs_srcdir/include/php/ext/pdo/php_pdo_driver.h; then
pdo_inc_path=$abs_srcdir/ext
elif test -f $abs_srcdir/ext/pdo/php_pdo_driver.h; then
pdo_inc_path=$abs_srcdir/ext
elif test -f $phpincludedir/ext/pdo/php_pdo_driver.h; then
pdo_inc_path=$phpincludedir/ext
elif test -f $prefix/include/php/ext/pdo/php_pdo_driver.h; then
pdo_inc_path=$prefix/include/php/ext
elif test -f $prefix/include/php5/ext/pdo/php_pdo_driver.h; then
pdo_inc_path=$prefix/include/php5/ext
elif test -f $prefix/include/php/5.5/php/ext/pdo/php_pdo_driver.h; then
pdo_inc_path=$prefix/include/php/5.5/php/ext
elif test -f $prefix/include/php/5.6/php/ext/pdo/php_pdo_driver.h; then
pdo_inc_path=$prefix/include/php/5.6/php/ext
else
AC_MSG_ERROR([Cannot find php_pdo_driver.h.])
fi
AC_MSG_RESULT($pdo_inc_path)
php_pdo_sqlcipher_sources_core="pdo_sqlite.c sqlite_driver.c sqlite_statement.c sqlite3.c"
PHP_NEW_EXTENSION(pdo_sqlite, $php_pdo_sqlcipher_sources_core, $ext_shared,,-I$pdo_inc_path)
ifdef([PHP_ADD_EXTENSION_DEP],
[
PHP_ADD_EXTENSION_DEP(pdo_sqlite, pdo)
])
fi
#
Building and testing a SQLCipher deb package
Ubuntu, being a variant of the Debian GNU/Linux operating system, uses ‘deb’ packages to install software. We therefore have another script called ‘build-sqlcipher.sh’ (acting as the ENTRYPOINT when running this Docker image) which makes a .deb package for us.
We can then ship that .deb package around in other containers or servers that need to run SQLCipher.
##
Breaking down the build script
The build-sqlcipher.sh script looks like this:
1#!/bin/bash
2
3set -e
4
5export WORKING_DIR="/usr/local/src/sqlcipher-working"
6export WORKING_DEB_DIR="/usr/local/src/sqlcipher-deb-package"
7export PHP8_LOCAL_DIR="/usr/local/lib/php8"
8export PHP_SRC_DIR=$(find "${WORKING_DIR}" -type d -name php8.2-*)
9
10echo ">>> Making our deb package in ${WORKING_DEB_DIR}"
11mkdir -p ${WORKING_DEB_DIR}/{DEBIAN,etc/php/8.2/mods-available,usr/local/lib/php8}
12
13cp -a "${PHP_SRC_DIR}/ext/sqlite3/modules/sqlite3.so" "${WORKING_DEB_DIR}${PHP8_LOCAL_DIR}/sqlite3.so"
14cp -a "${WORKING_DIR}/pdo_sqlcipher/build/modules/pdo_sqlite.so" "${WORKING_DEB_DIR}${PHP8_LOCAL_DIR}/pdo_sqlite.so"
15
16echo "extension=${PHP8_LOCAL_DIR}/sqlite3.so" > "${WORKING_DEB_DIR}/etc/php/8.2/mods-available/sqlite3.ini"
17echo "extension=${PHP8_LOCAL_DIR}/pdo_sqlite.so" > "${WORKING_DEB_DIR}/etc/php/8.2/mods-available/pdo_sqlite.ini"
18
19NEW_VERSION=$(date +%s)
20
21# Copy in the 'control' metadata file for the deb, and adjust version number
22cp /usr/local/src/sqlcipher-configs/control ${WORKING_DEB_DIR}/DEBIAN/control
23sed -i s/VERSION_CHANGEME/${NEW_VERSION}+v${SQLCIPHER_VERSION}/g ${WORKING_DEB_DIR}/DEBIAN/control
24
25# Build the .deb
26builtin cd /usr/local/src
27dpkg -b ${WORKING_DEB_DIR}
28
29# Rename the .deb file and install it
30mv sqlcipher-deb-package.deb php8.2-sqlcipher-${NEW_VERSION}+v${SQLCIPHER_VERSION}_amd64.deb
31dpkg -i php8.2-sqlcipher-${NEW_VERSION}+v${SQLCIPHER_VERSION}_amd64.deb
32
33ln -sf /etc/php/8.2/mods-available/sqlite3.ini /etc/php/8.2/fpm/conf.d/21-sqlite3.ini
34ln -sf /etc/php/8.2/mods-available/pdo_sqlite.ini /etc/php/8.2/fpm/conf.d/22-pdo_sqlite.ini
35ln -sf /etc/php/8.2/mods-available/sqlite3.ini /etc/php/8.2/cli/conf.d/21-sqlite3.ini
36ln -sf /etc/php/8.2/mods-available/pdo_sqlite.ini /etc/php/8.2/cli/conf.d/22-pdo_sqlite.ini
37
38echo ">>> Testing that SQLite modules load ok in PHP (cli)"
39php8.2 --ri sqlite3
40php8.2 --ri pdo_sqlite
41
42builtin cd "${WORKING_DIR}"
43
44echo ">>> Testing that we can create a SQLCipher-encrypted SQLite database and write to it"
45php8.2 /usr/local/src/sqlcipher-tests/test_sqlcipher_write.php
46
47echo ">>> Testing that the raw SQLite database looks encrypted"
48strings /tmp/test.pds | head
49
50echo ">>> Testing that we can read from the SQLCipher-encrypted SQLite database"
51php8.2 /usr/local/src/sqlcipher-tests/test_sqlcipher_read.php
52
53# Copy the deb package into /opt which will be mounted as a volume from the host
54cp /usr/local/src/php8.2-sqlcipher-${NEW_VERSION}+v${SQLCIPHER_VERSION}_amd64.deb /opt/php8.2-sqlcipher-${NEW_VERSION}+v${SQLCIPHER_VERSION}_amd64.deb
As you can see, it sets similar environment variables as the compile script, but then moves on to setting up the structure needed for making a deb file. It copies the compiled binaries from the previous steps, into this directory structure , along with some metadata, then produces and installs the .deb package.
The ‘control’ metadata file that we copy in (via the Dockerfile), looks like this - a typical, lightweight deb control file. Adjust it to suit your needs:
Section: php
Priority: optional
Maintainer: John Citizen <you@example.com>
Depends: php8.2-common
Homepage: https://www.zetetic.net/sqlcipher
Package: php8.2-sqlcipher
Version: VERSION_CHANGEME
Architecture: amd64
Description: SQLCipher library for PHP.
SQLCipher is an SQLite extension that provides transparent 256-bit AES encryption of
database files. Pages are encrypted before being written to disk and are decrypted
when read back. Due to the small footprint and great performance it’s ideal for
protecting embedded application databases and is well suited for mobile development.
##
Testing
Finally, it runs some PHP test scripts (test_sqlcipher_write.php and test_sqlcipher_read.php) which make sure that it can use the SQLCipher-enabled SQLite PHP library to create and write to a simple encrypted SQLite database, and also read back the contents (knowing the secret key). If this doesn’t work, then it means something went wrong with compiling SQLCipher (or with the files in the deb package).
The ‘write’ script looks like this:
1<?php
2 try {
3 $dbh = new PDO("sqlite:/tmp/test.pds");
4 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
5 $dbh->query("PRAGMA key = 'testing123'");
6 $dbh->exec("PRAGMA cipher_migrate;");
7 $create = "CREATE TABLE IF NOT EXISTS users (name TEXT NOT NULL)";
8 $dbh->exec($create);
9 $insert = "INSERT INTO users(name) VALUES(:name)";
10 $stmt = $dbh->prepare($insert);
11 $stmt->bindValue(":name", "johncitizen");
12 $stmt->execute();
13 echo $dbh->lastInsertId();
14 } catch (PDOException $e) {
15 echo $e->getMessage();
16 exit(1);
17 }
And the read script:
1<?php
2 try {
3 $dbh = new PDO("sqlite:/tmp/test.pds");
4 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
5 $dbh->query("PRAGMA key = 'testing123'");
6 $dbh->exec("PRAGMA cipher_migrate;");
7 $result = $dbh->query("SELECT name FROM users;");
8 foreach ($result as $row) {
9 print_r($row);
10 }
11 $result = $dbh->query("PRAGMA cipher_integrity_check;");
12 foreach ($result as $row) {
13 print_r($row);
14 }
15 $result = $dbh->query("PRAGMA cipher_version;");
16 foreach ($result as $row) {
17 print_r($row);
18 }
19 $result = $dbh->query("PRAGMA compile_options;");
20 foreach ($result as $row) {
21 print_r($row);
22 }
23 } catch (PDOException $e) {
24 echo $e->getMessage();
25 exit(1);
26 }
These tests also show the SQLite variables we set during the compilation step are present.
#
Running the build
On a machine with Docker installed, we can build the image like this, which goes as far as compiling SQLCipher for PHP:
sudo docker build --build-arg SQLCIPHER_VERSION="4.6.0" \
--no-cache --progress=plain -t sqlcipher:dev .
Then we can launch a container instance of the image, which will compile the .deb package and test it:
docker run -t -v "$(pwd)":/opt sqlcipher:dev
You’ll see output from the tests, proving everything is working, such as this:
>>> Testing that we can create a SQLCipher-encrypted SQLite database and write to it
>>> Testing that the raw SQLite database looks encrypted
M[X0
#UgFQ
@Z=6
PmR$"GY
o0C!
67V#
[=0:
or&|
}M}9I
fmt,Oz2
>>> Testing that we can read from the SQLCipher-encrypted SQLite database
Array
(
[name] => johncitizen
[0] => johncitizen
)
Array
(
[cipher_version] => 4.6.0 community
[0] => 4.6.0 community
)
At the end, we should have a file with a name like ‘php8.2-sqlcipher-1717459920+v4.6.0_amd64.deb’ in the working directory of our machine, which we can ‘sudo dpkg -i’ on other machines or add to other containers as part of their build process!
#
Conclusion
Despite Zetetic no longer publishing equivalent documentation, we have found that SQLCipher continues to compile just fine against PHP, provided you make a few quirky tweaks to the PHP source files.
The most important non-standard things we needed to do after fetching the source code, were:
Renaming the config0.m4 file to config.m4, inside the ext/sqlite3 directory of the PHP source code
44mv config0.m4 config.m4
Using sed to replace the path to the ‘pic_object’ in the resulting sqlite3.lo file that is created after having run ‘make’ on it:
50sed -i s/"pic_object='.libs\/sqlite3.o'"/"pic_object='.libs\/sqlite3.o\ \/usr\/local\/src\/sqlcipher\-working\/sqlcipher\/.libs\/sqlite3.o'"/g "${PHP_SRC_DIR}/ext/sqlite3/sqlite3.lo"
We’d like to extend our thanks to Ricardo Guevera who wrote some similar guides on his blog and also as a Github gist. This helped us in particular with some of the sed tricks for PHP 7 and higher, which weren’t required in PHP 5.
We’d also, of course, like to thank Zetetic for continuing to maintain such a terrific piece of software that adds much needed full encryption at rest for SQLite databases. There are many ways to encrypt data in databases, or filesystems, but SQLCipher has been serving us very well for well over a decade. For us, it is extremely stable and, though we haven’t done comprehensive measurements, the overheads of encryption are minimal - every day we handle tens of thousands of large data insertion and read requests which complete within half a second end-to-end (HTTPS and TCP overheads included). Why not try it out?