#47960 [Com]: PDO-mysql cannot bind LIMIT or OFFSET
| From: | balagod at gmail dot com | Date: | Thu, 30 Apr 2009 06:20:45 +0000 |
| Subject: | #47960 [Com]: PDO-mysql cannot bind LIMIT or OFFSET | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-136536@lists.php.net to get a copy of this message | ||
ID: 47960
Comment by: balagod at gmail dot com
Reported By: dan dot franklin at pearson dot com
Status: Open
Bug Type: PDO related
Operating System: RHEL 5.3
PHP Version: 5.2.9
New Comment:
Hi,
i faced same problem.
i converted my parameters as integer using typecast (int) it's
working.
$stcount=(int)$_POST['stcount'];
$ecount=10;
$selQry="SELECT * from TABLE where status=1 order by add_date limit
:offset,:rowcount";
$stmt=$conn->prepare($selQry);
$stmt->bindParam(':offset',$stcount,PDO::PARAM_INT);
$stmt->bindParam(':rowcount',$ecount,PDO::PARAM_INT);
$stmt->execute();
Previous Comments:
------------------------------------------------------------------------
[2009-04-13 13:50:53] dan dot franklin at pearson dot com
Description:
------------
In moving from PHP 5.1.6 to 5.2.9 I've lost the ability to bind to a
LIMIT or OFFSET parameter against MySQL 5.0; that is, if I have
... LIMIT ?
or
... OFFSET ?
it fails with
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 ''3'' at line 1
(where "3" is the actual value of the parameter bound to the ?).
Reproduce code:
---------------
<?php
$dsn = "mysql:host=localhost;dbname=test";
$dbh = new PDO($dsn, '', '', array());
run_PDO($dbh, "CREATE TABLE t (n int)", array());
run_PDO($dbh, "select n FROM t LIMIT ?", array(3));
run_PDO($dbh, "select n FROM t LIMIT 3 OFFSET ?", array(3));
run_PDO($dbh, "DROP TABLE t", array());
function run_PDO($dbh, $sql, $params) {
$stmt = $dbh->prepare($sql);
if (!$stmt) {
$i = $dbh->errorInfo();
print "Preparing \"$sql\" failed: $i[2]\n";
} else if (!$stmt->execute($params)) {
$i = $stmt->errorInfo();
print "Executing \"$sql\" failed: $i[2]\n";
} else {
print "\"$sql\" succeeded\n";
}
}
?>
Expected result:
----------------
"CREATE TABLE t (n int)" succeeded
"select n FROM t LIMIT ?" succeeded
"select n FROM t LIMIT 3 OFFSET ?" succeeded
"DROP TABLE t" succeeded
(This is what happens with PHP 5.1.6 MySql 5.0.45)
Actual result:
--------------
"CREATE TABLE t (n int)" succeeded
Executing "select n FROM t LIMIT ?" failed: 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 ''3'' at line 1
Executing "select n FROM t LIMIT 3 OFFSET ?" failed: 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 ''3'' at line 1
"DROP TABLE t" succeeded
(With PHP 5.2.9 MySQL 5.0.77)
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=47960&edit=1