LIMIT keyword on MySQL with prepared statement

Question

``` SELECT id, content, date FROM comment WHERE post = ? ORDER BY date DESC LIMIT ?, ? ``` With PDO (I'm using MAMP 2.0.5 that has Apache 2.2.21, PHP up to 5.3.6, and MySQL 5.5.9) prepared statement this doesn't work, if I change the query with ``` LIMIT 0, 10 ``` it works. I see in the bugs of MySQL that this was a bug in previous version but I can't understand if this is still to be fixed. If this is still a problem, there is a way to select a range of rows in another way? code: ``` $comments = $db->prepare($query); /* where $db is the PDO object */ $comments->execute(array($post, $min, $max)); ```

Accepted Answer

Here's the problem:

```
$comments = $db->prepare($query); 
/* where $db is the PDO object */ 
$comments->execute(array($post, $min, $max));
```

The manual page for PDOStatement::execute() says (emphasis mine):

Parameters

`input_parameters` An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

Thus your parameters are getting inserted as strings, so the final SQL code looks like this:

```
LIMIT '0', '10'
```

This is a particular case where MySQL will not cast to number but trigger a parse error:

```
mysql> SELECT 1 LIMIT 0, 10;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT 1 LIMIT '0', '10';
ERROR 1064 (42000): 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', '10'' at line 1
```

What docs have to say:

The `LIMIT` clause can be used to constrain the number of rows returned by the `SELECT` statement. `LIMIT` takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

Within prepared statements, `LIMIT` parameters can be specified using ? placeholder markers.

Within stored programs, `LIMIT` parameters can be specified using integer-valued routine parameters or local variables.

Your choices include:

Bind parameters one by one so you can set a type:

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

Do not pass those values as parameters:

```
$query = sprintf('SELECT id, content, date
    FROM comment
    WHERE post = ?
    ORDER BY date DESC
    LIMIT %d, %d', $min, $max);
```

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

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

LIMIT keyword on MySQL with prepared statement

Solution

Problem

Related problems