pdo_sqlite on macOS: SELECT … ORDER BY raises SQLSTATE HY000 error 25 "column index out of range" (Apple ENABLE_API_ARMOR + sqlite3_data_count() guard bug in pdo_sqlite_stmt_get_col)

[belisoful]

Description

Description

On macOS, any SELECT … ORDER BY query executed through pdo_sqlite against Apple's system SQLite library (/usr/lib/libsqlite3.dylib) raises a PDOException with SQLSTATE HY000, error code 25, message "column index out of range". The query is syntactically and semantically valid; the result set is never returned.

The bug has two cooperating causes, both in ext/pdo_sqlite/sqlite_statement.c, and is fully reproducible and root-caused.

---

PHP Version

Reproducible on all PHP versions that link pdo_sqlite against Apple's system SQLite. On macOS 12 (Monterey) and later, Apple no longer ships /usr/bin/php, so the affected PHP is typically one explicitly compiled against /usr/lib/libsqlite3.dylib. Homebrew PHP links against its own libsqlite3 and is not affected.

To confirm which SQLite library the active PHP links against:

php -r "echo SQLite3::version()['versionString'], PHP_EOL;"
otool -L "$(php -r 'echo ini_get("extension_dir");')/pdo_sqlite.so" | grep sqlite

The bug appears when the output shows /usr/lib/libsqlite3.dylib. Apple's build carries the source-ID suffix apl (e.g. ...114dcaapl) and is compiled with SQLITE_ENABLE_API_ARMOR.

---

Operating System

macOS (any version where PHP links pdo_sqlite against /usr/lib/libsqlite3.dylib). Not reproducible on Linux or on macOS with Homebrew PHP.

---

Steps to Reproduce

<?php
$pdo = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec('CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)');
$pdo->exec("INSERT INTO t VALUES (1, 'b'), (2, 'a')");

// Any ORDER BY triggers the exception on affected builds:
$stmt = $pdo->prepare('SELECT id, val FROM t ORDER BY val ASC');
$stmt->execute();           // may raise PDOException here
$rows = $stmt->fetchAll();  // or here
var_dump($rows);

A single-column minimal case also reproduces: SELECT id FROM t ORDER BY id.

---

Expected Results

array(2) {
  [0]=> array(4) { ["id"]=> int(2) ["val"]=> string(1) "a" ... }
  [1]=> array(4) { ["id"]=> int(1) ["val"]=> string(1) "b" ... }
}

---

Actual Results

PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 25 column index out of range

---

Root Cause

Background — Apple's SQLITE_ENABLE_API_ARMOR

Apple's /usr/lib/libsqlite3.dylib is compiled with SQLITE_ENABLE_API_ARMOR. This causes sqlite3_column_type() (and sqlite3_column_text(), sqlite3_column_int(), etc.) to call sqlite3_log(SQLITE_RANGE, "column index out of range") and set db->errCode = 25 when called with an index >= column_count. On standard SQLite builds these calls return SQLITE_NULL / 0 silently. sqlite3_column_name() and sqlite3_column_decltype() do not trigger this behaviour for out-of-bounds indices.

Cause 1 — Apple's internal ORDER BY processing calls sqlite3_column_type() with sort-key indices

When sqlite3_step() processes an ORDER BY query, Apple's SQLite accesses sort-key columns via the public sqlite3_column_type() API using internal result indices that are >= result_column_count. For example, for SELECT id FROM t ORDER BY val ASC, the sort key val is at internal index 1 while result_column_count = 1. SQLITE_ENABLE_API_ARMOR fires and sets db->errCode = SQLITE_RANGE (25).

Cause 2 — pdo_sqlite_stmt_get_col uses sqlite3_data_count() as a bounds guard

In ext/pdo_sqlite/sqlite_statement.c, pdo_sqlite_stmt_get_col begins:

