PHP + mySQL : Prepare, bind_param 没有像我预期的那样工作

Question

我会在这里放两个例子，其中 $stmt = $mysqli->prepare() + $stmt->bind_param() 拒绝工作，我可以'我自己不明白为什么。不工作:

if ($stmt = $mySQLi->prepare("DROP DATABASE ?")) {
        $stmt->bind_param('s', $db_name);
        $stmt->execute(); 
        $stmt->store_result();
}

当前的解决方法:

 if ($stmt = $mySQLi->prepare("DROP DATABASE $db_name")) {
        //$stmt->bind_param('s', $db_name);
        $stmt->execute(); 
        $stmt->store_result();
 }

不工作:

if ($stmt = $strSQLi->prepare("SELECT ? FROM Strings.texts WHERE keyName = ? LIMIT 1")) {
        $stmt->bind_param('ss', strtolower($lang), strtolower("help_".$key));
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($result);
}

当前的解决方法:

if ($stmt = $strSQLi->prepare("SELECT {strtolower($lang)} FROM EVEStrings.texts WHERE keyName = ? LIMIT 1")) {
        $stmt->bind_param('s', strtolower("help_".$key));
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($result);
}

知道为什么吗？

Answer 1

这就是 mysqli::prepare() 的工作方式。它完全写在文档中。

http://php.net/manual/en/mysqli.prepare.php

Note:

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.

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. The latter restriction is necessary because it would be impossible to determine the parameter type. It's not allowed to compare marker with NULL by ? IS NULL too. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

这部分主要是:不允许用于标识符(例如表名或列名)

绑定(bind)参数的想法是您将查询发送到数据库引擎，并在 runtine 中绑定(bind)您提供的值。如果未指定表，引擎将无法构建有效的语句，因此无法继续执行查询和绑定(bind)参数。

我一般不建议使用动态表名，不管它是否安全。但是，如果您真的坚持要这样做，请不要让用户来决定。在应用程序级别(即来自数组)而非用户输入确定 {strtolower($lang)}。