How to apply bindValue method in LIMIT clause?

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.

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?

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

The simplest solution would be to switch the emulation mode off. You can do it by simply adding the following line

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

Also, this mode can be set as a constructor parameter when creating a PDO connection. It could be a better solution as some report their driver doesn't support the setAttribute() function.

It will not only solve your problem with binding, but also let you send values directly into execute(), which will make your code dramatically shorter. Assuming the emulation mode has been already set, the whole affair will take as much as half a 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);