How to apply bindValue method in LIMIT clause?

user_25645 0
Viewed 66349

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.

bindvalue pdo mysql sql php
11 Answers

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);
user_1558620

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?

user_1876060

PDO::ATTR_EMULATE_PREPARES gave me the

Driver does not support this function: This driver doesn't support setting attributes' error.

My workaround was to set a $limit variable as a string, then combine it in the prepare statement as in the following example:

$limit = ' LIMIT ' . $from . ', ' . $max_results;
$stmt = $pdo->prepare( 'SELECT * FROM users WHERE company_id = :cid ORDER BY name ASC' . $limit . ';' );
try {
    $stmt->execute( array( ':cid' => $company_id ) );
    ...
}
catch ( Exception $e ) {
    ...
}
user_15012540

There is alot going on between different versions of PHP and the oddities of PDO. I tried 3 or 4 methods here but could not get LIMIT working.
My suggestion is to use string formatting / concatination WITH an intval() filter:

$sql = 'SELECT * FROM `table` LIMIT ' . intval($limitstart) . ' , ' . intval($num).';';

It is very important to use intval() to prevent SQL injection, particularly if you are getting your limit from $_GET or the like. If you do that this is the easiest way to get LIMIT working.

There is alot of talk about 'The problem with LIMIT in PDO' but my thought here is that PDO params were never ment to be used for LIMIT since they will alway be integers a quick filter works. Still, it is a bit misleading since the philosophy has always been to not do any SQL injection filtering yourself but rather 'Have PDO handle it'.

user_10675830

Try the following:

$sql2 = "SELECT  * FROM tab ORDER BY id DESC limit 2
user_193030610
Related