LIMIT keyword on MySQL with prepared statement [duplicate]

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)); 

Answer 1

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);
  

Answer 2

You can declare a specific attribute to resolve the problem.

$dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

The other answer illustrates how it works. PDO just emulates prepares by default, and as you are binding your variables silently, without setting a type, PDO treats them as strings by default. Strings in SQL have to be quoted and escaped - thus you have quotes in your LIMIT clause and a syntax error. While when native prepared statements are used, a database can manage to sort out the appropriate type.