How to apply bindValue method in LIMIT clause?

Question

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.

Answer 1

The simplest solution would be to switch the emulation mode off. You can do it either as a connection option or simply by adding the following line

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

It will not only solve your problem with bind param, but also let you to send values in execute(), which will make your code dramatically shoter

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

Answer 2

This just as summary.
There are four options to parameterize LIMIT/OFFSET values:

1. Disable PDO::ATTR_EMULATE_PREPARES as mentioned above.

   Which prevents values passed per ->execute([...]) to always show up as strings.

2. Switch to manual ->bindValue(..., ..., PDO::PARAM_INT) parameter population.

   Which however is less convenient than an ->execute list[].

3. Simply make an exception here and just interpolate plain integers when preparing the SQL query.

    $limit = intval($limit);
    $s = $pdo->prepare("SELECT * FROM tbl LIMIT {$limit}");
   

   The casting is important. More commonly you see ->prepare(sprintf("SELECT ... LIMIT %d", $num)) used for such purposes.

4. If you're not using MySQL, but for example SQLite, or Postgres; you can also cast bound parameters directly in SQL.

    SELECT * FROM tbl LIMIT (1 * :limit)
   

   Again, MySQL/MariaDB don't support expressions in the LIMIT clause. Not yet.

Answer 3

//BEFORE (Present error) $query = " .... LIMIT :p1, 30;"; ... $stmt->bindParam(':p1', $limiteInferior);

//AFTER (Error corrected) $query = " .... LIMIT :p1, 30;"; ... $limiteInferior = (int)$limiteInferior; $stmt->bindParam(':p1', $limiteInferior, PDO::PARAM_INT);

Answer 4

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 ) {
    ...
}