ID: 47960
Updated by: u...@php.net
Reported By: dan dot franklin at pearson dot com
-Status: Open
+Status: Bogus
Bug Type: PDO related
Operating System: RHEL 5.3
PHP Version: 5.2.9
New Comment:
You are not telling PDO what type your parameter is and it defaults to
the safe choice of string. PDO escapes the string and you get the error.
Specify INT and it works.
Previous Comments:
------------------------------------------------------------------------
[2009-04-30 06:20:44] balagod at gmail dot com
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();
------------------------------------------------------------------------
[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
