PHP: Prepare JSON selector to Prevent MySQL Injection

What is the best/proper way to prepare a dynamic MySQL JSON selector for queries, in order to prevent SQL injection? As an example, say I want to execute the following query:

SELECT `key` ->> "$.key.path" AS `setting_value`
FROM `settings`
LIMIT 1

But I want to make the key path dynamic, e.g.:

$sql = <<<SQL
    SELECT `key` ->> "{$path}" AS `setting_value`
    FROM `settings`
    LIMIT 1
SQL;

With conditional values, I can structure the SQL for a prepared statement using PDO, e.g.:

$sql = <<<SQL
    SELECT *
    FROM `settings`
    WHERE `other_field` = :field_val
    LIMIT 1
SQL;

$statement = $this->handle()->prepare($sql);
$statement->execute([':field_val' => 'some val']);
$records = $statement->fetchAll(PDO::FETCH_OBJ);

Adding something like the following to my database adapter (or even to my helper functions) seems rather inelegant and prone to errors/issues:

    public function cleanJsonSelector(string $selector) : string {
        return preg_replace('/[^-\$a-zA-Z0-9_\[\].*\s]/', '', $selector);
    }

Thoughts/help?

Solution

The -> and ->> operators only support string literals. They do not expressions, variables, or parameters.

But you can use any expression — including a parameter placeholder — in the equivalent JSON function JSON_EXTRACT().

The ->> operator is like JSON_UNQUOTE(JSON_EXTRACT(json_doc, path))

$sql = <<<SQL
    SELECT JSON_UNQUOTE(JSON_EXTRACT(`key`, :path)) AS `setting_value`
    FROM `settings`
    LIMIT 1
SQL;

This seems like an unnecessary inconsistency in the functionality of MySQL's JSON operators, but this is how it's implemented in the current MySQL version 8.3.

For what it's worth, it was requested to support expressions after the -> operator (and presumably also the ->> operator) here: https://bugs.mysql.com/bug.php?id=87360

You may want to log into the MySQL bug tracker site and click the "Affects Me" button to hopefully increase the priority of implementing that feature.

If one uses PDO and sets PDO::ATTR_EMULATE_PREPARES => true (the default), then parameters are not true parameters, they are string-substitutions into the SQL query before it is parsed. So what looks like a parameterized query really uses a string literal as far as MySQL is concerned, and the ->> operator is successful.

If one uses PDO and sets PDO::ATTR_EMULATE_PREPARES => false, then parameters placeholders are parsed by MySQL during the prepare step. Since parameters are not supported as the argument to the ->> operator, this will fail.

Mysqli only supports true parameters, so it will always fail.