Using multiple parameters as arguments for SUBSTRING() fails #6451

New Issue

admin · 2026-01-22T15:33:30+01:00

admin commented

2026-01-22 15:33:30 +01:00

Originally created by @patrick-vandy on GitHub (Apr 21, 2020).

Bug Report

Q	A
BC Break	N/A
Version	2.5.14

Summary

Using multiple named parameters as arguments to SUBSTRING() results in the order of the parameters getting switched around and yields unexpected results.

Current behavior

When using SUBSTRING(:foo, 1, :pos) in a query builder and calling setParameter('foo', 'foobar') and setParameter('pos', 3), the order of the parameters gets switched and the generated PDO query passes 3 as the value of :foo and foobar as the value of :pos.

How to reproduce

Using Oracle Database 12c (12.2.0.1.0):

// in controller
$qb = $this->getDoctrine()->getRepository(Project::class)->createQueryBuilder('p')
    ->select('p.id')
    ->addSelect('SUBSTRING(:foo, 1, :pos) AS substrField')
    ->setParameter('foo', 'foobar')
    ->setParameter('pos', 3)
;

dump($qb->getQuery()->getResult());

This results in an Invalid Number error because foobar is provided as the value of :pos. In the exception, you can see that the generated PDO query is using ? placeholders and has the order of the parameters mixed up:

An exception occurred while executing 'SELECT s0_.PROJ_ID AS PROJ_ID_0, SUBSTR(?, 1, ?) AS SCLR_1 FROM APPUSER.PROJECT s0_' with params [3, "foobar"]:

ORA-01722: invalid number

Switching the parameters around in the setParameter methods yields the expected result:

$qb = $this->getDoctrine()->getRepository(Project::class)->createQueryBuilder('p')
    ->select('p.id')
    ->addSelect('SUBSTRING(:foo, 1, :pos) AS substrField')
    ->setParameter('foo', 3)
    ->setParameter('pos', 'foobar')
;

Note: I have not tested this on other database vendors, so I do not know if this is specific to Oracle or will occur using any database vendors.

Expected behavior

The value of substrField should be foo

Originally created by @patrick-vandy on GitHub (Apr 21, 2020). ### Bug Report | Q | A |------------ | ------ | BC Break | N/A | Version | 2.5.14 #### Summary Using multiple named parameters as arguments to `SUBSTRING()` results in the order of the parameters getting switched around and yields unexpected results. #### Current behavior When using `SUBSTRING(:foo, 1, :pos)` in a query builder and calling `setParameter('foo', 'foobar')` and `setParameter('pos', 3)`, the order of the parameters gets switched and the generated PDO query passes `3` as the value of `:foo` and `foobar` as the value of `:pos`. #### How to reproduce Using Oracle Database 12c (12.2.0.1.0): ```php // in controller $qb = $this->getDoctrine()->getRepository(Project::class)->createQueryBuilder('p') ->select('p.id') ->addSelect('SUBSTRING(:foo, 1, :pos) AS substrField') ->setParameter('foo', 'foobar') ->setParameter('pos', 3) ; dump($qb->getQuery()->getResult()); ``` This results in an `Invalid Number` error because `foobar` is provided as the value of `:pos`. In the exception, you can see that the generated PDO query is using `?` placeholders and has the order of the parameters mixed up: ``` An exception occurred while executing 'SELECT s0_.PROJ_ID AS PROJ_ID_0, SUBSTR(?, 1, ?) AS SCLR_1 FROM APPUSER.PROJECT s0_' with params [3, "foobar"]: ORA-01722: invalid number ``` Switching the parameters around in the `setParameter` methods yields the expected result: ```php $qb = $this->getDoctrine()->getRepository(Project::class)->createQueryBuilder('p') ->select('p.id') ->addSelect('SUBSTRING(:foo, 1, :pos) AS substrField') ->setParameter('foo', 3) ->setParameter('pos', 'foobar') ; ``` _Note: I have not tested this on other database vendors, so I do not know if this is specific to Oracle or will occur using any database vendors._ #### Expected behavior The value of `substrField` should be `foo`

admin referenced this issue

2026-01-22 16:06:03 +01:00

[PR #6451] Allow Symfony 4.0 #9981

Sign in to join this conversation.