Prepared statement confuses PostgreSQL ? operator for positional argument

[jplitza]

Bug Report

Q	A
Version	4.3.1
Previous Version if the bug is a regression	no regression

Summary

In PostgreSQL, the following is a valid statement (that should return true):

SELECT '["foo", "bar"]'::jsonb ? 'foo'

See PostgreSQL documentation on JSON operators for details of the ? operator (and the equivalently problematic ?|).

Current behavior

When used in a prepared statement, Doctrine DBAL interprets the operator as denoting a positional parameter. It thus replaces it with $1 (in the above query, number might differ in other queries), which PostgreSQL then complains about, because it requires an operator at that position:

Uncaught Doctrine\DBAL\Driver\PgSQL\Exception: syntax error at or near "$1" in vendor/doctrine/dbal/src/Driver/PgSQL/Exception.php:25

In some other constellation, I had the following exception instead:

Uncaught Doctrine\DBAL\ArrayParameters\Exception\MissingPositionalParameter: Positional parameter at index 0 does not have a bound value.

Expected behavior

Doctrine detects that this isn't a positional parameter and forwards the ? unmodified

How to reproduce

$conn->prepare('SELECT \'["foo", "bar"]\'::jsonb ? \'foo\'');

[jplitza]

Thanks to this SO answer (which is about JDBC having the same problem), I found a viable workaround: Using jsonb_exists('["foo", "bar"]'::jsonb, 'foo') instead (and jsonb_exists_any() instead of ?|).

[derrabus]

I you find a good way to fix this, please send a PR. Otherwise I'd rather document this issue as a known limitation. WDYT?

[derrabus]

Does the same issue appear with the PDO_pgsql driver btw? PDO should have the same problem as we do. It would be interesting to know if and how they solved it.

[jplitza]

I you find a good way to fix this, please send a PR. Otherwise I'd rather document this issue as a known limitation. WDYT?

Since there is a workaround (which I found literally minutes after opening this issue), I agree that documenting this limitation should be enough. I don't know where, though.

Does the same issue appear with the PDO_pgsql driver btw? PDO should have the same problem as we do. It would be interesting to know if and how they solved it.

It does:

$pdo = new PDO('pgsql:host=localhost;dbname=postgres;user=postgres;password=secret');
$stmt = $pdo->prepare('SELECT \'["foo", "bar"]\'::jsonb ? \'foo\';');
$stmt->execute();

leads to

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "$1"
LINE 1: SELECT '["foo", "bar"]'::jsonb $1 'foo';
                                       ^ in /home/jplitza/Code/doctrine_pgsql_repro/test2.php:4

But they have a workaround (source):

As of PHP 7.4.0, question marks can be escaped by doubling them. That means that the ?? string will be translated to ? when sending the query to the database.

So this works as expected:

$pdo = new PDO('pgsql:host=localhost;dbname=postgres;user=postgres;password=secret');
$stmt = $pdo->prepare('SELECT \'["foo", "bar"]\'::jsonb ?? \'foo\';');
$stmt->execute();

[derrabus]

But they have a workaround (source):

As of PHP 7.4.0, question marks can be escaped by doubling them. That means that the ?? string will be translated to ? when sending the query to the database.

So this works as expected:

$pdo = new PDO('pgsql:host=localhost;dbname=postgres;user=postgres;password=secret');
$stmt = $pdo->prepare('SELECT '["foo", "bar"]'::jsonb ?? 'foo';');
$stmt->execute();

We could implement the same behavior, I think. 🤔

[mbeccati]

@derrabus as the author of the RFC, I'm not sure how you could safely tell the difference between a placeholder question mark and an operator question mark...

I think documentation is key.

[derrabus]

@mbeccati So you would advise against adopting your solution in DBAL? I'm confused.

[mbeccati]

@derrabus I suppose the end user should be instructed to escape their "?" operators properly. Having DBAL automatically do that could be tricky, I suppose.

[derrabus]

Having DBAL automatically do that could be tricky, I suppose.

Nobody said DBAL should do that.

[mbeccati]

@derrabus Then I must have misunderstood:

We could implement the same behavior, I think. 🤔

What did you mean by that?

FYI, I've added the following to tests/Driver/PDO/PgSQL/DriverTest.php:

    public function testEscapedJsonOperators(): void
    {
        $connection = $this->connect([]);

        self::assertTrue(
            $connection->query("SELECT '{\"a\": 1}'::jsonb ?? 'a'")->fetchOne()
        );
    }

And the test is passing.