PDO quoting integers in the query.What should I do now?

Question

This question has been asked before.I have the following queries

$start=150;
$sql='select * from articles where category=? limit ?,5';
$query=$con->prepare($sql)
$query->execute(array($_GET['category'],$start));

and I am getting the error (due to quoting of 150):

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 ''150',5' at line 1.

This has been reported as a bug and later closed as bogus.What should I do now?Is there any workaround?

EDIT

Why I do not run into the same issue, when I have a query like

$sql='select * from articles where id=?';
$query=$con->prepare($sql);
$query->execute(array($id));

It isn't an issue when binding against `id=?` because MySQL doesn't care if that is quoted. The solution to using it in `LIMIT` as identified both in the answer below and in the linked question is to explicitly `bindParam()` to force `PDO::PARAM_INT` instead of passing an array to `execute()` which will send strings (and quote them) — Michael Berkowski, Feb 07 '14 at 14:37
@MichaelBerkowski: and why is that so special.Shouldn't the behaviour be same in every case.Is the quotes acceptable in case of Primary key columns coz in place of `id` it could have been `user_id` and it still works with quotes around integer? — Naveen, Feb 07 '14 at 14:42
That's a question for MySQL developers. `LIMIT` is MySQL-specific, not implemented in all other RDBMS. You may get different results if you have emulated prepares enabled, and disable it in PDO. — Michael Berkowski, Feb 07 '14 at 14:47

score 4 · Answer 1 · answered Feb 07 '14 at 14:21

4

As PDOStatement::execute documentation says, "All values are treated as PDO::PARAM_STR." You should specify the data types using bindValue or bindParam:

$query->bindValue(1, (int) $_GET['category'], PDO::PARAM_INT);
$query->bindValue(2, $start, PDO::PARAM_INT);
$query->execute();

answered Feb 07 '14 at 14:21

Piotr

321
2
6

@Pitor:If I am not wrong,it means if I have strings only ,then no worries I can directly use `$query->execute(array(string1,string2,string3,string4))`. The only care needs to be taken when I have non-strings and thus use binding specifying their type – Naveen Feb 07 '14 at 14:27

score 0 · Answer 2 · edited May 23 '17 at 12:13

First solution:
As stated in this answer https://stackoverflow.com/a/10014200/1311025

Disable emulated prepares (the MySQL driver has a bug/feature that will make it quote numeric arguments):

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

Use individual bind params with the integer casting option:

$comments->bindParam(1, $post, PDO::PARAM_STR);
$comments->bindParam(2, (int)$min, PDO::PARAM_INT);
$comments->bindParam(3, (int)$min, PDO::PARAM_INT);

Another solution:
As its going to be always an integer, you can avoid more problems with the PDO variable binding, and use intval to retrieve the integer representation of the $start variable, thus adding the same security validation that is added with the variable binding.

$start=150;
$integerStart = intval($start);
$sql='SELECT * FROM articles WHERE category=? LIMIT '.$integerStart.', 5';
$query=$con->prepare($sql)
$query->execute(array($_GET['category']));

PDO quoting integers in the query.What should I do now?

2 Answers2