php 如何在 LIMIT 子句中应用 bindValue 方法？

How to apply bindValue method in LIMIT clause?

提问by Nathan H

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

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

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

似乎 PDO 在 SQL 代码的 LIMIT 部分为我的变量添加了单引号。我查了一下我发现了这个我认为相关的错误：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?

这就是我在看的吗？这个bug从2008年4月就被打开了！这期间我们该怎么办？

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

我需要建立一些分页，并且需要在发送 sql 语句之前确保数据是干净的、sql 注入安全的。

回答by Stephen Curran

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

回答by Your Common Sense

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.

此外，在创建 PDO 连接时，可以将此模式设置为构造函数参数。这可能是一个更好的解决方案，因为有些人报告他们的驱动程序不支持该setAttribute()功能。

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

它不仅可以解决您的绑定问题，还可以让您将值直接发送到 中execute()，这将使您的代码显着缩短。假设已经设置了仿真模式，整个事情将需要多达六行代码

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

回答by Pekka

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 onequote 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?

但是你确定你输入的数据是正确的吗？因为在错误消息中，数字后面似乎只有一个引号（而不是将整个数字括在引号中）。这也可能是您传入的数据出错。你能做个print_r($_GET);了解吗？

回答by mario

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

这只是作为总结。
有四个选项可以参数化 LIMIT/OFFSET 值：

1. Disable PDO::ATTR_EMULATE_PREPARESas 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.

1. 禁用PDO::ATTR_EMULATE_PREPARES如上所述以上。

   这可以防止传递的值->execute([...])始终显示为字符串。

2. 切换到手动->bindValue(..., ..., PDO::PARAM_INT)参数填充。

   然而，这不如 ->execute list[] 方便。

3. 在这里做一个例外，并在准备 SQL 查询时插入普通整数。

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

   选角很重要。您更常看到->prepare(sprintf("SELECT ... LIMIT %d", $num))用于此类目的。

4. 如果您不使用 MySQL，而是使用 SQLite 或 Postgres；您还可以直接在 SQL 中转换绑定参数。

    SELECT * FROM tbl LIMIT (1 * :limit)
   

   同样，MySQL/MariaDB 不支持 LIMIT 子句中的表达式。还没有。

回答by Nicolas Manzini

for LIMIT :init, :end

为了 LIMIT :init, :end

You need to bind that way. if you had something like $req->execute(Array());it wont work as it will cast PDO::PARAM_STRto all vars in the array and for the LIMITyou absolutely need an Integer. bindValue or BindParam as you want.

你需要这样绑定。如果你有类似的东西$req->execute(Array());它不会工作，因为它会PDO::PARAM_STR转换为数组中的所有变量，并且LIMIT你绝对需要一个整数。bindValue 或 BindParam 根据需要。

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

回答by Melissa Williams

Since nobody has explained why this is happening, I'm adding an answer. The reason it is behaving this was is because you are using trim(). If you look at the PHP manual for trim, the return type is string. You are then trying to pass this as PDO::PARAM_INT. A few ways to get around this are:

由于没有人解释为什么会发生这种情况，我添加了一个答案。它表现出这种行为的原因是因为您正在使用trim(). 如果你查看 PHP 手册trim，返回类型是string. 然后，您尝试将其作为PDO::PARAM_INT. 解决这个问题的几种方法是：

1. Use filter_var($integer, FILTER_VALIDATE_NUMBER_INT)to make sure you are passing an integer.
2. As others said, using intval()
3. Casting with (int)
4. Checking if it is an integer with is_int()

1. 用于filter_var($integer, FILTER_VALIDATE_NUMBER_INT)确保您传递的是整数。
2. 正如其他人所说，使用 intval()
3. 铸造与 (int)
4. 检查它是否是一个整数 is_int()

There are plenty more ways, but this is basically the root cause.

还有很多方法，但这基本上是根本原因。

回答by Karel

bindValue offset and limit using PDO::PARAM_INT and it will work

使用 PDO::PARAM_INT 的 bindValue 偏移和限制，它将起作用

回答by Brayan Josue Medina Melendez

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

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

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

回答by Fins

PDO::ATTR_EMULATE_PREPARESgave me the

PDO::ATTR_EMULATE_PREPARES给了我

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

驱动程序不支持此功能：此驱动程序不支持设置属性的错误。

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

我的解决方法是将$limit变量设置为字符串，然后将其组合在准备语句中，如下例所示：

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

回答by Tycon

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:

不同版本的 PHP 和 PDO 的奇怪之处之间发生了很多变化。我在这里尝试了 3 或 4 种方法，但无法使 LIMIT 工作。
我的建议是使用带有intval()过滤器的字符串格式/连接：

$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.

使用 intval() 来防止 SQL 注入非常重要，尤其是当您从 $_GET 等获取限制时。如果你这样做，这是让 LIMIT 工作的最简单方法。

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'.

有很多关于“PDO 中 LIMIT 的问题”的讨论，但我的想法是 PDO 参数永远不会用于 LIMIT，因为它们始终是快速过滤器工作的整数。尽管如此，它还是有点误导，因为哲学一直是不要自己做任何 SQL 注入过滤，而是“让 PDO 处理它”。