Can't escape quote in orderby with a function

[aleblanc]

When I add a quote in a function in orderBy like this :

$value = " string with quote ' that make error ";
$queryBuilder->orderBy('INSTR(field, '.$this->getEntityManager()->getConnection()->quote($value).')');

This code generate this error :

"[Syntax Error] line 0, col 131: Error: Expected Doctrine\\ORM\\Query\\Lexer::T_CLOSE_PARENTHESIS, got 'that'"

NB: If I remove the quote in the string the query works.

Environnement :

            "name": "gedmo/doctrine-extensions",
            "version": "v3.15.0",

            "name": "doctrine/orm",
            "version": "2.19.5",

[greg0ire]

You are using a DBAL connection method to quote a string that you insert in an ORM query. That seems wrong.

[aleblanc]

I have the same error if I escape the quote manually whitout DBAL connection method :

$queryBuilder->orderBy('INSTR(field, \'string with quote \\\' that make error \')');

[greg0ire]

Have you tried using a prepared statement instead?

[aleblanc]

No, I can't use prepare in my use case, but I think it would work without queryBuilder.

I have tried also with setParameter but I have have this error :

Too many parameters: the query defines 1 parameters and you bound 2

Maybe the problem come from Lexer because if I do that, that work :

$queryBuilder->orderBy('INSTR(field, \'string with quote that make error \')');

Maybe Lexer seems to consider \' as the end of a string.

[greg0ire]

By prepared statement I meant a query that uses parameters. It's IMO better than attempting to do the escaping yourself.

I have tried also with setParameter but I have have this error :

You must be doing something wrong, because I have tried modifying https://github.com/doctrine/orm/blob/3d9af3187f59930972e7fcb90a1d8059a37b8032/tests/Tests/ORM/Functional/OneToOneInverseSideLoadAfterDqlQueryTest.php#L45

so that it uses inverse'a as a parameter, and it seems to parse just fine.

[aleblanc]

Your exemple don't utilise a function, in my exemple I use a function INSTR in the orderby : $queryBuilder->orderBy('INSTR(field, :value )')->setParameter('value', 'string with quote that make error');

[greg0ire]

Well I just tried this:

        $fetchedInverse = $this
            ->_em
            ->createQueryBuilder()
            ->select('inverse')
            ->from(InverseSide::class, 'inverse')
            ->andWhere('inverse.id = LOWER(:id)')
            ->setParameter('id', "inverse'a")
            ->getQuery()
            ->getSingleResult();

It parses fine as well. I also tried

        $fetchedInverse = $this
            ->_em
            ->createQueryBuilder()
            ->select('inverse')
            ->from(InverseSide::class, 'inverse')
            ->andWhere('inverse.id = :id')
            ->setParameter('id', 'inverse')
            ->orderBy('LOWER(:other_param)', 'ASC')
            ->setParameter('other_param', "a'b")
            ->getQuery()
            ->getSingleResult();

That parses just right as well.

[aleblanc]

Indeed with the setParameters, it works, I had a problem in my loop (who remove the orderby parameter), thanks for the help