問題描述

在我看來，我有一個類似這樣的查詢:

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.

這篇關于是否可以將 bind_param 用于 ORDER BY?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！