DDC-354: Set parameter on NativeQuery shouldn't quote numbers #438

New Issue

admin · 2026-01-22T12:38:15+01:00

admin commented

2026-01-22 12:38:15 +01:00

Originally created by @doctrinebot on GitHub (Feb 20, 2010).

Jira issue originally created by user kanundrum:

On a native sql query when a number is passed in setParameter it gets quoted. This messes up the query if one of the parameters is a limit (e.g. LIMIT ?, ? breaks because the integer is quoted).

$query = $this->em->createNativeQuery('SELECT * FROM users LIMIT ?,?',$rsm);
$query->setParameter(1,0);
$query->setParameter(2,20);

//outputs SELECT * FROM users LIMIT '0','20' which isn't correct sql

Originally created by @doctrinebot on GitHub (Feb 20, 2010). Jira issue originally created by user kanundrum: On a native sql query when a number is passed in setParameter it gets quoted. This messes up the query if one of the parameters is a limit (e.g. LIMIT ?, ? breaks because the integer is quoted). ``` $query = $this->em->createNativeQuery('SELECT * FROM users LIMIT ?,?',$rsm); $query->setParameter(1,0); $query->setParameter(2,20); ``` //outputs SELECT \* FROM users LIMIT '0','20' which isn't correct sql

admin added the Improvement label 2026-01-22 12:38:15 +01:00

admin closed this issue

2026-01-22 12:38:15 +01:00

admin commented

2026-01-22 12:38:17 +01:00

@doctrinebot commented on GitHub (Feb 28, 2010):

Comment created by @guilhermeblanco:

That's a limitation of PDO and it's out of scope of Doctrine.

Unfortunately, we cannot fix it.

@doctrinebot commented on GitHub (Feb 28, 2010): Comment created by @guilhermeblanco: That's a limitation of PDO and it's out of scope of Doctrine. Unfortunately, we cannot fix it.

admin commented

2026-01-22 12:38:17 +01:00

@doctrinebot commented on GitHub (Mar 19, 2010):

Comment created by romanb:

PDO should certainly not quote the numbers but I cant reproduce it. It works fine for me. Doctrine certainly isnt quoting them afaik.

@doctrinebot commented on GitHub (Mar 19, 2010): Comment created by romanb: PDO should certainly not quote the numbers but I cant reproduce it. It works fine for me. Doctrine certainly isnt quoting them afaik.

admin commented

2026-01-22 12:38:19 +01:00

@doctrinebot commented on GitHub (Mar 19, 2010):

Comment created by romanb:

OK, got it reproduced with MySql. This needs investigation.

@doctrinebot commented on GitHub (Mar 19, 2010): Comment created by romanb: OK, got it reproduced with MySql. This needs investigation.

admin commented

2026-01-22 12:38:19 +01:00

@doctrinebot commented on GitHub (Mar 19, 2010):

Comment created by romanb:

This is definitely related to PDO. I think its because all parameters default to PDO::PARAM_STR.

Doesnt work:

        $stmt = $pdo->prepare('SELECT * FROM DDC425Entity LIMIT ?,?');
        $stmt->bindValue(1, 0);
        $stmt->bindValue(2, 20);
        $stmt->execute();

Works:

        $stmt = $pdo->prepare('SELECT * FROM DDC425Entity LIMIT ?,?');
        $stmt->bindValue(1, 0, \PDO::PARAM_INT);
        $stmt->bindValue(2, 20, \PDO::PARAM_INT);
        $stmt->execute();

This is a real problem.

@doctrinebot commented on GitHub (Mar 19, 2010): Comment created by romanb: This is definitely related to PDO. I think its because all parameters default to PDO::PARAM_STR. Doesnt work: ``` $stmt = $pdo->prepare('SELECT * FROM DDC425Entity LIMIT ?,?'); $stmt->bindValue(1, 0); $stmt->bindValue(2, 20); $stmt->execute(); ``` Works: ``` $stmt = $pdo->prepare('SELECT * FROM DDC425Entity LIMIT ?,?'); $stmt->bindValue(1, 0, \PDO::PARAM_INT); $stmt->bindValue(2, 20, \PDO::PARAM_INT); $stmt->execute(); ``` This is a real problem.

