Page 1 of 1

Problem with LIMIT syntax

Posted: Sun Jan 19, 2014 3:30 am
by social_experiment
i can't retrieve information from the table when i use the code below;

Code: Select all

<?php
$sql = "SELECT 
`ip`,
`action_date`
FROM
`admin_logins` 
WHERE 
`ip` = ? LIMIT ?, ?";			
					
$sth2 = $dbh->prepare($sql);		
$sth2->execute(array($ip, 0, 4));  // <- line 74
?>
The SQL below is what works, when used in in phpMyAdmin

Code: Select all

SELECT * 
FROM `admin_logins` 
WHERE `ip`= '41.52.92.107'
LIMIT 0, 5
The error i am receiving via error_log is the following
PDOStatement::execute(): 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', '4'' at line 7 in /nanya/search.php on line 74

If i remove the LIMIT clause from the query it works;

any ideas / suggestions appreciated

Edit
Did the same script using mysqli_ functions and it seems the problem (seems to me anyway) lies with the ip address not being 'escaped' (?)

Re: Problem with LIMIT syntax

Posted: Sun Jan 19, 2014 7:50 am
by Celauran
There was a bug in the MySQL driver -- apparently fixed in 5.5.6 -- that causes it to quote numeric arguments, so your query ends up with LIMIT '0', '5'. You can disable emulated prepares

Code: Select all

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
or sprintf the query to pass in the limit values

Code: Select all

$sql = sprintf("SELECT 
`ip`,
`action_date`
FROM
`admin_logins` 
WHERE 
`ip` = ? LIMIT %d, %d", $foo, $bar);
Of course, if 0 and 4 are static values, you can just include them in the query directly.

Re: Problem with LIMIT syntax

Posted: Sun Jan 19, 2014 9:13 am
by social_experiment
thanks for letting me know; i wasn't aware of this :)