LIMIT keyword on MySQL with prepared statement [duplicate]

by

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

More Related Contents:

  1. Error while using PDO prepared statements and LIMIT in query [duplicate]
  2. PHP – Using PDO with IN clause array
  3. mysqli_stmt::bind_result(): Number of bind variables doesn’t match number of fields in prepared statement
  4. How to use mysqli prepared statements?
  5. MySQLi prepared statements error reporting [duplicate]
  6. Example of how to use bind_result vs get_result
  7. MySQL Prepared statements with a variable size variable list
  8. mysqli: can it prepare multiple queries in one statement?
  9. Pagination using MySQL LIMIT, OFFSET
  10. Is mysql_real_escape_string() broken?
  11. Why is using a mysql prepared statement more secure than using the common escape functions?
  12. mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables
  13. pdo prepared statements with wildcards
  14. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  15. How can I Use Prepared Statements in CodeIgniter
  16. Insert multiple rows with PDO prepared statements
  17. Replacing mysql_* functions with PDO and prepared statements
  18. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  19. using nulls in a mysqli prepared statement
  20. Calling stored procedure with Out parameter using PDO
  21. When *not* to use prepared statements?
  22. Is mysql_real_escape_string() necessary when using prepared statements?
  23. PDO error: SQLSTATE[HY000]: General error: 2031
  24. How to prepare statement for update query? [duplicate]
  25. WordPress prepared statement with IN() condition
  26. Use of PDO in classes [duplicate]
  27. How can I select rows in MySQL starting at a given row number?
  28. Using Prepared Statement, how I return the id of the inserted row?
  29. PHP/MySQL Pagination
  30. Using MySQL’s TIMESTAMP vs storing timestamps directly

Leave a Comment