How to load an SQLite extension in PDO?
Tags: php, sqlite, pdo, ffi
Published: 2020-05-28 06:00:00

---
title: How to load an SQLite extension in PDO?
published: true
date: 2020-05-28 6:00:00 UTC
canonical_url: https://www.moxio.com/blog/47/how-to-load-an-sqlite-extension-in-pdo
description: "Recently we ran into the need to load an extension for SQLite using PHP’s PDO database abstraction, which is not supported out of the box. Eventually we found a solution involving FFI and Z-Engine. In this blog post I will take you on a tour of our solution and the black magic behind it."
tags: PHP,SQLite,PDO,FFI
cover_image: https://www.moxio.com/documents/gfx/weblog/blog/.thumbs/610x320/6666cd76f96956469e7be39d750cc7d9_tn_blogpost-phpelephant-lang_1_jpg.jpg
---

Recently we ran into the need to load an extension for SQLite using PHP's PDO database abstraction. Unfortunately, this is not supported out of the box. Eventually we found a solution involving [FFI](https://www.php.net/manual/en/book.ffi.php) and [Z-Engine](https://github.com/lisachenko/z-engine). In this blog post I will take you on a tour of our solution and the _black magic_ behind it.

## The problem

At Moxio we make extensive use of geospatial database functions. Whether it's for infrastructural construction projects or rule management for the living environment, a lot of objects are bound to a specific geographic location and need to be queried as such. In production we run MariaDB as a database engine, which has geospatial support [baked in](https://mariadb.com/kb/en/geographic-geometric-features/). For integration tests however we use the lightweight SQLite database engine, which does not come with geospatial functions out of the box. The [SpatiaLite](https://www.gaia-gis.it/fossil/libspatialite/index) extension for SQLite can be used for this.

Currently we use a homemade database abstraction layer, which uses the [`SQLite3` PHP extension](https://www.php.net/manual/en/book.sqlite3.php) for communicating with SQLite. This PHP extension allows loading SQLite extensions such as SpatiaLite using the [`SQLite3::loadExtension`](https://www.php.net/manual/en/sqlite3.loadextension.php) method. So far all is well.

At the moment we're looking at switching our own database abstraction layer for the [Doctrine DBAL](https://www.doctrine-project.org/projects/dbal.html). It simply doesn't make sense anymore to maintain our own. We'd rather use a battle-tested open source solution and [try to contribute back](https://github.com/doctrine/dbal/issues/3963) some of our code if possible, so that everybody benefits.

The problem is that the Doctrine DBAL implementation is not based on PHP's SQLite3 extension, but basically a wrapper around [PDO](https://www.php.net/manual/en/book.pdo.php), which is already a database abstraction layer. It therefore contains few SQLite-specific features. There are [three `sqlite`-prefixed functions](https://www.php.net/manual/en/ref.pdo-sqlite.php) in PDO, but the possibility to load an SQLite extension is not one of them. This is registered [in the PHP issue tracker](https://bugs.php.net/bug.php?id=64810) and there's [an implementation PR on GitHub](https://github.com/php/php-src/pull/3368), but that one didn't make it into PHP 7.4. So, no Doctrine DBAL for us?

## Exploring solutions

Naturally, we started thinking of solutions. Even if PHP didn't expose the functionality to load extensions, maybe we could still call it directly through the SQLite C API. The [Foreign Function Interface (FFI)](https://www.php.net/manual/en/book.ffi.php) introduced in PHP 7.4 would be a good candidate to do this. However, if we look at the relevant function declaration in the C API we notice a problem:

```c
int sqlite3_load_extension(
  sqlite3 *db,          /* Load the extension into this database connection */
  const char *zFile,    /* Name of the shared library containing extension */
  const char *zProc,    /* Entry point.  Derived from zFile if 0 */
  char **pzErrMsg       /* Put error message here if not 0 */
);
```

The `sqlite3_load_extension` requires a pointer to the database connection we want to load the extension into, which we don't have in PHP. We must find some trick to obtain it from the `PDO` object.

Luckily, through trips to phpCE 2018 and Bulgaria PHP 2019 I've come to know [Alexander Lisachenko](https://twitter.com/lisachenko) and his work on [Z-Engine](https://github.com/lisachenko/z-engine). Z-Engine is a PHP library that uses FFI to hook into the Zend Engine, providing access to PHP's internal structures. Maybe this would allow obtaining the internal `sqlite3` pointer behind a `PDO` object? Indeed, after reaching out Twitter, Alexander [confirmed](https://twitter.com/lisachenko/status/1252168616746893313) that this would probably be possible using Z-Engine.

Hence I set out to try a solution based on Z-Engine, but soon another challenge presented itself. I had hoped that the `PDO` object would have some sort of direct reference (something like a private variable) to the internal SQLite connection. This turned out not to be the case. Instead, the PDO implementation uses a common C trick where the `PDO` object and its internal structures are [aligned next to eachother in memory](https://github.com/php/php-src/blob/d1764ca33018f1f2e4a05926c879c67ad4aa8da5/ext/pdo/php_pdo_driver.h#L510). In the C code they use a function [`php_pdo_dbh_fetch_inner`](https://github.com/php/php-src/blob/d1764ca33018f1f2e4a05926c879c67ad4aa8da5/ext/pdo/php_pdo_driver.h#L520) to shift in memory between the `PDO` object and the internal struct. We cannot use this function from FFI hoewever, since it is inlined and thus not exposed as a separate function to the outside world. I reached out to Alexander once again, and he and [Nikita Popov](https://twitter.com/nikita_ppv) pointed me to a trick involving `handler->offset` to obtain the correct memory offset to implement the memory shift myself.

## Putting it all together

This proved to be the missing puzzle piece. It still took some time to get used to working with PHP FFI, but eventually I got a working solution. First we need to obtain a C pointer to the relevant `PDO` object using Z-Engine:

```php
use ZEngine\Core;
use ZEngine\Reflection\ReflectionValue;

Core::init();

$pdo_refl_value = new ReflectionValue($pdo);
$pdo_obj_pointer = $pdo_refl_value->getRawObject();
```

We then initialize an `FFI` instance to traverse the relevant structures of the PDO-SQLite extension. To keep the header definitions brief, we omit all parts of C structs after the properties we're interested in. Also, we replace pointers to properties before the ones we want (and the `sqlite3` pointer itself for now) by void pointers. This allows us to omit the headers for their real types, and doesn't matter since a pointer takes the same amount of memory regardless of what it points to. Also, we had to add a `struct` keyword in some places to please the PHP FFI parser.

```php
$pdo_sqlite_ffi = \FFI::cdef(<<<CDEF
/* From https://github.com/php/php-src/blob/d1764ca33018f1f2e4a05926c879c67ad4aa8da5/ext/pdo/php_pdo_driver.h#L432 */
struct _pdo_dbh_t {
    /* replaced pdo_dbh_methods* by void* */
    const void *methods;
    void *driver_data;
    /* omitted rest of struct */
};
/* From https://github.com/php/php-src/blob/d1764ca33018f1f2e4a05926c879c67ad4aa8da5/ext/pdo/php_pdo_driver.h#L510 */
struct _pdo_dbh_object_t {
    /* had to insert struct keyword here */
    struct _pdo_dbh_t *inner;
    /* omitted `zend_object std` */
};
/* Adapted from https://github.com/php/php-src/blob/cfc704ea83c56970a72756f7d4fe464885445b5e/ext/pdo_sqlite/php_pdo_sqlite_int.h#L55 */
struct pdo_sqlite_db_handle {
    /* replaced sqlite3* by void* */
    void *db;
    /* omitted rest of struct */
};
CDEF, "pdo_sqlite.so");
```

With that FFI instance we can now perform the memory shift to get the PDO internal data structure corresponding to the exposed `PDO` object. We use the `handle->offset` trick to obtain the correct memory offset for the shift:

```php
// Following https://github.com/php/php-src/blob/d1764ca33018f1f2e4a05926c879c67ad4aa8da5/ext/pdo/php_pdo_driver.h#L520
$offset = $pdo_obj_pointer->handlers->offset;
$pdo_dbh_object_pointer = $pdo_sqlite_ffi->cast("struct _pdo_dbh_object_t*", $pdo_sqlite_ffi->cast("char*", $pdo_obj_pointer) - $offset);
$pdo_dbh_pointer = $pdo_dbh_object_pointer[0]->inner;
```

The driver-specific handles are in the `driver_data` property. In case of the PDO SQLite driver this points to a `pdo_sqlite_db_handle` object, which contains the raw SQLite connection in its `db` property. We obtain a void pointer to that connection as follows:

```php
// Following https://github.com/php/php-src/pull/3368/files#diff-eb26679695f7db289366ef6b03ee25daR729
$pdo_sqlite_db_handle_pointer = $pdo_sqlite_ffi->cast("struct pdo_sqlite_db_handle*", $pdo_dbh_pointer[0]->driver_data);
$sqlite3_void_pointer = $pdo_sqlite_db_handle_pointer[0]->db;
```

Now we set up an FFI object for communicating with the SQLite C API (or at least the parts of it we want to use):

```php
$sqlite3_ffi = \FFI::cdef(<<<CDEF
/* From https://github.com/sqlite/sqlite/blob/278b0517d88d4150830a4ee2c628a55da40d186d/src/sqlite.h.in#L249 */
typedef struct sqlite3 sqlite3;

/* From https://github.com/sqlite/sqlite/blob/278b0517d88d4150830a4ee2c628a55da40d186d/src/sqlite.h.in#L6581 */
int sqlite3_load_extension(
  sqlite3 *db,          /* Load the extension into this database connection */
  const char *zFile,    /* Name of the shared library containing extension */
  const char *zProc,    /* Entry point.  Derived from zFile if 0 */
  char **pzErrMsg       /* Put error message here if not 0 */
);
CDEF, "sqlite3.so");
```

We can then cast our void pointer to an actual `sqlite3` pointer and use it to call functions on the SQLite C API:

```php
$sqlite3_pointer = $sqlite3_ffi->cast("struct sqlite3*", $sqlite3_void_pointer);
$sqlite3_ffi->sqlite3_load_extension($sqlite3_pointer, "mod_spatialite.so", null, null);
```

And _voila_, our extension is loaded!

## Wrapping up

To make this solution easy to use, we published it as a [Composer package](https://packagist.org/packages/moxio/sqlite-extended-api). It hides all underlying Z-Engine and FFI logic and allows loading SQLite extensions through a simple API. We aim to grow this library to also provide access to other SQLite API's that are otherwise not available in PHP. If there are other SQLite features that you'd like to use with PHP, feel free to file a feature request or create a PR!

Finally, please note that Z-Engine should not be considered stable before version 1.0.0. Use it at your own risk.