#40740 [Asn->Fbk]: PDO::execute() errors when parameters are used in LIMIT clause
| From: | felipe@php.net | Date: | Tue, 04 Nov 2008 15:25:02 +0000 |
| Subject: | #40740 [Asn->Fbk]: PDO::execute() errors when parameters are used in LIMIT clause | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-130603@lists.php.net to get a copy of this message | ||
ID: 40740
Updated by: felipe@php.net
Reported By: phpbugs at filofox dot com
-Status: Assigned
+Status: Feedback
Bug Type: PDO related
Operating System: Linux Debian Sarge 3.1
PHP Version: 5.2.1
Assigned To: wez
New Comment:
Please try using this CVS snapshot:
http://snaps.php.net/php5.2-latest.tar.gz
For Windows:
http://windows.php.net/snapshots/
Previous Comments:
------------------------------------------------------------------------
[2008-11-02 03:43:00] dev at bcdiv dot com
PHP v 5.2.6
MySQL v 5.0.51b-community-nt
Vista Ultimate SP1
Similar (though not exactly) usage of PDO to run a stored procedure w/
a parameter that sets the number in a limit clause does not replicate
error.
$dbc = new DBO;
$dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sproc = $dbc->prepare(CALL spLimitProc ?);
$sproc->bindParam(1,$limit, PDO::PARAM_INT);
$result = $sproc->execute();
Result returns table rows as expected (similar to direct MySQL sproc
call).
Perhaps resolved or not experienced across platforms?
------------------------------------------------------------------------
[2008-08-29 01:46:16] jmalis at malisphoto dot com
This problem is still not fixed in PHP 5.2.6
------------------------------------------------------------------------
[2007-03-23 15:12:20] vrana@php.net
Emulation is wrong if it doesn't work. Numbers after LIMIT should not
be quoted.
------------------------------------------------------------------------
[2007-03-06 17:13:44] phpbugs at filofox dot com
Description:
------------
The following emerged after upgrading from 5.2.0 to 5.2.1 and has been
checked in both versions: the error only occurs in 5.2.1 .
When passing parameters into a LIMIT clause using PDO::execute(), it
appears that PDO is quoting the parameters, which causes MYSQL to throw
an error.
FYI: PDO_MYSQL is built against MySQL client library 5.0.18.
Reproduce code:
---------------
$dbh = new PDO('mysql:localhost;dbname=my_db', 'user', '' );
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try{
$query = $dbh->prepare( 'SELECT * FROM some_table LIMIT :start,
:limit' );
if ( $query->execute ( array ( 'start' => 0, 'limit' => 10 ) ) )
{
while ( $row = $query->fetch ( PDO::FETCH_ASSOC ) )
{
print_r($row);
}
$query->closeCursor();
}
} catch( Exception $e ){
print_r( $e );
}
Expected result:
----------------
A number of rows are returned.
Actual result:
--------------
An exception is thrown:
PDOException Object
(
[message:protected] => SQLSTATE[42000]: Syntax error or access
violation: 1064 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 ''0', '10'' at line 1
[string:private] =>
[code:protected] => 42000
[file:protected] => [my_file].php
[line:protected] => 19
[trace:private] => Array
(
[0] => Array
(
[file] => [my_file].php
[line] => 19
[function] => execute
[class] => PDOStatement
[type] => ->
[args] => Array
(
[0] => Array
(
[start] => 0
[limit] => 10
)
)
)
)
[errorInfo] => Array
(
[0] => 42000
[1] => 1064
[2] => 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 ''0', '10'' at line 1
)
)
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=40740&edit=1