2

I'm using this query to get range of rows for example 0-5 , But I get an error message.

Here is the code:

if(isset($_GET['page']) && isset($_GET['per'])){        
    $per = $_GET['per'];
    $pageNumber = $_GET['page'];
    $from = ($pageNumber * $per) - $per;
    $results = $pdo->prepare('SELECT * FROM users LIMIT :sfrom , :sto');
    $results->execute(array('sfrom' => $from , 'sto' => $per));
}

I get the following error :

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 ''5' , '5''

I think that's because the $from and $per are inserted as strings or something I tried to convert them to int using (int)$from , (int)$per But still having the same error

Paolo
  • 15,233
  • 27
  • 70
  • 91
jack
  • 151
  • 2
  • 11
  • Can you edit your code to include the version with the casting applied? That should resolve it. – tadman Mar 03 '18 at 21:48
  • Note that LIMIT without ORDER BY is fairly meaningless – Strawberry Mar 03 '18 at 21:50
  • Probably a duplicate of https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause – Jeto Mar 03 '18 at 22:01
  • @tadman , MYSQL version 4.6.5.2 , It's running on Xampp on my device – jack Mar 03 '18 at 22:03
  • @jack That's not what I was asking about. I mean the version of the code with `(int)` in it. – tadman Mar 03 '18 at 22:03
  • @tadman , How can I know the version with the casting applied? – jack Mar 03 '18 at 22:06
  • I've got a simple request here: Can you edit your question to include the casted code? That should work. – tadman Mar 03 '18 at 22:07
  • @Strawberry , I edited it ` $results = $pdo->prepare('SELECT * FROM users ORDER BY id LIMIT :sfrom , :sto');` but still the same error – jack Mar 03 '18 at 22:07
  • Try `$per = (int) $_GET['per']`and the same for `page`. – tadman Mar 03 '18 at 22:11
  • @tadman , I also tried `$results->execute(array('sloc' => $query , 'sfrom' =>(int) $from , 'sto' => (int)$per));` but it's not working – jack Mar 03 '18 at 22:17

2 Answers2

2

The values passed must be integers but casting them is not enought.

Instruct PDO the binded parameters' type is integer by using PDOStatement::bindParam

Then call PDOStatement::execute as last step (without parameters).

$results = $pdo->prepare('SELECT * FROM users LIMIT :sfrom , :sto');
$results->bindParam(':sfrom', (int)$from, PDO::PARAM_INT );
$results->bindParam(':sto',   (int)$to,   PDO::PARAM_INT );
$results->execute();

See http://php.net/manual/en/pdostatement.bindparam.php

Paolo
  • 15,233
  • 27
  • 70
  • 91
0

Setting $per and $from as numeric strings is totally OK. Make sure you are not passing "'5'" as a value. Casting "'5'" to int results in 0. Try

// remove " and ' then cast to int
$yourNumber = (int) str_replace(['"', "'"], '', $yourInput);
hpaknia
  • 2,769
  • 4
  • 34
  • 63