如何使用准备好的 PDO 语句设置 ORDER BY 参数? [英] How do I set ORDER BY params using prepared PDO statement?

问题描述

我在 SQL 的 ORDER BY 部分使用参数时遇到问题.它不会发出任何警告，但不会打印任何内容.

I'm having problems using params in the ORDER BY section of my SQL. It doesn't issue any warnings, but prints out nothing.

$order = 'columnName';
$direction = 'ASC';

$stmt = $db->prepare("SELECT field from table WHERE column = :my_param ORDER BY :order :direction");
$stmt->bindParam(':my_param', $is_live, PDO::PARAM_STR);
$stmt->bindParam(':order', $order, PDO::PARAM_STR);
$stmt->bindParam(':direction', $direction, PDO::PARAM_STR);
$stmt->execute();

:my_param 有效，但 :order 或 :direction 无效.它不是在内部正确转义吗?我是否坚持将它直接插入到 SQL 中?像这样:

The :my_param works, but not :order or :direction. Is it not being internally escaped correctly? Am I stuck inserting it directly in the SQL? Like so:

$order = 'columnName';
$direction = 'ASC';

$stmt = $db->prepare("SELECT * from table WHERE column = :my_param ORDER BY $order $direction");

是否有 PDO::PARAM_COLUMN_NAME 常量或某些等效项?

Is there a PDO::PARAM_COLUMN_NAME constant or some equivalent?

谢谢！

推荐答案

是的，您被困在直接将其插入 SQL 中.当然，有一些预防措施.每个运算符/标识符都必须在脚本中进行硬编码，如下所示:

Yes, you're stuck inserting it directly in the SQL. With some precautions, of course. Every operator/identifier must be hardcoded in your script, like this:

$orders=array("name","price","qty");
$key=array_search($_GET['sort'],$orders);
$order=$orders[$key];
$query="SELECT * from table WHERE is_live = :is_live ORDER BY $order";

方向相同.

我写了一个白名单辅助函数用于这种情况，大大减少了代码量需要写的:

I wrote a whitelisting helper function to be used in such cases, it greatly reduces the amount of code that needs to be written:

$order = white_list($order, ["name","price","qty"], "Invalid field name");
$direction = white_list($direction, ["ASC","DESC"], "Invalid ORDER BY direction");

$sql = "SELECT field from table WHERE column = ? ORDER BY $order $direction";
$stmt = $db->prepare($sql);
$stmt->execute([$is_live]);

这里的想法是检查值并在不正确的情况下引发错误.

The idea here is to check the value and raise an error in case it is not correct.

这篇关于如何使用准备好的 PDO 语句设置 ORDER BY 参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！