[DBAL-299] Multiple different interspersed named parameters Created: 06/Jul/12  Updated: 17/Apr/14  Resolved: 07/Jul/12

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.5
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Spinning Top Assignee: Alexander
Resolution: Fixed Votes: 0
Labels: None

PHP 5.3.9-ZS5.6.0, Ubuntu 10.04.4 LTS on VirtualBox 4.1..18 r78361 on Mac OS X version 10.6.8


For this code

$sql = <<<SQL
SELECT 1 as id
WHERE (:foo = 2)
AND (:bar = 3)
AND (:foo = 2)
$rsm = new \Doctrine\Orm\Query\ResultSetMapping();
$rsm->addScalarResult('id', 'id');

$query = $em->createNativeQuery($sql, $rsm);
$query = $query->setParameters(array('foo' => 2, 'bar' => 3));
$result = $query->getResult();

Generates the SQL in DBAL\Connection\executeQuery

SELECT 1 as id
WHERE (? = 2)
AN?bar = 3)
AND (? = 2)

The problem appears to be in DBAL\SQLParserUtils\expandListParameters.

When replacing the named parameters with ?'s an offset is kept to keep track of where to insert the next parameter. This is done per named parameter (all of :foo is replaced then all :bar, etc). This will calculate the incorrect offset if a named parameter(e.g. :bar) is in between instances of another named parameter (e.g. :foo) (i.e. :bar in the sql "(:foo = 2) AND (:bar =3) AND (:foo = 2)") since the offset will be for the TOTAL number of instances of the named parameter (e.g. -6) not the number of instances that occur before the needed replacement (e.g. -3).

This is where the SQL has "AN?bar = 3)" instead of "AND(3 = 3)" the offset is calculated at -6 instead of the proper -3

Comment by Alexander [ 07/Jul/12 ]

Fixed here: https://github.com/doctrine/dbal/commit/78dbf28923059545b24ba753c112560ad59ca89e

Generated at Wed Oct 22 00:13:49 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.