是否可以对ORDER BY使用bind_param? [英] Is it possible to use bind_param for 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"的结果集中按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时没有转换sort变量一样.

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.

对于任何有兴趣的人-

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

推荐答案

仅数据可以与占位符绑定.

列/表名称是 schema 的一部分，并且无法绑定. (它产生奇数结果"而不是简单地产生错误的事实是实现的特殊性.)

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.

这篇关于是否可以对ORDER BY使用bind_param?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！