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:');
$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:
$stmt = $pdo->prepare('SELECT id, val FROM t ORDER BY val ASC');
$stmt->execute();           // may raise 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, most relevant!

[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.