如何在LIMIT子句中应用bindValue方法? [英] How to apply bindValue method in LIMIT clause?

问题描述

这是我的代码的快照:

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

我知道

您的SQL语法有错误； 检查对应的手册 您的MySQL服务器版本 在'15'，15'附近使用的正确语法 第1行

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

似乎PDO在SQL代码的LIMIT部分的变量中添加了单引号.我查了一下，发现这个与我相关的错误: http://bugs.php.net/bug.php?id=44639

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

这就是我在看的吗?自2008年4月以来，此错误已被打开！ 同时我们该怎么办?

Is that what I'm looking at? This bug has been opened since April 2008! What are we supposed to do in the meantime?

在发送sql语句之前，我需要进行一些分页，并确保数据干净，sql注入安全.

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

推荐答案

我记得以前有这个问题.将值强制转换为整数，然后再将其传递给bind函数.我认为这可以解决问题.

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

这篇关于如何在LIMIT子句中应用bindValue方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！