PHP PDO bindValue in LIMIT

Question

Here is a snapshot of my code:

$fetchPictures = $PDO->prepare("SELECT * FROM pictures WHERE album = :albumId ORDER BY id ASC LIMIT :skip, :max");
$fetchPictures->bindValue(':albumId', $_GET['albumid'], PDO::PARAM_INT);
if(isset($_GET['skip'])){
    $fetchPictures->bindValue(':skip', trim($_GET['skip']), PDO::PARAM_INT);    
}
else{
    $fetchPictures->bindValue(':skip', 0, PDO::PARAM_INT);  
}
$fetchPictures->bindValue(':max', $max, PDO::PARAM_INT);
$fetchPictures->execute() or die(print_r($fetchPictures->errorInfo()));
$pictures = $fetchPictures->fetchAll(PDO::FETCH_ASSOC);

I get

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 ''15', 15' at line 1

It seems that PDO is adding single quotes to my variables in the LIMIT part of the SQL code. I looked it up I found this bug which I think is related: http://bugs.php.net/bug.php?id=44639

Is that what I'm looking at? This bug has been opened since April 2008! What are we supposed to do in the meantime?

i need to build some pagination, and need to make sure the data is clean, sql injection-safe, before sending the sql statement.

Answer 1

Looking at the bug report, the following might work:

$fetchPictures->bindValue(':albumId', (int)$_GET['albumid'], PDO::PARAM_INT);

$fetchPictures->bindValue(':skip', (int)trim($_GET['skip']), PDO::PARAM_INT);  

but are you sure your incoming data is correct? Because in the error message, there seems to be only one quote after the number (as opposed to the whole number being enclosed in quotes). This could also be an error with your incoming data. Can you do a print_r($_GET); to find out?

Answer 2

I remember having this problem before. Cast the value to an integer before passing it to the bind function. I think this solves it.

$fetchPictures->bindValue(':skip', (int) trim($_GET['skip']), PDO::PARAM_INT);