Convert named parameters to unnamed in prepared statement in PHP

Question

I have an SQL query that uses named placeholders, and an associative array containing corresponding keys and values.

INSERT INTO table1 (a, b, c) VALUES (:a, :b, :c)

[
  'a' => 'ValueA',
  'c' => 'ValueC',
  'b' => 'ValueB'
]

Now I need to convert the query - and the corresponding parameters - to use unnamed (question marks) instead.

INSERT INTO table1 (a, b, c) VALUES (?, ?, ?)

[
  0 => 'ValueA',
  1 => 'ValueB',
  2 => 'ValueC'
]

How can I do this, without risking that the parameters end up in the wrong order?

Answer 1

I would make use of preg_replace_callback and simply append the value of the corresponding parameter to the new array:

/**
 * @return mixed[] An array containing two elements: the modified SQL query (string), 
 *                 and the modified params (array).
 */
function unnameSqlParameters(string $sql, array $params): array
{
    $newParams = [];

    $newSql = preg_replace_callback(
        '/:(\w+)/',
        static function (array $matches) use ($params, &$newParams): string {
            $name = $matches[1];

            if (!array_key_exists($name, $params)) {
                throw new \RuntimeException("Cannot find parameter value for :{$name}.");
            }

            $newParams[] = $params[$name];

            return '?';
        },
        $sql
    );

    return [$newSql, $newParams];
}

Usage:

[$sql, $params] = unnameSqlParameters(
    'INSERT INTO table1 (a, b, c) VALUES (:a, :b, :c)',
    ['a' => 'ValueA', 'c' => 'ValueC', 'b' => 'ValueB']
);

Demo

Note that the used regex, as it is, will also match parameters (things like :a) inside strings. There are ways to prevent that if needed.