Mysqli Prepare 语句 + 绑定顺序 BY

2021-12-25 00:00:00 php mysqli prepared-statement

我在使用 mysqli_stmt 准备函数时遇到了一个小问题.这是我的查询:

I am having a small issue with the mysqli_stmt prepare function. Here is my query:

$params = array(
    "sisi",
    "some_string",
    5000,
    "date_added DESC"
);

$sql = "SELECT *
        FROM scenes
        WHERE scene_title LIKE ?
        AND scene_id > ?
        ORDER BY ?
        LIMIT ?";

现在，当我像这样将参数绑定到数组时(我实例化了一个有效的 mysqli_stmt 对象):

Now when i bind the params to the array like this (i have a valid mysqli_stmt object instantiated):

call_user_func_array(array($this->mysql_stmt, 'bind_param'), $params);

order by 没有绑定.我在 php.net (http://ca3.php.net/manual/en/mysqli.prepare.php)

The order by is not binded. I read on php.net (http://ca3.php.net/manual/en/mysqli.prepare.php)

标记仅在某些情况下是合法的放在 SQL 语句中.例如，它们在 VALUES() 列表中是允许的INSERT 语句的(指定行的列值)，或在与 WHERE 中的列进行比较子句来指定一个比较值.

The markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value.

但是，他们不允许标识符(例如表或列名称)，在名称的选择列表中SELECT 返回的列语句，或指定两个操作数二元运算符，例如 =等号.

However, they are not allowed for identifiers (such as table or column names), in the select list that names the columns to be returned by a SELECT statement, or to specify both operands of a binary operator such as the = equal sign.

有没有办法解决这个问题，还是我必须将 mysql_real_escape_char() 用于 ORDER BY 子句?

Is there a way around this or am i going to have to use mysql_real_escape_char() for the ORDER BY clause?

推荐答案

正如您找到的 php.net 链接所述，您不能将绑定变量用于标识符.你需要一个解决方法.mysql_real_escape_char 肯定是一种方式.

As the php.net link you found states, you cannot use bind variables for identifiers. You'll need a workaround. mysql_real_escape_char would certainly be one way.

相关文章