Nathan H Nathan H - 1 year ago 74
MySQL Question

How to apply bindValue method in LIMIT clause?

Here is a snapshot of my code:

$fetchPictures = $PDO->prepare("SELECT *
FROM pictures
WHERE album = :albumId
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.

Answer Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download