Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (20.3k points)

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:

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.

1 Answer

0 votes
by (40.7k points)

Try casting the value to an integer before passing it to the bind function like this:

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

Related questions

0 votes
1 answer
asked Jul 30, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
asked Nov 29, 2020 in SQL by Appu (6.1k points)

Browse Categories