Error while using PDO prepared statements and LIMIT in query

Question

I'm using PDO in my application. But I have a problem while I'm working with prepared statements in a query that contains LIMIT. What's the problem?
Codes:

$start = 0;
$rows = 20;
$sql = "SELECT * FROM tbl_news ORDER BY date DESC LIMIT ?, ?";
$q = $db->prepare($sql);
$q->execute(array($start , $rows));

Error:

check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '20''

Answer 1

You can do like this:

$sql = SELECT * FROM tbl_news ORDER BY date DESC LIMIT :start, :rows";
$q = $db->prepare($sql);
$q->bindParam(':start', $start, PDO::PARAM_INT);
$q->bindParam(':rows',$rows, PDO::PARAM_INT);

Answer 2

Regarding to post LIMIT keyword on MySQL with prepared statement , the code below could solve my problem.

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

Thanks Álvaro G. Vicario and Maerlyn

Answer 3

It is a known bug which was fixed in 5.5.6 from memory.

From the article: LIMIT doesn't allow variables in any context. Its arguments must be integer constants.

Further Edit: (There is contention on the matter) User variables are accepted arguments of LIMIT clause in prepared statements, and SQL syntax for prepared statements can be used in stored procedures.

Third Edit: This link explains that these should work with prepared statements.

Answer 4

I just stumbled upon the same problem. For me, using my own statement class (extending PDOStatement) with my own execute() method fixed it.

This is the class:

class MyPDOStatement extends PDOStatement {

  public function execute($input_parameters = null) {
    if (is_array($input_parameters)) {
      $i = 1;
      foreach ($input_parameters as $p) {
        // default to string datatype
        $parameterType = PDO::PARAM_STR;
        // now let's see if there is something more appropriate
        if (is_bool($p)) {
          $parameterType = PDO::PARAM_BOOL;
        } elseif (is_null($p)) {
          $parameterType = PDO::PARAM_NULL;
        } elseif (is_int($p)) {
          $parameterType = PDO::PARAM_INT;
        }
        // parameters passed to execute() are input-only parameters, so use
        // bindValue()
        $this->bindValue($i, $p, $parameterType);
        $i++;
      }
    }
    return parent::execute();
  }

}

To tell PDO to use this statement class instead of the default one, do this:

$db = new PDO(...);
$db->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('MyPDOStatement'));

Now the code in the question will work:

$start = 0;
$rows = 20;
$sql = "SELECT * FROM tbl_news ORDER BY date DESC LIMIT ?, ?";
$q = $db->prepare($sql);
$q->execute(array($start , $rows));

The only thing you have to make shure is that the variables bound to the statement have the correct type, integer. If you have a numeric string, e.g. from the $_GET array, you can do something like this:

if (isset($_GET['start']) && is_numeric($_GET['start'])
    && is_int($_GET['start'] + 0) {
  $start = (int) $_GET['start'];
}

I'm not shure if there is an easier way for the last thing, but at least it works fine for me.

Answer 5

date is a reserved word you have to wrap it with back-ticks