php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #40740 PDO::execute() errors when parameters are used in LIMIT clause
Submitted: 2007-03-06 17:13 UTC Modified: 2014-01-26 18:16 UTC
Votes:76
Avg. Score:4.6 ± 0.7
Reproduced:69 of 71 (97.2%)
Same Version:30 (43.5%)
Same OS:11 (15.9%)
From: phpbugs at filofox dot com Assigned: wez (profile)
Status: Duplicate Package: PDO related
PHP Version: 5.2.1 OS: Linux Debian Sarge 3.1
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: phpbugs at filofox dot com
New email:
PHP Version: OS:

 

 [2007-03-06 17:13 UTC] 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
        )

)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-03-23 15:12 UTC] vrana@php.net
Emulation is wrong if it doesn't work. Numbers after LIMIT should not be quoted.
 [2008-08-29 01:46 UTC] jmalis at malisphoto dot com
This problem is still not fixed in PHP 5.2.6
 [2008-11-02 03:43 UTC] 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-11-04 15:25 UTC] felipe@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/


 [2008-11-12 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2008-11-26 10:57 UTC] miha dot vrhovnik at domenca dot si
I re-compiled pdo and pdo_mysql from 5.2.7 RC4 and overwritten libraries from php 5.2.6. Is this enough? If it is, then this issue is not fixed.
 [2009-02-18 08:51 UTC] nduteil at freedev dot org
Hi,
Still not fixed in the 5.2.8 release. If PDOStatement::bindParam or PDOStatement::bindValue is used to specified the parameter type (ie :  PDO::PARAM_INT) everything works as expected. Not forcing the parameter type to integer generates an error in Mysql.
 [2009-06-16 17:49 UTC] tony at tonybibbs dot com
Same issue on 5.3.0RC2 using pretty much the same code in the initial bug report.
 [2009-08-22 05:59 UTC] marcel dot esser at gmail dot com
Same problem on 5.2.6, Ubuntu Jaunty.
 [2009-08-26 16:36 UTC] seb117 at free dot fr
I don't understand why this bug does not solved ? It's very important, and we don't use PDO properly with this bug.
 [2009-09-08 12:56 UTC] weblab dot solutions at gmail dot com
Try PHP 5.2.9 & 5.3.0 - get the same exception.
 [2009-09-08 13:00 UTC] weblab dot solutions at gmail dot com
And I really surprised this issue not fixed for more than 2 year.
 [2009-09-23 21:13 UTC] uw@php.net
Can we close it as a duplicate and if favour of http://bugs.php.net/bug.php?id=44639 ?
 [2010-12-11 07:52 UTC] elim dot css at gmail dot com
I had this error when I was passing in a value from a form which was a string. I 
had to force the parameter to be an int using (int)$value and do this:

bindValue(":limit", $value, PDO::PARAM_INT);
 [2014-01-26 18:16 UTC] mightyuhu@php.net
-Status: No Feedback +Status: Duplicate
 [2014-01-26 18:16 UTC] mightyuhu@php.net
Duplicate #44639
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Nov 21 21:01:28 2024 UTC