Question 329081 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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 "nnParameterised 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search