What is the best way to use the bindValue function in a LIMIT clause?

Problem

Here’s an example 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

In the LIMIT section of the SQL query, PDO appears to be adding single quotes to my variables. When I checked it up, I discovered this problem that I believe is related: http://bugs.php.net/bug.php?id=44639

Is that the image I’m seeing? Since April 2008, this bug has been open! In the meantime, what are we expected to do?

Before delivering the sql statement, I need to build some pagination and make sure the data is clean and sql injection-safe.

Asked by Nathan H

Solution #1

This is a problem I recall facing in the past. Before sending the value to the bind function, convert it to an integer. This, I believe, resolves the issue.

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

Answered by Stephen Curran

Solution #2

The most straightforward approach is to turn off the emulation mode. It’s as simple as adding the following line.

$PDO->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

When constructing a PDO connection, this mode can also be used as a constructor argument. Because some drivers don’t support the setAttribute() function, this could be a better approach.

It will not only address your binding problem, but it will also allow you to send values directly into execute(), making your code significantly shorter. Assuming the emulation mode has already been established, the entire process will take no more than a half-dozen lines of code.

$skip = isset($_GET['skip']) ? (int)trim($_GET['skip']) : 0;
$sql  = "SELECT * FROM pictures WHERE album = ? ORDER BY id LIMIT ?, ?";
$stmt  = $PDO->prepare($sql);
$stmt->execute([$_GET['albumid'], $skip, $max]);
$pictures = $stmt->fetchAll(PDO::FETCH_ASSOC);

Answered by Your Common Sense

Solution #3

Following is a suggestion based on the bug report:

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

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

However, are you certain that your receiving data is correct? Because there appears to be just one quote after the number in the error message (as opposed to the whole number being enclosed in quotes). It’s also possible that there’s a problem with the data you’re sending. Is it possible to discover out by running print r($ GET);?

Answered by Pekka

Solution #4

This is merely a summary. LIMIT/OFFSET values can be parameterized in four ways:

Answered by 3 revs

Solution #5

LIMIT:init,:end LIMIT:init,:end LIMIT:init,:end LI

You must bind in this manner. It won’t work if you use $req->execute(Array()); since it will cast PDO::PARAM STR to all vars in the array, and the LIMIT requires an Integer. BindValue or BindParam, whatever you prefer.

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

Answered by Nicolas Manzini

Post is based on https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause