PHP sqlite3 won't load this extension whereas sqlite3 CLI loads fine

[victorgveloso]

Minimal replication instructions:

/Dockerfile

FROM php:8-apache
RUN apt-get update && apt-get install -y sqlite3 libsqlite3-dev && rm -rf /var/lib/apt/lists/*
COPY src/ /var/www/html/
COPY ini/php.ini /usr/local/etc/php/conf.d/php.ini

/ini/php.ini

sqlite3.extension_dir = /var/www/html/sqlitehash

/src/test.php

<?php
$db = new SQLite3(':memory:');
$db->loadExtension('libsqlite_hashes');
echo $db->querySingle("SELECT hex(sha256('oi'))");

Running

$ mkdir -p /src/sqlitehash/ && cd /src/sqlitehash/
$ wget https://github.com/nyurik/sqlite-hashes/releases/download/v0.9.0/sqlite-hashes-x86_64-unknown-linux-gnu.tar.gz
$ tar -xzvf sqlite-hashes-x86_64-unknown-linux-gnu.tar.gz

Results in the file
/src/sqlitehash/libsqlite_hashes.so is RELEASE 9.0

Running the following command should result in the error as demonstrated:

$ php test.php

Warning: SQLite3::loadExtension(): Unable to load extension at '/var/www/html/sqlitehash/libsqlite_hashes' in /var/www/html/test.php on line 3

php -i | grep sqlite3.extension_dir
sqlite3.extension_dir => /var/www/html/sqlitehash

However, when I run sqlite3 CLI, it just works.

$ cd /var/www/html/sqlitehash && sqlite3
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load ./libsqlite_hashes
sqlite> SELECT sha256('oi')
   ...> ;
��3cLİ/b��e
)���*
     �A�%�w-�
Xԉ
sqlite> SELECT hex(sha256('oi'));
87F633634CC4B02F628685651F0A29B7BFA22A0BD841F725C6772DD00A58D489

Any help would be much appreciated.

[nyurik]

I asked AI about it, so posting its answer here - just in case some of it might help. I have never used an extension from PHP, so if any of this helps, please let me know and/or update the readme to help others?

---

The error occurs because PHP's SQLite3::loadExtension() method requires the full filename, including the extension (e.g., .so on Linux), whereas the SQLite shell automatically appends it. Here's how to fix it:

1. Check the Extension Filename:

   • Ensure the extension file in /var/www/html/sqlitehash is named correctly (e.g., libsqlite_hashes.so).

2. Update the PHP Code:

   • Modify the loadExtension call to include the file extension:

     $db->loadExtension('libsqlite_hashes.so'); // Add .so or the correct extension

3. Verify PHP Configuration:

   • In php.ini, confirm:

     sqlite3.extension_dir = /var/www/html/sqlitehash
     sqlite3.load_extensions = On

4. Check Permissions and Dependencies:

   • Ensure the web server user (e.g., www-data) has read/execute permissions on the file.
   • Use ldd /var/www/html/sqlitehash/libsqlite_hashes.so to check for missing dependencies.

5. Review Error Logs:

   • Check PHP and SQLite error messages for further details after making these changes.

Summary: The main issue is the missing file extension in the loadExtension() call. SQLite's shell automatically appends it, but PHP does not. Including the correct extension (e.g., .so) in the method call should resolve the error.

[victorgveloso]

Unfortunately, I tried all solutions proposed by LLMs before posting this issue. Unlike what the LLM says, PHP's $db->loadExtension() actually assumes the path is relative to the sqlite3.extension_dir defined in php.ini. Hence why I explicitly specify its value as /var/www/html/sqlitehash. Besides, the permissions are correctly set and the ldd command outputs:

$ ldd libsqlite_hashes.so
	linux-vdso.so.1 (0x0000714862c8d000)
	libgcc_s.so.1 => /usr/lib/libgcc_s.so.1 (0x0000714862bae000)
	libc.so.6 => /usr/lib/libc.so.6 (0x00007148629bd000)
	/usr/lib64/ld-linux-x86-64.so.2 (0x0000714862c8f000)

The only error log is the one I shared.

EDIT:

The only thing I though could be an issue is the GLIBC version your tools was compiled with. In the docker container, I have GLIBC 2.31-13+deb11u5 according to the following command:

$ ldd --version
ldd (Debian GLIBC 2.31-13+deb11u5) 2.31
Copyright (C) 2020 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.

However, SQlite CLI succesfully loading your extension kinda contradicts my suspicion.
Do you think you generating a GLIBC 2.31 version of your library would fix the issue?

[victorgveloso]

For my individual project, I used the PHP's hash library to generate the value I need but it's much more resource intensive to do so. I wish there was a way to use your extension in PHP's sqlite.

[nyurik]

@victorgveloso I tried exactly what AI suggested, and it worked for me -- change your code to this (adding the ".so")

$db->loadExtension('libsqlite_hashes.so'); // Add .so or the correct extension

[nyurik]

P.S. My slightly modified test:

# Dockerfile
FROM php:8-apache
RUN apt-get update && apt-get install -y sqlite3 libsqlite3-dev && rm -rf /var/lib/apt/lists/*
COPY libsqlite_hashes.so /var/www/extensions/
COPY test.php /var/www/html/
COPY php.ini /usr/local/etc/php/conf.d/php.ini
RUN cd /var/www/html && php test.php

# php.ini
sqlite3.extension_dir = /var/www/extensions/

<?php
// test.php
$db = new SQLite3(':memory:');
$db->loadExtension('libsqlite_hashes.so');
echo $db->querySingle("SELECT hex(sha256('oi'))");

All these files in the same dir, running it with this:

docker build --progress plain .

Output:

...

#7 [3/6] COPY libsqlite_hashes.so /var/www/extensions/
#7 DONE 0.0s

#8 [4/6] COPY test.php /var/www/html/
#8 DONE 0.0s

#9 [5/6] COPY php.ini /usr/local/etc/php/conf.d/php.ini
#9 DONE 0.0s

#10 [6/6] RUN cd /var/www/html && php test.php
#10 0.161 87F633634CC4B02F628685651F0A29B7BFA22A0BD841F725C6772DD00A58D489
#10 DONE 0.2s

#11 exporting to image
#11 exporting layers
#11 exporting layers 0.2s done
#11 writing image sha256:4777432822f38d4b65e890a94519a277a8acf79daeacd0e8e882d50b0008babf done
#11 DONE 0.2s

[victorgveloso]

You are a life saver!

I feel so stupid now. I tried all of that but with different settings. My project was originally in PHP 7.

And doing the same thing in PHP 7 results in the following error:

$ php test.php

Warning: SQLite3::loadExtension(): Unable to load extension at '/var/www/extensions/libsqlite_hashes.so.so' in /var/www/html/test.php on line 3
Failed: not an error

In the process of fixing it, I upgraded the project to PHP 8 but I never tried specifying the extension again as I assumed it would not work.

I guess it's worth mentioning this in the README. Would you like me to create a PR?

[nyurik]

Sure, it would be good to add a small PHP example to the readme. Thx!

[nyurik]

I guess there is no more actionables on this ticket