$app['request']->getQuery() add quote on 1.3.0/1.3.1

Hi.

I have this code

$app->get('/pictures', function() use ($app)
{
    $limit = $app['request']->getQuery('limit', 'int', 10, true);
    $offset = $app['request']->getQuery('offset', 'int', 0, true);

    $phql = "SELECT p.id, p.filename, p.likes FROM pictures p WHERE p.status = 'Active' ORDER BY p.id DESC LIMIT :limit: OFFSET :offset:";

    $pictures = $app->modelsManager->executeQuery(
        $phql,
        array(
            'limit' => $limit,
            'offset' => $offset
        ),
        array(
            'limit' => \Phalcon\Db\Column::BIND_PARAM_INT,
            'offset' => \Phalcon\Db\Column::BIND_PARAM_INT
        )
    );

    $data = array();
    foreach ($pictures as $picture) {
        $data[] = array(
            'id' => $picture->id,
            'filename' => $picture->filename,
            'likes' => $picture->likes
        );
    }

    echo json_encode($data);
});

When i run /pictures?limit=20 for example do i get this error

Uncaught exception 'PDOException' with 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 ''20' OFFSET 0' at line 1' in /media/sf_pattegufv3/index.php:37 Stack trace: #0 [internal function]: PDOStatement->execute() #1 [internal function]: Phalcon\Db\Adapter\Pdo->executePrepared(Object(PDOStatement), Array, Array) #2 [internal function]: Phalcon\Db\Adapter\Pdo->query('SELECT `p`.`id`...', Array, Array) #3 [internal function]: Phalcon\Mvc\Model\Query->_executeSelect(Array, Array, Array) #4 [internal function]: Phalcon\Mvc\Model\Query->execute(Array, Array) #5 /media/sf_pattegufv3/index.php(37): Phalcon\Mvc\Model\Manager->executeQuery('SELECT p.id, p....', Array, Array) #6 [internal function]: {closure}() #7 /media/sf_pattegufv3/index.php(85): Phalcon\Mvc\Micro->handle() #8 {main} thrown in /media/sf_pattegufv3/index.php on line 37

When i don't add limit does it work perfectly with the default value.

Why are you running a query rather than using models?

Are you using MySQL? If so, the syntax of limiting is:

LIMIT :limit:,:offset:


14.6k
edited Mar '14

I am building a small API with Micro, don't see the need to use models.

and, limit is still a string. and with

$app->get('/pictures', function() use ($app)
{
    $limit = $app['request']->getQuery('limit', 'int', 10);
    $offset = $app['request']->getQuery('offset', 'int', 0);

    if($limit > 50) {
        $response = new Phalcon\Http\Response();
        $response->setStatusCode(403, 'Forbidden');
    }

    $phql = "SELECT p.id, p.filename, p.likes FROM pictures p WHERE p.status = 'Active' ORDER BY p.id DESC LIMIT :limit:,:offset:";

    $pictures = $app->modelsManager->executeQuery(
        $phql,
        array(
            'limit' => $limit,
            'offset' => $offset
        ),
        array(
            'limit' => \Phalcon\Db\Column::BIND_PARAM_INT,
            'offset' => \Phalcon\Db\Column::BIND_PARAM_INT
        )
    );

    $data = array();
    foreach ($pictures as $picture) {
        $data[] = array(
            'id' => $picture->id,
            'filename' => $picture->filename,
            'likes' => $picture->likes
        );
    }

    echo json_encode($data);
});

do i not get any result now with the default values, and with something set with ?limit=20 do i get

Fatal error: Uncaught exception 'PDOException' with 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 ''20'' at line 1' in /media/sf_pattegufv3/index.php:42 Stack trace: #0 [internal function]: PDOStatement->execute() #1 [internal function]: Phalcon\Db\Adapter\Pdo->executePrepared(Object(PDOStatement), Array, Array) #2 [internal function]: Phalcon\Db\Adapter\Pdo->query('SELECT `p`.`id`...', Array, Array) #3 [internal function]: Phalcon\Mvc\Model\Query->_executeSelect(Array, Array, Array) #4 [internal function]: Phalcon\Mvc\Model\Query->execute(Array, Array) #5 /media/sf_pattegufv3/index.php(42): Phalcon\Mvc\Model\Manager->executeQuery('SELECT p.id, p....', Array, Array) #6 [internal function]: {closure}() #7 /media/sf_pattegufv3/index.php(138): Phalcon\Mvc\Micro->handle() #8 {main} thrown in /media/sf_pattegufv3/index.php on line 42