if(colno >= sqlite3_data_count(S->stmt)) {
    pdo_sqlite_error_stmt(stmt);
    return 0;
}
switch (sqlite3_column_type(S->stmt, colno)) {

sqlite3_data_count() returns the number of columns in the current row — it returns 0 after sqlite3_reset(). After pdo_sqlite_stmt_execute sees SQLITE_DONE and calls sqlite3_reset(S->stmt), sqlite3_data_count() drops to 0. Any subsequent call to pdo_sqlite_stmt_get_col with any colno satisfies colno >= 0 == TRUE, causing pdo_sqlite_error_stmt(stmt) to be called unconditionally.

The full chain

1. sqlite3_step() processes ORDER BY sort keys; Apple's ENABLE_API_ARMOR sets db->errCode = SQLITE_RANGE (25).
2. pdo_sqlite_stmt_execute SQLITE_DONE branch calls sqlite3_reset(S->stmt) — data_count drops to 0; db->errCode remains 25.
3. pdo_sqlite_stmt_get_col guard: colno >= sqlite3_data_count() == colno >= 0 == TRUE → pdo_sqlite_error_stmt(stmt) called.
4. pdo_sqlite_error_stmt reads sqlite3_errcode(S->H->db) == 25 (SQLITE_RANGE) → raises PDOException SQLSTATE HY000 / 25 / "column index out of range".

Note on pdo_sqlite_stmt_describe: this function was audited including via git history going back 6+ years. It calls only sqlite3_column_name() and uses sqlite3_column_count() for bounds. It never calls sqlite3_column_type() and is not the bug site.

---

C-Level Proof

A standalone C probe tests /usr/lib/libsqlite3.dylib directly via the SQLite C API without PHP involvement. Building with -DSQLITE_ENABLE_API_ARMOR (which exposes the header declarations; the runtime armor code is always present in Apple's dylib) and calling sqlite3_column_type() with an out-of-bounds index reproducibly sets sqlite3_extended_errcode() to 25. sqlite3_column_name() and sqlite3_column_decltype() called with the same index do not.

Probe FINDINGS SUMMARY output on Apple SQLite:

FINDINGS SUMMARY
============================================================
  Part 1  Hidden sort-key columns / NULL column names   CLEAN
  Part 2  ENABLE_COLUMN_METADATA functions              CLEAN
  Part 3  ENABLE_API_ARMOR out-of-bounds detection      CONFIRMED

The Part 3 output shows, per query shape and per accessor:

sqlite3_column_name     [index=1, before step]  ec_before=0   ec_after=0   (no change)
sqlite3_column_decltype [index=1, before step]  ec_before=0   ec_after=0   (no change)
sqlite3_column_type     [index=1, before step]  ec_before=0   ec_after=25  <--- SQLITE_RANGE SET — API_ARMOR CONFIRMED

---

Proposed Fix

Fix 1 — Correct the bounds guard in pdo_sqlite_stmt_get_col

File: ext/pdo_sqlite/sqlite_statement.c

Change:

if(colno >= sqlite3_data_count(S->stmt)) {

To:

if(colno >= sqlite3_column_count(S->stmt)) {

sqlite3_column_count() returns the prepared statement's column count regardless of execution state and is the correct guard for column metadata access. sqlite3_data_count() is intended to reflect whether a row is currently available, not to bound column index checks.

This fix alone is sufficient to prevent the PDOException from being raised.

Fix 2 — Clear stale error state after sqlite3_reset() in pdo_sqlite_stmt_execute

File: ext/pdo_sqlite/sqlite_statement.c, SQLITE_DONE branch

case SQLITE_DONE:
    php_pdo_stmt_set_column_count(stmt, sqlite3_column_count(S->stmt));
    stmt->row_count = sqlite3_changes(S->H->db);
    sqlite3_reset(S->stmt);
    /* Clear any error state Apple's armored SQLite may have set during
       internal ORDER BY sort-key processing (SQLITE_ENABLE_API_ARMOR). */
    sqlite3_exec(S->H->db, "SELECT 1", NULL, NULL, NULL);
    S->done = 1;
    return 1;

This prevents the stale db->errCode = 25 from persisting to subsequent queries on the same connection.

---

Notes

• The bug is specific to Apple's /usr/lib/libsqlite3.dylib with SQLITE_ENABLE_API_ARMOR. Standard SQLite builds (Linux, Homebrew) are not affected because out-of-bounds sqlite3_column_type() calls return SQLITE_NULL silently.
• Queries without ORDER BY are not affected because no sort-key column access occurs inside sqlite3_step().
• The sqlite3_data_count() guard bug is latent on unarmored builds: for successful non-DONE executions data_count == column_count, so the wrong guard never misfires. It only misfires when data_count drops to 0 after reset and a stale non-zero db->errCode is present.

PHP Version

PHP 8.1.34 (cli) (built: Dec 16 2025 18:33:34) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.1.34, Copyright (c) Zend Technologies
    with Xdebug v3.2.1, Copyright (c) 2002-2023, by Derick Rethans
    with Zend OPcache v8.1.34, Copyright (c), by Zend Technologies

Operating System

macOS 26.3.1. I am seeing some of this behavior without apple's API ARMOR.

[devnexen]

purposely putting sqlite3 too as some of these might apply too (e.g. sqlite3_data_count part).

[belisoful]

Makefile <- remove the ".txt" extension
order_by_column_probe.c
pdo_order_by_isolate.php
README.txt

These are the test files used to diagnose the issue. run make run-meta and make run-armor. use make help to see what it can do.

This affects more than just the Mac version of pdo_sqlite. I'm seeing it in the linux tests too.
BTW, PDO::ATTR_ERRMODE must be PDO::ERRMODE_EXCEPTION.

[belisoful]

Update: minimal reproduction is much smaller, and ORDER BY is a red herring

After more probing I'm retracting much of the diagnosis above, but not the bug output. There is erroneous behavior, exceptions, and issues.

The trigger has nothing to do with ORDER BY, with Apple's SQLITE_ENABLE_API_ARMOR, or with macOS in particular. I can reproduce on Homebrew PHP 8.1.34 / SQLite 3.53.0 (macOS arm64) with this script:

<?php
$pdo = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec('CREATE TABLE t (id INTEGER, name TEXT)');

$stmt = $pdo->prepare('SELECT * FROM t');   // statement has ZERO placeholders
$stmt->bindValue(1, null, PDO::PARAM_NULL); // bind to a non-existent param index
$stmt->execute();                           // PDOException: SQLSTATE[HY000]: General error: 25 column index out of range

No ORDER BY. No rows. No ATTR_EMULATE_PREPARES, no ATTR_STRINGIFY_FETCHES. No Apple SQLite. Just bindValue() to an index that doesn't exist on a prepared statement, followed by execute().

What's actually happening

PDOStatement::bindValue($index, null, PDO::PARAM_NULL) against a statement that has no parameters silently succeeds at bind time, but leaves SQLITE_RANGE (25) on the connection's error state. The next execute() (or fetch) then surfaces it as a PDOException, even though execution itself succeeded.

The following re-explains the initial observations:

• Why ORDER BY "caused" it in my original repro — it didn't. My framework code path (Prado) constructs criteria like new TSqlCriteria('1=1', null); the null second arg gets normalised into a single-element [null] parameters array, and the framework's binder loops over that array calling $stmt->bindValue(1, null, PDO::PARAM_NULL) on the prepared SELECT — which has no placeholders. The SELECT happened to also have ORDER BY, which is what made me chase that thread. Removing ORDER BY from the same code path still triggers the error; removing the spurious bind makes the error vanish regardless of ORDER BY.
• Why other drivers (MySQL, Postgres) don't show it — they appear to silently tolerate binding to an out-of-range placeholder index. Only pdo_sqlite poisons the connection error state.
• Why my "Apple SQLite + ENABLE_API_ARMOR" theory looked plausible — sqlite3_bind_* returning SQLITE_RANGE for out-of-range parameter indices is standard upstream SQLite behaviour, not Apple-specific. API_ARMOR isn't required. I was misled because the bug only surfaced through one specific framework code path that happened to also use ORDER BY.

Where the real defect is

There are two reasonable places to argue the bug lives:

1. PDOStatement::bindValue() / bindParam() should fail at bind time when the index/name doesn't exist in the prepared statement, the way the SQLite C API does (sqlite3_bind_* → SQLITE_RANGE). Silently accepting the bind and surfacing the error from a later, otherwise-successful execute() is the worst of both worlds: callers can't tell the bind was wrong, and the error gets attributed to a query that actually ran fine.

Earlier pdo_sqlite_stmt_get_col analysis is correct

The sqlite3_data_count() vs sqlite3_column_count() analysis posted earlier is appears accurate as written — data_count does drop to 0 after sqlite3_reset() and the bounds check is unsafe — but that path is only reachable once db->errCode is already polluted. With the spurious-bind scenario above, fixing the error-state pollution removes the user-visible symptom without needing to touch the column-count guard. That said, replacing sqlite3_data_count() with sqlite3_column_count() in the guard still seems correct on its own merits.

Without the delayed error from PDOStatement::bindValue()/bindParam()`, this may not have been found.

This does seem like a proper change to use sqlite3_column_count() rather than sqlite3_data_count()

---

Sorry for the noise on the macOS / API_ARMOR angle — the C-level probing was real, but it was probing a downstream symptom rather than the cause.

[SakiTakamachi]

I verified this with all bundled drivers.
For all drivers except ODBC, at least the error messages themselves are accurate.

However, for all drivers other than Firebird, the error occurs at execute().
This is because execute() may receive parameter values as arguments, and depending on the driver, validation of bindings is performed at the time execute() is called.

pdo_mysql:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /mount/sqlite.php:11
Stack trace:
#0 /mount/sqlite.php(11): PDOStatement->execute()
#1 {main}
  thrown in /mount/sqlite.php on line 11

pdo_pgsql:

Fatal error: Uncaught PDOException: SQLSTATE[08P01]: <<Unknown error>>: 7 ERROR:  bind message supplies 1 parameters, but prepared statement "pdo_stmt_00000001" requires 0 in /mount/sqlite.php:11
Stack trace:
#0 /mount/sqlite.php(11): PDOStatement->execute()
#1 {main}
  thrown in /mount/sqlite.php on line 11

pdo_firebird:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: -999 Invalid parameter index in /mount/sqlite.php:10
Stack trace:
#0 /mount/sqlite.php(10): PDOStatement->bindValue(1, NULL, 0)
#1 {main}
  thrown in /mount/sqlite.php on line 10

pdo_dblib:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /mount/sqlite.php:11
Stack trace:
#0 /mount/sqlite.php(11): PDOStatement->execute()
#1 {main}
  thrown in /mount/sqlite.php on line 11

pdo_odbc: No error

[belisoful]

It may have been premature to report this. I spent more time getting to the bottom of the issues today, and made the discovery that was commented. There are still some invalid assumptions in my comments, which I apologize for.

I feel I bit embarrassed reporting this before doing more research, my thought was that sqlite3_column_count() vs sqlite3_data_count may actually be helpful given what those methods apparently do.

[SakiTakamachi]

After debugging with gdb, I found that the error occurs before the query is actually executed—specifically within PDO_PARAM_EVT_EXEC_PRE in pdo_sqlite_stmt_param_hook.

At that point, the error code returned from the database handler is SQLITE_RANGE (25). Looking at the SQLite source code, this corresponds exactly to the error message reported in this case.

https://github.com/sqlite/sqlite/blob/fb7c64299002298b97d8b53c7d838cf645141ce7/src/main.c#L1680

Since the exact issue is not yet clear, I will wait for further feedback.

[belisoful]

I thought there was an issue. apparently not.