Parametrized PDO query and `LIMIT` clause - not working

Question

I have query like this:

SELECT imageurl 
FROM entries 
WHERE thumbdl IS NULL 
LIMIT 10;

It works perfectly with PDO and MySQL Workbench (it returns 10 urls as I want).

However I tried to parametrize LIMIT with PDO:

$cnt = 10;
$query = $this->link->prepare("
             SELECT imageurl 
             FROM entries 
             WHERE imgdl is null 
             LIMIT ?
         ");

$query->bindValue(1, $cnt);

$query->execute();

$result = $query->fetchAll(PDO::FETCH_ASSOC);

That returns empty array.

Bill Karwin · Accepted Answer · 2015-10-12T01:55:20.907

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.

i am another downvoter. I downvoted because of: "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, as long as emulated prepares is disabled." The power of prepared statements is about binding! With binding habbit you justcan't never forget escaping or casting. And yes, sometimes you need some extra code to get it working. But: safety first! — Terradon, Oct 11 '15 at 14:26
@Terradon, Thanks for at least admitting to the downvote and explaining your reason. But your reason is incorrect. Yes, binding parameters is important, and binding is what happens in all three cases: bindValue(), bindParam() or pass the parameters as an array to execute(). And in all three cases, binding does _not_ happen if you have PDO configured to use emulated prepares. — Bill Karwin, Oct 11 '15 at 16:58

score 10 · Answer 2 · answered Aug 01 '13 at 22:21

10

By default bindValue binds a string value but limit is an integer, so use PDO::PARAM_INT

$query->bindValue(1, $cnt, PDO::PARAM_INT);

answered Aug 01 '13 at 22:21

Musa

96,336
17
118
137

This one helped me:) – Terradon Oct 11 '15 at 14:27
Hello Musa, how you going? I got one similar question about PDO Limit Placeholders, is it possible to use the named placeholder to Limit inside the execute? https://stackoverflow.com/questions/72081221/error-to-selecet-when-use-pdo-prepared-limit?noredirect=1#comment127363151_72081221 – Sophie May 02 '22 at 05:42

Parametrized PDO query and `LIMIT` clause - not working

2 Answers2

Linked

Related