PHP: Prepare JSON selector to Prevent MySQL Injection

Question

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?

Answer 1

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.

Answer 2

Unless I misunderstood, what you are asking for is entirely possible; I had no problem using a parameterised path.

docker-compose.yml:

services:
  mdb:
    image: mysql
    ports:
      - '3306:3306'
    environment:
      MYSQL_ROOT_PASSWORD: 123456

test.php:

<?php

// Set up test structure & data
$pdo = new PDO('mysql://localhost', 'root', '123456', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,]);
$pdo->exec('DROP DATABASE IF EXISTS testing');
$pdo->exec('CREATE DATABASE testing');
$pdo->exec('CREATE TABLE testing.t_test ( c_key JSON NULL CHECK (json_valid(`c_key`)));');
$pdo->exec('INSERT INTO testing.t_test (c_key) VALUES (\'{"a":{"b":"c"}}\');');

// Retrieve record with a direct query
$statement = $pdo->query(
    'SELECT c_key ->> "$.a.b" AS setting_value FROM testing.t_test'
);
$records = $statement->fetchAll(PDO::FETCH_OBJ);
echo "Direct query:\n" . var_export($records, true);

// Retrieve record with a parameterised query
$statement = $pdo->prepare(
    'SELECT c_key ->> :path AS `setting_value` FROM testing.t_test LIMIT 1'
);
$statement->execute([':path' => '$.a.b']);  // <---- parameterised path
$records = $statement->fetchAll(PDO::FETCH_OBJ);
echo "\n\nParameterised query:\n" . var_export($records, true);

output:

Direct query:
array (
  0 => 
  (object) array(
     'setting_value' => 'c',
  ),
)

Parameterised query:
array (
  0 => 
  (object) array(
     'setting_value' => 'c',
  ),
)

---

With regards to SQL Injection, I think you should be fine with parameters:

$statement->execute([':path' => '$.a.b\' xx']);

I tried that with a few quotes and common sqli scenarios, the error is always the same:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 3143 Invalid JSON path expression. The error is around character position 6. in <path>index.php:31

Of course you will still need to handle that and similar exceptions for when the path is not valid. I also cannot guarantee that it is fully safe - something in the json handling routines might still have some sort of security problem (buffer overflows, memory corruption, denial of service etc), but probably not of the sql injection kind.

If you can afford it, I'd go for a simple/naive regex match on the path, just to be safe: /^\$[\w.]*$/, depending on how much of the json selector syntax you want to support.