Can't escape quote in orderby with a function #7373

New Issue

admin · 2026-01-22T15:50:49+01:00

admin commented

2026-01-22 15:50:49 +01:00

Originally created by @aleblanc on GitHub (May 16, 2024).

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",

Originally created by @aleblanc on GitHub (May 16, 2024). When I add a quote in a function in orderBy like this : ```php $value = " string with quote ' that make error "; $queryBuilder->orderBy('INSTR(field, '.$this->getEntityManager()->getConnection()->quote($value).')'); ``` This code generate this error : <pre> "[Syntax Error] line 0, col 131: Error: Expected Doctrine\\ORM\\Query\\Lexer::T_CLOSE_PARENTHESIS, got 'that'" </pre> NB: If I remove the quote in the string the query works. Environnement : <pre> "name": "gedmo/doctrine-extensions", "version": "v3.15.0", "name": "doctrine/orm", "version": "2.19.5", </pre>

admin closed this issue

2026-01-22 15:50:49 +01:00

admin commented

2026-01-22 15:50:49 +01:00

@greg0ire commented on GitHub (May 16, 2024):

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

@greg0ire commented on GitHub (May 16, 2024): You are using a DBAL connection method to quote a string that you insert in an ORM query. That seems wrong.

admin commented

2026-01-22 15:50:50 +01:00

@aleblanc commented on GitHub (May 16, 2024):

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 \')');

@aleblanc commented on GitHub (May 16, 2024): I have the same error if I escape the quote manually whitout DBAL connection method : <pre> $queryBuilder->orderBy('INSTR(field, \'string with quote \\\' that make error \')'); </pre>

admin commented

2026-01-22 15:50:50 +01:00

@greg0ire commented on GitHub (May 16, 2024):

Have you tried using a prepared statement instead?

@greg0ire commented on GitHub (May 16, 2024): Have you tried using a prepared statement instead?

admin commented

2026-01-22 15:50:50 +01:00

@aleblanc commented on GitHub (May 16, 2024):

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.

@aleblanc commented on GitHub (May 16, 2024): 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 : <pre> Too many parameters: the query defines 1 parameters and you bound 2 </pre> Maybe the problem come from Lexer because if I do that, that work : <pre> $queryBuilder->orderBy('INSTR(field, \'string with quote that make error \')'); </pre> Maybe Lexer seems to consider \\' as the end of a string.

admin commented

2026-01-22 15:50:51 +01:00

@greg0ire commented on GitHub (May 16, 2024):

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 3d9af3187f/tests/Tests/ORM/Functional/OneToOneInverseSideLoadAfterDqlQueryTest.php (L45)

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

@greg0ire commented on GitHub (May 16, 2024): 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.

admin commented

2026-01-22 15:50:51 +01:00

@aleblanc commented on GitHub (May 16, 2024):

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');

@aleblanc commented on GitHub (May 16, 2024): 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');

admin commented

2026-01-22 15:50:51 +01:00

@greg0ire commented on GitHub (May 16, 2024):

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.

@greg0ire commented on GitHub (May 16, 2024): Well I just tried this: ```php $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 ```php $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.

admin commented

2026-01-22 15:50:52 +01:00

@aleblanc commented on GitHub (May 16, 2024):

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

@aleblanc commented on GitHub (May 16, 2024): Indeed with the setParameters, it works, I had a problem in my loop (who remove the orderby parameter), thanks for the help

admin referenced this issue

2026-01-22 16:07:36 +01:00

[PR #7373] Remove remnants of yaml from documentation #10455

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#7373