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
 [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

Add a Patch

Pull Requests

Add a Pull Request

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 Mar 28 20:01:28 2024 UTC