如何使用预准备的PDO语句通过params设置订单?

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

我的SQL中使用了params，这是有问题的。它不会发出任何警告，但不会打印任何东西。

$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_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:

my_param可以工作，但不能工作:order or: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_NAME constant or some equivalent?

是否有一个PDO::PARAM_COLUMN_NAME常量或等价的?

Thanks!

谢谢!

8 个解决方案

#1

Here comes the question shows that widely loved prepared statements is not the silver bullet, hehe :)

这个问题表明，广受欢迎的事先准备好的声明并不是灵丹妙药，呵呵:

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:

当然，您需要采取一些预防措施，将它直接插入到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 direction.

相同的方向。

Note that bindParam does no escaping, as no escaping needed at all. it does binding.

注意，bindParam不会转义，因为根本不需要转义。它绑定。

#2

I don't think you can :

我认为你不能:

Use placeholders in an order by clause
在order by子句中使用占位符
Bind column names : you can only bind values -- or variables, and have their value injected in the prepared statement.
绑定列名:只能绑定值——或变量，并将它们的值注入准备好的语句中。

#3

It's possible use prepared statements in ORDER BY clause, unfortunately you need pass the order of column insted of the name and is required set PDO_PARAM_INT with 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();

#4

I don't think you can get ASC/DESC as part of the prepared statement, but the column you can.

我不认为你可以把ASC/DESC作为准备好的陈述的一部分，但是你可以把它作为列。

 order 
    by 
       case :order
           when 'colFoo' then colFoo
           when 'colBar' then colBar
           else colDefault
       end
       $direction

Since ASC/DESC is only two possible values, you can easily validate and select between them as hardcoded values.

由于ASC/DESC只有两个可能的值，因此可以很容易地将它们作为硬编码值进行验证和选择。

You could also make use of the ELT(FIELD(,,,,,),,,,,) functions for this, but then ordering will always be done as a string, even if it's a numeric column.

您还可以为此使用ELT(字段(、、、、、)、、、、)函数，但是排序将始终作为字符串执行，即使它是一个数字列。

#5

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.

使用标准转义创建查询并直接执行它。

#6

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”子句中使用number而不是字段名。这是从1开始的数字，按照查询中的字段名的顺序排列。你可以将一个字符串连接到ASC或DESC中。" + strDesc + "极限10,5"strDesc=" ASC" / " DESC"。

#7

-1

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:

如果我没有完全弄错的话，帕斯卡是对的。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 $order and $direction, but since you set them manually and didn't set them via user input, I think you're all set.

唯一需要注意的是正确转义的$order和$direction，但是由于您手动设置它们，并且没有通过用户输入设置它们，我认为您已经设置好了。

#8

-1

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条件。如果(ascCondion)然后用columnName ASC Else绑定值，但是硬代码顺序使用columnName DESC绑定值，但是硬代码顺序

#1