Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
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:  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.

1 Answer

0 votes
by (40.4k 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)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.2k users

Browse Categories

...