How to load sqlite extension in PDO?

Question

First, I want to inform about the case to avoid the misunderstanding.

By sqlite extension, I mention Sqlite's extension like FTS, not PHP's sqlite extension.

I have been using PDO Sqlite in my application, it cannot be changed.

As I saw here, Sqlite extensions can be loaded as query seen below:

SELECT load_extension('xyz.so');
$db = new PDO ( 'sqlite:qwert.db' );
$db->query("SELECT load_extension('myextension.so');");
$db->query("SELECT myfunction(name) FROM table");
$rows = $db->fetchAll(PDO::FETCH_CLASS, 'stdClass');

Note: myfunction is method of myextension

But when I test with this query from PDO, it return "not authorized" message.

For only testing purpose, I tried PHP's Sqlite3 extension to load the extension by using the code below:

$db = new SQLite3('qwer.db');
$db->loadExtension('xyz.so');

It works

As I know that PDO Sqlite hasnot a method like loadExtension for loading extensions

Any idea how can I handle this?

You should add your code how you connect to the sqlite database and that shows how you fire the `SELECT load` query. — hakre, Jan 06 '12 at 10:09
Where does the error occur? When loading the extension or when calling `myfunction` ? — Dan Soap, Jan 06 '12 at 11:43

score 9 · Accepted Answer · edited Jun 24 '20 at 22:06

9

could not find a compiler flag and we have solved it with a quick'n dirty hack in pdo_sqlite extension. patched sqlite_driver.c with sqlite3_enable_load_extension() from sqlite3 API.

--- php-5.3.7.old/ext/pdo_sqlite/sqlite_driver.c    2012-01-06 11:04:44.000000000 -0500
+++ sqlite_driver.c 2012-01-06 08:16:58.000000000 -0500
@@ -718,6 +718,8 @@
        goto cleanup;
    }
 
+   sqlite3_enable_load_extension(H->db, 1);
+
    if (PG(safe_mode) || (PG(open_basedir) && *PG(open_basedir))) {
        sqlite3_set_authorizer(H->db, authorizer, NULL);
    }

edited Jun 24 '20 at 22:06

Dharman

30,962
25
85
135

answered Jan 06 '12 at 16:13

Yagmur

126
2

It's again fixed same issue after 7 years, thanks :) – Umut KIRGÖZ Oct 17 '19 at 06:19

score 2 · Answer 2 · answered Jun 02 '20 at 06:01

I have implemented a library with a solution for this problem for PHP 7.4+ which does not involve recompiling the PDO driver. In short, it uses PHP's FFI extension and Z-Engine (which reads PHP's internal memory structures using FFI) to make a direct call to the SQLite C API. You can find more detailed background information in this blog post. I would not recommend this solution in production yet, but it could be a feasible workaround for test or development workflows.

score 1 · Answer 3 · answered May 08 '20 at 08:50

1

No way present now except workaround mentioned above. If you would want to check progress somewhere in the future, there's an issue on PHP bugtracker.

answered May 08 '20 at 08:50

RollingHog

121
7

How to load sqlite extension in PDO?

3 Answers3

Linked