ID: 47960
User updated by: dan dot franklin at pearson dot com
Reported By: dan dot franklin at pearson dot com
Status: Bogus
Bug Type: PDO related
Operating System: RHEL 5.3
PHP Version: 5.2.9
New Comment:
No, the safe choice for PDO would be to use the native PHP type of the
variable, where there is a corresponding SQL type. That would be
sensible default behavior. I changed my PDO interface library to do
this and it works perfectly. PDO ought to work the same way.
Previous Comments:
------------------------------------------------------------------------
[2009-09-23 20:15:20] u...@php.net
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.
------------------------------------------------------------------------
[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
