Parameters used in LIMIT and OFFSET in MySQL get quoted and quoted wrong

[sma27]

Bug Report

Q	A
Version	4.2.2

pdo_mysql driver

Summary

Double quotes parameters in LIMIT and OFFSET

Current behavior

MySQL query like the following:

$query = "
SELECT *
FROM my_table
LIMIT :limit OFFSET :offset";

$qb->executeQuery($query, ['limit' => 10, 'offset' => 2]); 

Throws an exception. It appears the SQL is being run as:

SELECT *
FROM my_table
LIMIT ''10'' OFFSET ''2''

My temporary fix was not to use parameters in the limit or offset, but shouldn't this be supported?

Expected behavior

The parameters passed in to the executeQuery functions are int parameters. I wouldn't expect them to be treated as strings.

SELECT *
FROM my_table
LIMIT 10 OFFSET 2

This is what I'd expect the SQL to be.

[sma27]

It works if I pass in a types array, but it should be able to detect this in my opinion.

$qb->executeQuery($query, ['limit' => 10, 'offset' => 2], ['limit' => \Doctrine\DBAL\ParameterType::INTEGER, 'offset' => \Doctrine\DBAL\ParameterType::INTEGER]);

[morozov]

The default parameter type is string, that's why the values get quoted.

it should be able to detect this in my opinion.

No. The binding type shouldn't be inferred from the input, it should be defined by the query and be static. The default is string.

If you want to build the LIMIT query manually, you should use parameter types. Otherwise, you may consider using QueryBuilder#setMaxResults() and QueryBuilder#setFirstResult() instead:

$qb
    ->setMaxResults(10)
    ->setFirstResult(2)
    ->executeQuery();

Although, there is a known downside that those parameters will be rendered as literal values, not parameters.

[derrabus]

I agree with @morozov here. We can't possibly know what the parameter values are used for, so you'll need to specify the type. Closing as won't fix.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.