Using LD_PRELOAD to run any version of SQLite with Python#

I’ve been wanting to figure this out for ages. This thread on Hacker News plus this Stackoverflow post gave me some initial clues.

The challenge: someone reports a bug in Datasette with a specific version of SQLite. How can I run Datasette against that specific version?

Steps:

Download the source code for that specific version
Build a libsqlite3.so file using that source code
Run Datasette prefixed with LD_PRELOAD=path/to/libsqlite3.so

Obtaining source code for old SQLite versions#

This took a while to figure out. https://www.sqlite.org/chronology.html has a list of links to different releases. From there clicking on one of the dates takes you to a page like this: https://www.sqlite.org/src/timeline?c=cf538e2783&y=ci

From there clicking the “version-X” link takes you through to the timeline page like this: https://www.sqlite.org/src/timeline?r=version-3.8.11.1 - then click on e.g. “check-in: cf538e27” to get to a page like https://www.sqlite.org/src/info/cf538e2783e468bb which has a link to a Tarball and a Zip archive. Download one of those!

Hopefully shorter version: decide which version you want, then navigate to https://www.sqlite.org/src/timeline?t=version-3.8.11.1 and click the check-in link to get to the page that links to the Zip / Tarball.

Here’s the URL to the 3.8.11.1 Tarball: https://www.sqlite.org/src/tarball/cf538e27/SQLite-cf538e27.tar.gz

Building the library#

I first tried this on macOS but I couldn’t get the LD_PRELOAD trick to work. I think this is because of “system integrity protection” (clue in this comment) - so I decided to do it in a Docker Ubuntu container instead.

To start a fresh Ubuntu container:

1
docker run -it -p 8001:8001 ubuntu

I’m using -p 8001:8001 here to allow me to access Datasette running on port 8001 later on.

Now we need python3 and the various build tools (SQLite also needs tcl for the build):

1
apt-get install -y python3 build-essential tcl wget

Annoyingly installing tcl requires us to interactively set the timezone data - I enter 2 for America and then 85 for Los Angeles.

Download and extract the SQLite code:

1
cd /tmp
2
wget https://www.sqlite.org/src/tarball/cf538e27/SQLite-cf538e27.tar.gz
3
tar -xzvf SQLite-cf538e27.tar.gz
4
cd SQLite-cf538e27

Now we can build the extension. The CPPFLAGS are optional but I found I needed them to get the full Datasette test suite to pass later on:

1
CPPFLAGS="-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_RTREE=1" ./configure
2
make

There is no need to run make install here, we just need the compiled library.

This puts the libraries in a .libs directory:

1
root@11b79085483e:/tmp/SQLite-cf538e27# ls -l .libs/
2
total 22660
3
-rw-r--r-- 1 root root 9622164 Jun 17 12:52 libsqlite3.a
4
lrwxrwxrwx 1 root root      16 Jun 17 12:52 libsqlite3.la -> ../libsqlite3.la
5
-rw-r--r-- 1 root root     955 Jun 17 12:52 libsqlite3.lai
6
lrwxrwxrwx 1 root root      19 Jun 17 12:52 libsqlite3.so -> libsqlite3.so.0.8.6
7
lrwxrwxrwx 1 root root      19 Jun 17 12:52 libsqlite3.so.0 -> libsqlite3.so.0.8.6
8
-rwxr-xr-x 1 root root 4021816 Jun 17 12:52 libsqlite3.so.0.8.6
9
-rwxr-xr-x 1 root root  314160 Jun 17 12:52 sqlite3
10
-rw-r--r-- 1 root root 9232752 Jun 17 12:52 sqlite3.o

We can confirm that this worked using the following one-liner:

1
LD_PRELOAD=.libs/libsqlite3.so python3 -c \
2
    'import sqlite3; print(sqlite3.connect(":memory").execute("select sqlite_version()").fetchone())'

Which for me now outputs:

1
('3.8.11.1',)

Try that without the LD_PRELOAD to see the difference:

1
root@11b79085483e:/tmp/SQLite-cf538e27# python3 -c \
2
> 'import sqlite3; print(sqlite3.connect(":memory").execute("select sqlite_version()").fetchone())'
3
('3.31.1',)

Running Datasette#

No need for a virtual environent since we are in a Docker container. We need to install pip:

1
apt-get install -y python3-pip

Then we can install Datasette:

1
python3 -mpip install datasette

Now run it like this:

1
datasette -h 0.0.0.0
2
INFO:     Started server process [10266]
3
INFO:     Waiting for application startup.
4
INFO:     Application startup complete.
5
INFO:     Uvicorn running on http://0.0.0.0:8001 (Press CTRL+C to quit)

We need -h 0.0.0.0 to allow access from outside. On our machine we can now visit it here:

http://0.0.0.0:8001/-/versions

And confirm that the default SQLite version is "version": "3.31.1".

Alternatively, run this to see the versions output without needing to run the server:

1
datasette --get /-/versions.json

Quit Datasette and start it again with the LD_PRELOAD:

1
LD_PRELOAD=.libs/libsqlite3.so datasette -h 0.0.0.0
2
INFO:     Started server process [10274]
3
INFO:     Waiting for application startup.
4
INFO:     Application startup complete.
5
INFO:     Uvicorn running on http://0.0.0.0:8001 (Press CTRL+C to quit)

And now http://0.0.0.0:8001/-/versions reports the following:

1
{
2
    "python": {
3
        "version": "3.8.2",
4
        "full": "3.8.2 (default, Apr 27 2020, 15:53:34) \n[GCC 9.3.0]"
5
    },
6
    "datasette": {
7
        "version": "0.44"
8
    },
9
    "asgi": "3.0",
10
    "uvicorn": "0.11.5",
11
    "sqlite": {
12
        "version": "3.8.11.1",
13
        "fts_versions": [],
14
        "extensions": {},
15
        "compile_options": [
16
            "HAVE_ISNAN",
17
            "SYSTEM_MALLOC",
18
            "TEMP_STORE=1",
19
            "THREADSAFE=1"
20
        ]
21
    }
22
}

Running the Datasette tests#

To run Datasette’s test suite I needed to install a few extra dependencies:

1
apt-get install -y python3-pip git python3.8-venv
2
cd /tmp
3
git clone https://github.com/simonw/datasette
4
cd datasette
5
python3 -m venv venv
6
source venv/bin/activate
7
pip install wheel # So bdist_wheel works in next step
8
pip install -e '.[test]'
9
LD_PRELOAD=/tmp/SQLite-cf538e27/.libs/libsqlite3.so python3 -c

I used this process to track down a bug that was introduced between SQLite 3.36.0 and 3.37.0 in this issue.