是否可以将 bind_param 用于 ORDER BY?

2021-12-25 00:00:00 php mysqli prepared-statement bindparam sql-order-by

在我看来，我有一个类似这样的查询:

In my mind I have a query that goes something like this:

$sort = isset($sort) ? sanitize($_sort) : 'id';

if ($result = $link->prepare("
    SELECT id, price
    FROM items
    ORDER BY ?
"))
{
    $result->bind_param("s", $sort);
    $result->execute();
    etc...
}

当我在不设置排序变量的情况下运行此代码块时，它运行时不会出现与在 ORDER BY 子句中使用 ? 相关的错误，并且结果集显示在看起来像带有ORDER BY id"的结果集.

When I run this code block without setting the sort variable it runs without an error relating to the use of the ? in the ORDER BY clause and a result set is displayed in what appears to be a result set with "ORDER BY id".

如果我将排序变量设置为price ASC"之类的值，我仍然会得到一个似乎是ORDER BY id"而不是ORDER BY price ASC"的结果集.

现在，如果我改变代码并像这样运行它:

Now, if I alter the code and run it like this:

$sort = isset($sort) ? sanitize($_sort) : 'id';

if ($result = $link->prepare("
    SELECT id, price
    FROM items
    ORDER BY $sort
"))
{
    $result->execute();
    etc...
}

它运行正确，结果集与我在 phpMyAdmin 中的查询相同.

It runs correctly, with a result set that is the same as my query in phpMyAdmin.

这里到底发生了什么以及为什么查询没有按照我最初使用 bind_param 的预期运行.

What exactly is going on here and why the query does not run as I initially intended using bind_param.

在我看来，它应该可以工作，因为不会出现与此类使用相关的错误……但实际上，它似乎不适用于 ORDER BY 子句.这几乎就像在运行 bind_param 时没有转换排序变量一样.

In my mind, it should work because no error comes up relating to such use... But in practice it seems that it does not work for an ORDER BY clause. It's almost like it is not translating the sort variable while running bind_param.

对于任何有兴趣的人 -

For anyone interested -

if (isset($sort))
{
    $acceptableSortValues = array('name', 'price ASC', 'price DESC');
    $sort = sanitize($sort);
    if (!in_array($sort, $acceptableSortValues))
    {
        $sort = 'name';
    }   
}
else
{
    $sort = 'name';
}

if ($result = $link->prepare("
    SELECT name, price
    FROM items
    ORDER BY $sort
"))
{
    $result->execute();
    etc...
}

推荐答案

只有数据可以绑定占位符.

列/表名是架构的一部分，不能绑定.(它产生奇怪的结果"而不是简单地产生错误的事实是实现的一个特殊性.)

Column/table names are part of the schema and cannot be bound. (The fact that it generates "odd results" instead of simply yielding an error is a peculiarity of the implementation.)

我建议使用列名和受控字符串插值的白名单.

I would suggest using a white-list of column-names and controlled string interpolation.

相关文章