Limit sql result on dbal

Question

Im using dbal on Symfony2 to retrieve some info from my table:

$social = $conn->fetchAll('SELECT * FROM page WHERE  brand_id = :brand LIMIT :start,:limit', array('brand'=>$brand, 'start'=>(int) $start, 'limit'=>(int) $limit));

Im getting an error only when I add the last part (LIMIT ....), this make me think that i cant limit the result inside the sql query but outside using some kind of command. How can i solve this?

Error:

An exception occurred while executing 'SELECT * FROM page WHERE brand_id = :brand LIMIT :start,:limit' with params {"brand":1,"start":0,"limit":4}:

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 ''0','4'' at line 1

[PDO prepared statement causes an error in LIMIT statement](http://stackoverflow.com/a/15991623/285587) — Your Common Sense, Jun 17 '13 at 12:05
@YourCommonSense using that solution on Symfony i got: Fatal error: Class 'Done\PunctisBundle\Controller\PDO' not found in /var/www/vhosts/blabla.com/httpdocs/src/Done/PunctisBundle/Controller/AjaxController.php — DomingoSL, Jun 17 '13 at 12:16

score 10 · Accepted Answer · answered Jan 14 '14 at 15:58

10

$statement = $connection->prepare(
    'SELECT ... LIMIT :limit'
);
$statement->bindValue('limit', $limit, \PDO::PARAM_INT);
$statement->execute();

$result = $statement->fetchAll();

answered Jan 14 '14 at 15:58

Aistis

3,695
2
34
34

Is this method still safe from SQL Injection? P.s. im using OCI8 and Oracle, so have to modify it a little. Trying to stick it straight into the SQL String, but its not quite working. http://doctrine-orm.readthedocs.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#quote and http://doctrine-orm.readthedocs.org/projects/doctrine-dbal/en/latest/reference/security.html#right-quoting-escaping-values But at risk of going off topic however, please just answer the SQL injection part of the question. – redfox05 Nov 03 '15 at 11:51
Yes, prepared statements (PDO) are safe from SQL injections, because they are escaped via value binding - the SQL itself is already precompiled. – Aistis Jul 19 '16 at 08:58

score 0 · Answer 2 · answered Apr 27 '17 at 14:21

0

Or you can simply use 3rd argument in the fetchAll($sql, array $params = array(), $types = array()) like that:

$social = $conn->fetchAll('SELECT * FROM page WHERE  brand_id = :brand LIMIT :start,:limit', array('brand'=>$brand, 'start'=>(int) $start, 'limit'=>(int) $limit), array(\PDO::PARAM_STR, \PDO::PARAM_INT, \PDO::PARAM_INT));

answered Apr 27 '17 at 14:21

Graundas

11
1

1

This doesn't work with limits/offsets and named parameters as of doctrine 2.6.0. I get invalid SQL exceptions. It does work, however, if you use ?'s for parameters. – Frug Jul 28 '17 at 05:09

Limit sql result on dbal

2 Answers2