Question mark escaping in literal strings still prohibited in PDO?

[stacyharper]

Description

Hey there, hopefully I've missed something:

Introduced with the RFC https://wiki.php.net/rfc/pdo_escape_placeholders, and later by #4217, the question mark escaping RFC give this precision:

The parsing of the “??” character sequence within queries is going to change, but only when they are outside of literal strings or comments. Since such character sequence isn't normally valid SQL syntax, no BC break is expected.

But does this means it is still impossible to build a jsonpath string parameter using PDO?

By example, the following code:

<?php

$user = 'XXXX';
$pass = 'XXXX';

$dbh = new PDO('pgsql:host=localhost;dbname=test', $user, $pass);

$stmt = $dbh->prepare("SELECT jsonb_path_query_array('[\"abc\", \"abd\", \"aBdC\", \"abdacb\", \"babc\"]'::jsonb, '$[*] ?? (@ like_regex ? flag \"i\")');");
$value = '^ab.*c';
$stmt->bindParam(1, $value);

$stmt->execute();

Resulted in this output:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "?" of jsonpath input
LINE 1: ...["abc", "abd", "aBdC", "abdacb", "babc"]'::jsonb, '$[*] ?? (...
                                                             ^ in /home/stacy/tmp/pdophp/index.php:12
Stack trace:
#0 /home/stacy/tmp/pdophp/index.php(12): PDOStatement->execute()
#1 {main}
  thrown in /home/stacy/tmp/pdophp/index.php on line 12

PHP Version

8.4.2

Operating System

Alpine Linux

[damianwadley]

Pretty sure this is just awkwardly worded in the RFC (and not precisely worded in the documentation): question marks outside of strings need to be quoted as of 7.4, but question marks inside strings are fine.

[MorganLOCode]

But note that there are two question marks inside the jsonpath string literal: one indicating a test and one is the parameter. There's no way to distinguish them (certainly no combination of ?/?, ??/?, ?/??, or ??/?? results in valid syntax, and only two of those would make sense anyway).

Throwing stuff at the wall until it stuck got me something that "works" (and I used a heredoc just to remove one layer of quote-escaping:

$stmt = $dbh->prepare(<<<SQL
SELECT jsonb_path_query_array(
    '["abc", "abd", "aBdC", "abdacb", "babc"]'::jsonb,
    ('$[*] ? (@ like_regex "' || ? || '" flag "i")')::jsonpath
);
SQL);

that explicitly isolates the parameter from the string literal, but I need hardly point out how much would break if $value contained a ".

But I can't see a cleaner way to go about it: it's probably not for PDO to be worrying about the syntax of every embedded DSL that might be used in a query.

[damianwadley]

Since when does PostgreSQL support prepared statement parameters being inside string literals?

Let's take PHP out of the equation. If PDO were to parse the query as expected, where the quoted question mark is unwrapped and the parameter edited to be positional, the result would be effectively this, right?

PREPARE stmt(text) AS SELECT jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]'::jsonb, '$[*] ? (@ like_regex $1 flag "i")');
EXECUTE stmt('^ab.*c');

I just ran that through onecompiler.com and got an error:

psql:commands.sql:1: ERROR:  syntax error at or near "$1" of jsonpath input
LINE 1: ...["abc", "abd", "aBdC", "abdacb", "babc"]'::jsonb, '$[*] ? (@...
                                                             ^

Which I would totally expect because the $1 is inside a string literal and so not understood as a statement parameter.

This problem here isn't PDO and its query parsing, but just the usual problem of needing to include a parameter inside a prepared string. Which has the usual two solutions: either make the entire string be the parameter or use string concatenation.

PREPARE stmt(text) AS SELECT jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]'::jsonb, $1::jsonpath);
EXECUTE stmt('$[*] ? (@ like_regex "^ab.*c" flag "i")');

https://onecompiler.com/postgresql/433ybkxfv

PREPARE stmt(text) AS SELECT jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]'::jsonb, ('$[*] ? (@ like_regex "' || $1 || '" flag "i")')::jsonpath);
EXECUTE stmt('^ab.*c');

https://onecompiler.com/postgresql/433ycbuar

[mbeccati]

The parsing of the “??” character sequence within queries is going to change, but only when they are outside of literal strings or comments. Since such character sequence isn't normally valid SQL syntax, no BC break is expected.

Awkwardly worded or not, the above means that if you have ?? inside a string literal, you won't suddenly get a single question mark sent to the database instead.

As others pointed out, trying to put parameters inside string literals is not something supported by either PDO or database engines, and that is not expected to change.

I would propose a third (better) variant to achieve what the OP is looking for:

$stmt = $dbh->prepare(<<<SQL
SELECT jsonb_path_query_array(
    '["abc", "abd", "aBdC", "abdacb", "babc"]'::jsonb,
    ('$[*] ? (@ like_regex ' || ? || ' flag "i")')::jsonpath
)
SQL);
$stmt->execute([
    json_encode('^ab.*c'),
]);

[MorganLOCode]

Pretty much the point I was making: I wouldn't expect PDO to go rummaging around inside string literals to look for things to substitute: if there was going to be anything like that it would have to be provided by the DBMS extending SQL itself.

I would propose a third (better) variant to achieve what the OP is looking for:

$stmt = $dbh->prepare(<<<SQL
SELECT jsonb_path_query_array(
    '["abc", "abd", "aBdC", "abdacb", "babc"]'::jsonb,
    ('$[*] ? (@ like_regex ' || ? || ' flag "i")')::jsonpath
)
SQL);
$stmt->execute([
    json_encode('^ab.*c'),
]);

Looks familiar...

[stacyharper]

I'd like to point out that I am not directly using PDO, but Doctrine ORM, and custom DQL AST lexers, and SQL formater. I don't have much ways to work-around. Ideally, I'd like for ?? to works.

[mbeccati]

@MorganLOCode yes, I've removed the double quotes and used json_encode instead to properly escape the input.

@stacyharper sorry, that won't work: it's simply not possible.