php 如何使用准备好的 PDO 语句设置 ORDER BY 参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2542410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I set ORDER BY params using prepared PDO statement?
提问by Marlorn
I'm having problems using params in the ORDER BYsection of my SQL. It doesn't issue any warnings, but prints out nothing.
我在ORDER BYSQL 部分使用参数时遇到问题。它不会发出任何警告，但不会打印任何内容。
$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();
The :my_paramworks, but not :orderor :direction. Is it not being internally escaped correctly? Am I stuck inserting it directly in the SQL? Like so:
的:my_param工作，但没有:order或:direction。它不是在内部正确转义吗？我是不是直接在 SQL 中插入它？像这样：
$order = 'columnName';
$direction = 'ASC';
$stmt = $db->prepare("SELECT * from table WHERE column = :my_param ORDER BY $order $direction");
Is there a PDO::PARAM_COLUMN_NAMEconstant or some equivalent?
有PDO::PARAM_COLUMN_NAME常数或等价物吗？
Thanks!
谢谢！
回答by Your Common Sense
Yes, you're stuck inserting it directly in the SQL. With some precautions, of course. Every operator/identifier must be hardcodedin your script, like this:
是的，您一直在将它直接插入 SQL 中。当然，有一些预防措施。每个操作符/标识符都必须在脚本中进行硬编码，如下所示：
$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";
Same for the direction.
方向也一样。
I wrote a whitelisting helper functionto 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.
这里的想法是检查值并在不正确的情况下引发错误。
回答by Pascal MARTIN
I don't think you can :
我认为你不能：
- Use placeholders in an
order byclause - Bind column names : you can only bind values -- or variables, and have their value injected in the prepared statement.
- 在
order by子句中使用占位符 - 绑定列名：您只能绑定值——或变量，并将它们的值注入到准备好的语句中。
回答by rray
It's possibleuse prepared statements in ORDER BYclause, unfortunately you need pass the order of column insted of the name and is required set PDO_PARAM_INTwith type.
可以在ORDER BY子句中使用准备好的语句，不幸的是您需要传递名称的列顺序，并且需要PDO_PARAM_INT使用类型设置。
In MySQL you can get the order of columns with this query:
在 MySQL 中，您可以使用此查询获取列的顺序：
SELECT column_name, ordinal_position FROM information_schema.columns
WHERE table_name = 'table' and table_schema = 'database'
PHP code:
PHP代码：
$order = 2;
$stmt = $db->prepare("SELECT field from table WHERE column = :param ORDER BY :order DESC");
$stmt->bindParam(':param', $is_live, PDO::PARAM_STR);
$stmt->bindParam(':order', $order, PDO::PARAM_INT);
$stmt->execute();
回答by goat
I don't think you can get ASC/DESC as part of the prepared statement, but the column you can if you list them all in the sql query like so:
我不认为您可以将 ASC/DESC 作为准备好的语句的一部分，但是如果您将它们全部列在 sql 查询中，则可以使用该列，如下所示：
// Validate between 2 possible values:
$sortDir = isset($_GET['sortDir']) && $_GET['sortDir'] === 'ASC' ? 'ASC' : 'DESC';
$sql = "
...
order
by
case :orderByCol
when 'email' then email
when 'age' then age
else surname
end
$sortDir
";
$stmt = $db->prepare($sql);
$stmt->bindParam(':orderByCol', $someColumn);
$stmt->execute();
Since ASC/DESC is only two possible values, you can easily validate and select between them as hardcoded values using php code.
由于 ASC/DESC 只有两个可能的值，因此您可以使用 php 代码轻松验证和选择它们作为硬编码值。
You could also make use of the ELT(FIELD(,,,,,),,,,,) functions for this, but then ordering will always be done as a string, even if the column is a numeric data type that should be sorted using numeric semantics / collation.
您也可以为此使用 ELT(FIELD(,,,,,),,,,,) 函数，但是排序将始终作为字符串完成，即使该列是数字数据类型使用数字语义/排序规则排序。
回答by cavila
Unfortunely I guess you could not make it with prepared statements. It would make it no cacheable since different columns may have values that could be sorted with special sorting strategies.
不幸的是，我猜你不能用准备好的语句来做到这一点。这将使它无法缓存，因为不同的列可能具有可以使用特殊排序策略进行排序的值。
Create query by using standard escapes and execute it directly.
使用标准转义创建查询并直接执行。
回答by bithom
It is possible. You can use number instead of field name in the 'order by' clause. This is a number starting from 1 and is in the order of field names in the query. And you can concatenate a string in for ASC or DESC. For example "Select col1,col2,col3 from tab1 order by ? " + strDesc + " limit 10,5". strDesc=" ASC" / " DESC".
这是可能的。您可以在“order by”子句中使用数字代替字段名称。这是一个从 1 开始的数字，按查询中字段名称的顺序排列。您可以为 ASC 或 DESC 连接一个字符串。例如“Select col1,col2,col3 from tab1 order by ?” + strDesc + “ limit 10,5”。strDesc=" ASC" / " DESC"。
回答by Rusty
Create an if-else condition.
If(ascCondion ) then bind the values but hard code ORDER BY columnName ASC
Else
Bind the values but hard code ORDER BY COlumnName DESC
创建一个 if-else 条件。
If(ascCondion) 然后绑定值但硬编码 ORDER BY columnName ASC
Else
绑定值但硬编码 ORDER BY COlumnName DESC
回答by Turbotoast
If I'm not entirely mistaken, Pascal is right.
The only binding possible in PDO is the binding of values, as you did with the ':my_param' parameter.
However, there's no harm done in:
如果我没有完全弄错，Pascal 是对的。
PDO 中唯一可能的绑定是值的绑定，就像您对 ':my_param' 参数所做的那样。
但是，在以下方面没有任何害处：
$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->execute();
The only thing to take notice of would be the correct escaping of $orderand $direction, but since you set them manually and didn't set them via user input, I think you're all set.
唯一需要注意的是正确转义$orderand $direction，但是由于您手动设置它们并且没有通过用户输入设置它们，我认为您已经设置好了。
