Using PDO insert values in the limit clause of an SQL statement?

Question

In my PDO implementation, I am attempting to use an inserted value in the limit clause of the SQL statement:

$sql = "SELECT * FROM table ORDER BY datetime DESC LIMIT :limit";
$params = array(":limit" => 5);
$query = $dbh->prepare($sql);
$query->execute($params);
$result = $query->fetchall(PDO::FETCH_ASSOC);

$params and $query are correctly returned, but $result is empty.

Upon running print_r($query->errorInfo);, I get the following:

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

How can I use PDO's insert values in this query? Am I doing it right?

Are you logging the queries? What is the actual SQL that is produced? — Wes, Jun 03 '11 at 01:57

score 2 · Accepted Answer · edited May 23 '17 at 12:28

2

See PHP PDO bindValue in LIMIT

Basically, you need to cast the limit value to int using intval() when binding.

edited May 23 '17 at 12:28

Community

1
1

answered Jun 03 '11 at 02:04

J.C. Inacio

4,442
2
22
25

Do I have to use bindValue, or is passing in an array of insert values also possible? – Julian H. Lam Jun 03 '11 at 02:32
Great! That worked well. It's now in my helper function, so I don't have to do this all the time. Byebye mysql_real_escape_string()! – Julian H. Lam Jun 03 '11 at 02:45

score -1 · Answer 2 · answered Jun 03 '11 at 01:59

-1

You cannot bind variables into LIMIT clause’s operand (exactly, it probably depends on your database system vendor). Instead, use just string interpolation. :-(

$limit = 5;
$sql = "SELECT * FROM table ORDER BY datetime DESC LIMIT $limit";
$stmt = $dbh->query($sql);
$result = $stmt->fetchall(PDO::FETCH_ASSOC);

answered Jun 03 '11 at 01:59

minhee

5,688
5
43
81

Instead user string interpolation after carefully sanitising the $limit value if you're getting it from outside the system, e.g `$_GET['limit']` or something. – El Yobo Jun 03 '11 at 02:03
Unfortunate... However, string interpolation was what I was trying to avoid in the first place! – Julian H. Lam Jun 03 '11 at 02:05

Using PDO insert values in the limit clause of an SQL statement?

2 Answers2