PHP PDO prepare statement wouldn't work with colons (:), only with dollar sign variables ($)

Question

I'm writing a pagination script which while leaving irrelevant details for now goes like this:

$starting_limit = ($page-1)*$limit;
$show  = "SELECT * FROM company ORDER BY id ASC LIMIT :starting_limit, :limit";

$r = $pdo->prepare($show);
$r->execute(array(':starting_limit' => $starting_limit, ':limit' => $limit));

And when I run it, I get an error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]:
Syntax error or access violation: 1064 You have an error in your SQL syntax; 
check the manual that corresponds to your MariaDB server version for the right 
syntax to use near ''0', '10'' at line 1' in C:\xampp\htdocs\plain-
pkr\tutorial_pagination.php:34 Stack trace: #0 C:\xampp\htdocs\plain-
pkr\tutorial_pagination.php(34): PDOStatement->execute(Array) #1 {main} thrown 
in C:\xampp\htdocs\plain-pkr\tutorial_pagination.php on line 34

But when i change :parameters to $parameters, it works:

$starting_limit = ($page-1)*$limit;
$show  = "SELECT * FROM company ORDER BY id ASC LIMIT $starting_limit, $limit";

$r = $pdo->prepare($show);
$r->execute();

Why's that?

You need to specify `PDO::PARAM_INT` explicitly with `LIMIT` clause. If I remember correctly, the variables need to be `int` type as well. (Bug might be fixed now) — Pinke Helga, Feb 06 '19 at 02:01

A l w a y s S u n n y · Answer 1 · 2019-02-06T02:09:48.780

Try to make it INT, because your error msg shows you're treating limit and offset as string i.e '0', '10' not as integer because with PDOStatement::execute, All values are treated as PDO::PARAM_STR.

$r->bindValue(':starting_limit', $starting_limit, PDO::PARAM_INT);
$r->bindValue(':limit', $limit, PDO::PARAM_INT);
$r->execute();

OR You can use this to stop making quotes on numeric arguments

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

PHP PDO prepare statement wouldn't work with colons (:), only with dollar sign variables ($)

1 Answers1