getSqlStringForSqlObject() returns an invalid SQL statement with LIMIT and OFFSET clauses

[zfbot]

Jira Information

Original Issue:	ZF2-503
Issue Type:	Bug
Reporter:	Magnus Berglund
Created:	08/26/12
Assignee:	ralphschindler
Components:	Zend\Db

Description

The getSqlStringForSqlObject() function returns a SQL statement where the integers for the LIMIT and OFFSET clauses are quoted, which is not correct and MySQL returns an exception.

In the model/mapper

$adapter = $this->getDbAdapter();
$sql = new Sql($adapter);

$select = $sql->select();
$select->from($this->tableName);
$select->limit(10);

$selectString = $sql->getSqlStringForSqlObject($select);

$selectString returns

SELECT `rules_questions`.* FROM `rules_questions` LIMIT '10'

That seems to be an incorrect SQL statement, there should be no quotations around the LIMIT integer

SELECT `rules_questions`.* FROM `rules_questions` LIMIT 10

So when executing the query

$results = $this->getDbAdapter()->query($selectString, $adapter::QUERY_MODE_EXECUTE);

MySQL returns this exception:

Message:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use
near ''10'' at line 1

The same thing happens when adding an OFFSET clause, the integer is incorrectly quoted.

[zfbot]

(Originally posted by: Magnus Berglund on 08/26/12)

An attempt at a fix: #2244

[zfbot]

(Originally posted by: samsonasik on 09/13/12)

i think ->execute() func can solve this

$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

[zfbot]

This issue was ported from the ZF2 Jira Issue Tracker at
http://framework.zend.com/issues/browse/ZF2-503

Known GitHub users mentioned in the original message or comment:
@ralphschindler, @samsonasik

[woyteck]

This bug is still present in stable 2.2.3 as well as dev-master. Why is that?

[weierophinney]

@woyteck If you're still seeing the bug, please provide a reproduce case so we can verify.

[woyteck]

PHP v. 5.4.16
ZF2 v. 2.2.3
MySQL v. 5.5.27

composer.json:
"require": {
"php": ">=5.3.3",
"zendframework/zendframework": "2.2.3"
}

global.php:
return array(
'service_manager' => array(
'factories' => array(
'Zend\Db\Adapter\Adapter' => 'Zend\Db\Adapter\AdapterServiceFactory',
),
'aliases' => array(
'db' => 'Zend\Db\Adapter\Adapter',
),
),
'db' => array(
'driver' => 'pdo',
'dsn' => 'mysql:dbname=abc;host=127.0.0.1',
'username' => 'abc',
'password' => '123',
'driver_options' => array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES 'UTF8'',
),
),
);

//controller:
$adapter = $this->getServiceLocator()->get('db');

$select = new Select('test_table');
$select->where(array('type' => 'something'));
$select->order('id');
$select->limit(15);
$query = $select->getSqlString($adapter->getPlatform());
echo $query;

results in:

SELECT test_table.* FROM test_table WHERE type = 'something' ORDER BY id ASC LIMIT '15'

The same thing happens when I update to dev-master.

[fyrye]

Caused by library/Zend/Db/Sql/Select.php
Select::processOffset()

    if ($driver) {
        $parameterContainer->offsetSet('offset', $offset, ParameterContainer::TYPE_INTEGER);
        return array($driver->formatParameterName('offset'));
    }

    return array($platform->quoteValue($offset)); //<-- just use $offset

Select::processLimit()

    if ($driver) {
        $sql = $driver->formatParameterName('limit');
        $parameterContainer->offsetSet('limit', $limit, ParameterContainer::TYPE_INTEGER);
    } else {
        $sql = $platform->quoteValue($limit); //<-- just use $limit
    }

[shaselboeck]

Bug is still present in Zend Framework 2.2.10. I have to use this version because of running PHP version 5.3.14.