0

I've been working on a forum, and now that's it almost done I'm cleaning up my code.

I changed this (which works perfectly):

$replyquery = $engine->runQuery("SELECT * FROM forum_posts WHERE topic_id=:topic_id AND deleted='0' ORDER BY timestamp ASC LIMIT $fromRecordNum, $recordsPerPage");
$replyquery->execute(array(':topic_id'=>$thread['id']));

To this:

$replyquery = $engine->runQuery("SELECT * FROM forum_posts WHERE topic_id=:topic_id AND deleted='0' ORDER BY timestamp ASC LIMIT :recordsNum, :recordsPerPage");
$replyquery->execute(array(':topic_id'=>$thread['id'],':recordsNum'=>$fromRecordNum,':recordsPerPage'=>$recordsPerPage));

It seems the second one does not work and throws me an error:

PHP Fatal error: Uncaught 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 MariaDB server version for the right syntax to use near ''0', '10'' at line 1'

Which is weird, because they should technically be both the same, right? Can anyone tell me what is going wrong and how I should resolve this?

J. Doe
  • 69
  • 8
  • Prepared statements does not support LIMIT (and ORDER BY etc...) – odan Jan 16 '18 at 13:18
  • @DanielO. Would the first query be a security issue, if I left it like that? – J. Doe Jan 16 '18 at 13:29
  • That's not entirely true, @Daniel. You can bind limits and offset. – Qirel Jan 16 '18 at 13:30
  • @Qirel Maybe it "depends" if you have emulated prepared statements enabled or not. Do you have a working example of that? – odan Jan 16 '18 at 13:42
  • @Daniel Check the flagged dupe, https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause :-) You can bind limits/offsets by binding through `bindParam()` instead of through `execute()`, but then all parameters has to be bound through that method. You also have to supply the `PDO::PARAM_INT` parameter. – Qirel Jan 16 '18 at 14:35

0 Answers0