Prepared statement limit not working even with emulate prepares turned off

Question

My query

$stmt = $db->prepare('SELECT * FROM generalevent ORDER BY date DESC LIMIT ?, 25');
$stmt->execute(array( $limit ));

keeps failing with the message

exception 'PDOException' with message '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 '?, 25' at line 1' 
in ../test.php:35 
Stack trace: #0 ../test.php(35): PDO->prepare('SELECT * FROM g...') #1 {main}

I already have

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

so from what I have read, this should work, no?

possible duplicate of [Error while using PDO prepared statements and LIMIT in query](http://stackoverflow.com/questions/11738451/error-while-using-pdo-prepared-statements-and-limit-in-query) — Marc B, Mar 03 '14 at 20:55
@Fred-ii- `$limit = $currPage*25;` Its type is `integer`, I checked with `gettype`. — gta0004, Mar 03 '14 at 21:01
If `$currPage` is a string (which I assume stands for "current page"), then try using double quotes instead `("SELECT * FROM generalevent ORDER BY date DESC LIMIT ?, 25")` — Funk Forty Niner, Mar 03 '14 at 21:04
Yet I tend to think that using `$currPage*25` is invalid, since `LIMIT` only (*usually*) accepts a single integer and not a mathematical equation; *I think*. @gta0004 — Funk Forty Niner, Mar 03 '14 at 21:06
@Fred-ii- If I put $limit right in the query like `'SELECT * FROM generalevent ORDER BY date DESC LIMIT '.$limit.', 25'` it works — gta0004, Mar 03 '14 at 21:16
You're getting the error on the `prepare` and not on the `execute`? Is the code that you posted the exact code that you're running? — Patrick Q, Mar 03 '14 at 21:22
@Fred-ii- I tried binding it like this: `$stmt->bindValue(1, $limit, PDO::PARAM_INT); $stmt->execute(); ` but it still throws the same error message. Even if I add a cast to integer. — gta0004, Mar 03 '14 at 21:28
Just for fun, try putting `date` in backticks (since it is a reserved word). — Patrick Q, Mar 03 '14 at 21:42
@PatrickQ FYI: [`date`](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) is not a reserved word. — Funk Forty Niner, Mar 03 '14 at 21:53
@Fred-ii- Sorry, you are correct. It is a "keyword", not a "reserved word". — Patrick Q, Mar 03 '14 at 21:57
@gta0004 Can you try `$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);` Or `DESC LIMIT %d, 25'` — Funk Forty Niner, Mar 03 '14 at 22:07
@Fred-ii- Nothing changes with bindValue. It works with %d, but isn't that the same as inserting the value directly into the query with `.$limit.` ? — gta0004, Mar 03 '14 at 22:17
I don't know. I only found that in the Q&A (and comments) here http://stackoverflow.com/q/7772648/ @gta0004 I'd have to further test it later on if I have any free time. — Funk Forty Niner, Mar 03 '14 at 22:42
Using %d is not the same as using a bound variable or parameter, but provides some of the same benefits especially with regard to SQL Injection. It forces whatever is inserted into the query to be a numeric value and not some unintended statement. However, it will still defeat the query cache as a different query will be submitted depending on the supplied parameter, where a properly parametrized query will not. — AaronM, Mar 03 '14 at 23:12
You can also try adding `$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` which (to a certain degree) worked for me while using `LIMIT ?,25` and `LIMIT :limit,25` then binding that afterwards. @gta0004 - However, I am very limited to testing, since you haven't provided full code. — Funk Forty Niner, Mar 03 '14 at 23:23

score 2 · Answer 1 · answered Mar 03 '14 at 23:42

The following worked for me, however it's hard to give a definite answer, since full code was not provided.

Here is what I tested with, on my own server from an existing table.

Sidenote: Using :limit or ? after LIMIT both worked and did not throw any errors.

<?php
$mysql_username = 'xxx'; // for DB
$mysql_password = 'xxx'; // for DB

try {

$pdo= new PDO('mysql:host=localhost;dbname=database_name', $mysql_username, $mysql_password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

} catch (PDOException $e) {
     exit( $e->getMessage() );
}

try {

$currPage = 2;

// $limit = 1;

$limit = $currPage * 25;

$sql = "SELECT * FROM animals LIMIT :limit,25";

$stmt = $pdo->prepare($sql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();

     $results = $pdo->query($sql);

//     print_r($results);
} catch (PDOException $e) {
     echo $e->getMessage();
}

    $cols = $results->columnCount(); // Number of returned columns

    echo 'Number of returned columns: '. $cols. '<br />';

echo '

<div align="center">
  <center>
  <table border="1" cellspacing="0" cellpadding="3">
    <tr>

      <td width="10%" bgcolor="#99CCFF"><p align="center">ID</td>
      <td width="33%" bgcolor="#99CCFF"><p align="center">Breed</td>
      <td width="34%" bgcolor="#99CCFF"><p align="center">Species</td>
    </tr>

';

foreach($results as $row) {

echo "<tr><td width=\"10%\"><p align=\"center\">" . $row['id'] . "</td>\n<td width=\"33%\">" . $row['name'] . "</td><td width=\"33%\">" . $row['species'] . "</td>";

}

echo "\n";
  echo "</tr>";

echo '

  </table>
  </center>
</div>
';

?>

Prepared statement limit not working even with emulate prepares turned off

1 Answers1