PDO prepared statement causes an error in LIMIT clause
For compatibility purposes, PDO will just emulate prepared statements by substituting placeholders with actual data, instead of sending them to the server separately, unless told otherwise. And with “lazy” binding (using array in execute()), PDO will treat every parameter as a string. As a result, the prepared LIMIT ?,?
query becomes LIMIT '10', '10'
which is invalid syntax that causes query to fail.
This issue can be solved either
-
by turning emulation mode off (as MySQL can sort all placeholders properly):
$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
-
by binding and setting proper type (PDO::PARAM_INT) explicitly:
$stm = $pdo->prepare('SELECT * FROM table LIMIT ?, ?'); $stm->bindValue(1, $limit_from,PDO::PARAM_INT); $stm->bindValue(2, $per_page,PDO::PARAM_INT); $stm->execute(); $data = $stm->fetchAll();