Parametrized PDO query and `LIMIT` clause – not working [duplicate]

by

I just tested a bunch of cases. I’m using PHP 5.3.15 on OS X, and querying MySQL 5.6.12.

Any combination works if you set:

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

All of the following work: you can use either an int or a string; you don’t need to use PDO::PARAM_INT. 

$stmt = $dbh->prepare("select user from mysql.user limit ?");

$int = intval(1);
$int="1";

$stmt->bindValue(1, 1);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindValue(1, '1');
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindValue(1, 1, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindValue(1, '1', PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $int);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $string);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $int, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $string, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

You can also forget about bindValue() or bindParam(), and instead pass either an int or a string in an array argument to execute(). This works fine and does the same thing, but using an array is simpler and often more convenient to code.

$stmt = $dbh->prepare("select user from mysql.user limit ?");

$stmt->execute(array($int));
print_r($stmt->fetchAll());

$stmt->execute(array($string));
print_r($stmt->fetchAll());

If you enable emulated prepares, only one combination works: you must use an integer as the parameter and you must specify PDO::PARAM_INT:

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

$stmt = $dbh->prepare("select user from mysql.user limit ?");

$stmt->bindValue(1, $int, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $int, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

Passing values to execute() doesn’t work if you have emulated prepares enabled.

More Related Contents:

  1. In PHP with PDO, how to check the final SQL parametrized query? [duplicate]
  2. Error while using PDO prepared statements and LIMIT in query [duplicate]
  3. Why does this PDO statement silently fail?
  4. Cleansing User Passwords
  5. How can I use PDO to fetch a results array in PHP?
  6. What are the disadvantages of using persistent connection in PDO
  7. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  8. MySQL check if a table exists without throwing an exception
  9. Error logging, in a smooth way
  10. How to read “fetch(PDO::FETCH_ASSOC);”
  11. How to make PDO run SET NAMES utf8 each time I connect, In ZendFramework
  12. How do I sanitize input with PDO?
  13. Calling stored procedure with Out parameter using PDO
  14. When *not* to use prepared statements?
  15. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  16. Setting a connect timeout with PDO
  17. What is the best way to insert multiple rows in PHP PDO MYSQL?
  18. How to bind LIKE values using the PDO extension?
  19. Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  20. PDO LIMIT and OFFSET [duplicate]
  21. PHP 7 RC3: How to install missing MySQL PDO
  22. I’m getting this error code: Invalid argument supplied for foreach() [duplicate]
  23. How to limit the elements created by explode()
  24. PHP Laravel: No connection could be made because the target machine actively refused it
  25. PDO bindParam into one statement?
  26. PDO Unbuffered queries
  27. escaping column name with PDO
  28. What is the PDO equivalent of function mysql_real_escape_string?
  29. Installing PDO driver on MySQL Linux server
  30. PDO Exception Questions – How to Catch Them

Leave a Comment