admin commented

2026-01-22 12:38:19 +01:00

@doctrinebot commented on GitHub (Mar 19, 2010):

Comment created by @beberlei:

This is what Guilherme meant with PDO scope.

The only possible fix would be extending the setParameter API for NativeQueries.

@doctrinebot commented on GitHub (Mar 19, 2010): Comment created by @beberlei: This is what Guilherme meant with PDO scope. The only possible fix would be extending the setParameter API for NativeQueries.

admin commented

2026-01-22 12:38:21 +01:00

@doctrinebot commented on GitHub (Mar 19, 2010):

Comment created by romanb:

It is more problematic than that, because the ORM currently does not set explicit binding types when binding parameter values. The handling may depend on the concrete PDO driver implementation since our Postgres tests work fine, even though postgres would immediately complain if any number is quoted.

@doctrinebot commented on GitHub (Mar 19, 2010): Comment created by romanb: It is more problematic than that, because the ORM currently does not set explicit binding types when binding parameter values. The handling may depend on the concrete PDO driver implementation since our Postgres tests work fine, even though postgres would immediately complain if any number is quoted.

admin commented

2026-01-22 12:38:21 +01:00

@doctrinebot commented on GitHub (Mar 19, 2010):

Comment created by romanb:

This is definitely driver-dependant. On Postgres, this works:

        $stmt = $pdo->prepare('SELECT * FROM DDC425Entity LIMIT ? OFFSET ?');
        $stmt->bindValue(1, 0);
        $stmt->bindValue(2, 20);
        $stmt->execute();

Looks like an ugly inconsistency...

@doctrinebot commented on GitHub (Mar 19, 2010): Comment created by romanb: This is definitely driver-dependant. On Postgres, this works: ``` $stmt = $pdo->prepare('SELECT * FROM DDC425Entity LIMIT ? OFFSET ?'); $stmt->bindValue(1, 0); $stmt->bindValue(2, 20); $stmt->execute(); ``` Looks like an ugly inconsistency...

admin commented

2026-01-22 12:38:22 +01:00

@doctrinebot commented on GitHub (Mar 19, 2010):

Comment created by romanb:

I am thinking about possible workarounds/solutions to optionally use explicit binding types.

@doctrinebot commented on GitHub (Mar 19, 2010): Comment created by romanb: I am thinking about possible workarounds/solutions to optionally use explicit binding types.

admin commented

2026-01-22 12:38:23 +01:00

@doctrinebot commented on GitHub (Mar 29, 2010):

Comment created by romanb:

You can now use PDO binding types as well as DBAL mapping types to work around this issue.

use Doctrine\DBAL\Types\Type;
...
$query->setParameter(1, 0, Type::INTEGER);
$query->setParameter(2, 10, Type::INTEGER);

or even just ...

use PDO;
...
$query->setParameter(1, 0, PDO::PARAM_INT);
$query->setParameter(2, 10, PDO::PARAM_INT);

@doctrinebot commented on GitHub (Mar 29, 2010): Comment created by romanb: You can now use PDO binding types as well as DBAL mapping types to work around this issue. ``` use Doctrine\DBAL\Types\Type; ... $query->setParameter(1, 0, Type::INTEGER); $query->setParameter(2, 10, Type::INTEGER); ``` or even just ... ``` use PDO; ... $query->setParameter(1, 0, PDO::PARAM_INT); $query->setParameter(2, 10, PDO::PARAM_INT); ```

admin commented

2026-01-22 12:38:23 +01:00

@doctrinebot commented on GitHub (Mar 29, 2010):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Mar 29, 2010): Issue was closed with resolution "Fixed"

admin referenced this issue

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

[PR #438] [CLOSED] Fixed Parser problem for SELECT (((3))) as .... #8194

admin referenced this issue

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

[PR #438] Fixed Parser problem for SELECT (((3))) as .... #8199

Sign in to join this conversation.