DBAL-1451: QueryParsing fails when using parameters inside PostgreSQL arrays

[doctrinebot]

Jira issue originally created by user andreas@mop.koeln:

The following is giving me hard errors on postgres:

$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);

$result = $conn->executeQuery('SELECT project_user_id FROM roles_project_users_nodes GROUP BY project_user_id HAVING array_agg(role_id) @> ARRAY[:test]::integer[]', ["test" => [1,2,3,4,5,6,7]], ["test" => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);

var_dump($result->fetchAll());

The error:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[08P01]: <>: 7 ERROR: bind message supplies 0 parameters, but prepared statement "pdo_stmt_00000001" requires 1' in /home/code/dbalvspostgres/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:91
Stack trace:
#0 /home/code/dbalvspostgres/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(91): PDOStatement->execute(Array)
#1 /home/code/dbalvspostgres/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(830): Doctrine\DBAL\Driver\PDOStatement->execute(Array)
#2 /home/code/dbalvspostgres/index.php(16): Doctrine\DBAL\Connection->executeQuery('SELECT project_...', Array, Array)
#3 {main}

Next exception 'Doctrine\DBAL\Driver\PDOException' with message 'SQLSTATE[08P01]: <>: 7 ERROR: bind message supplies 0 parameters, but prepared statement "pdo_stmt_00000001" requires 1' in /home/code/dbalvspostgres/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:93
Stack trace:
#0 /home/code/dbalvspostgr in /home/code/dbalvspostgres/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php on line 91

The problem here is some code which is trying to find strings inside the query.

http://www.doctrine-project.org/jira/browse/[DBAL-552](http://www.doctrine-project.org/jira/browse/DBAL-552) introduced some changes for special MS-SQL string literals (SELECT [a literal string] seems to be perfect MSSQL). This however breaks the above postgres query.

When removing the "[" from the regexp in SQLParserUtils::getUnquotedStatementFragments everything is fine again.

I am unsure how to proceed here :S

[doctrinebot]

Comment created by andreas@mop.koeln:

ah 552 is actually related to MySQL but somebody made some copy pasta here:

https://github.com/doctrine/dbal/blob/master/tests/Doctrine/Tests/DBAL/SQLParserUtilsTest.php#L55 and the related commit here 646f479 was indeed sql server related ;)

[morozov]

This must have been fixed by #4397, no longer reproducible on 3.1.x. The query in question is parsed as:

SELECT project_user_id FROM roles_project_users_nodes GROUP BY project_user_id HAVING array_agg(role_id) @> ARRAY[{:test}]::integer[]

The curly braces identify what has been recognized as a parameter (see Doctrine\DBAL\Tests\SQL\ParserTest).

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.