我可以在准备好的语句中参数化表名吗?

I've used the mysqli_stmt_bind_param function several times. However, if I separate variables that I'm trying to protect against SQL injection I run into errors.

我已经多次使用mysqli_stmt_bind_param函数。但是，如果我分离要保护的变量以避免SQL注入，我就会遇到错误。

Here's some code sample:

这里有一些代码示例:

function insertRow( $db, $mysqli, $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol )
{
    $statement = $mysqli->prepare("INSERT INTO " .$new_table . " VALUES (?,?,?,?,?,?,?);");
    mysqli_stmt_bind_param( $statment, 'sssisss', $Partner, $Merchant, $ips, $score, $category, $overall, $protocol );
    $statement->execute();
}

Is it possible to somehow replace the .$new_table. concatenation with another question mark statement, make another bind parameter statement, or add onto the existing one to protect against SQL injection?

是否可以以某种方式替换。$new_table。与另一个问题标记语句连接，生成另一个绑定参数语句，或者添加到现有的一个以保护SQL注入?

Like this or some form of this:

像这样或这样:

function insertRow( $db, $mysqli, $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol )
{    
    $statement = $mysqli->prepare("INSERT INTO (?) VALUES (?,?,?,?,?,?,?);");
    mysqli_stmt_bind_param( $statment, 'ssssisss', $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol );
    $statement->execute();
}

2 个解决方案

#1

Short answer to your question is "no".

对你的问题的简短回答是“不”。

In the strictest sense, at the database level, prepared statements only allow parameters to be bound for "values" bits of the SQL statement.

在最严格的意义上，在数据库级别，准备语句只允许将参数绑定到SQL语句的“值”位。

One way of thinking of this is "things that can be substituted at runtime execution of the statement without altering its meaning". The table name(s) is not one of those runtime values, as it determines the validity of the SQL statement itself (ie, what column names are valid) and changing it at execution time would potentially alter whether the SQL statement was valid.

其中一种思考方式是“在运行时执行语句时可以替换的东西，而不改变其含义”。表名不是这些运行时值之一，因为它确定了SQL语句本身的有效性(即，哪些列名是有效的)，在执行时更改它可能会改变SQL语句是否有效。

At a slightly higher level, even in database interfaces that emulate prepared statement parameter substitution rather than actually send prepared statements to the database, such as PDO, which could conceivably allow you to use a placeholder anywhere (since the placeholder gets replaced before being sent to the database in those systems), the value of the table placeholder would be a string, and enclosed as such within the SQL sent to the database, so SELECT * FROM ? with mytable as the param would actually end up sending SELECT * FROM 'mytable' to the database, which is invalid SQL.

略高的水平,即使在数据库接口,模拟预备语句参数替换而不是将准备好的语句发送到数据库,如PDO,可能允许您使用一个占位符的地方(因为占位符替换之前发送到数据库的系统),表占位符的值是一个字符串,和封闭在SQL发送到数据库中,所以选择* ?将mytable作为参数，实际上会将SELECT *从'mytable'发送到数据库，这是无效的SQL。

Your best bet is just to continue with

你最好的选择就是继续

SELECT * FROM {$mytable}

but you absolutely should have a white-list of tables that you check against first if that $mytable is coming from user input.

但是，如果mytable的$mytable来自用户输入，那么您必须首先检查表的白列表。

#2

-3

After all i get perfect answer. how to write table name in prepare statement dynamically. all trick happens with '{}'. you must use your variable inside this '{}'. it is working in my code.

毕竟我得到了完美的答案。如何在准备语句中动态地写入表名。所有的技巧都发生在“{}”上。您必须在这个'{}'中使用您的变量。它在我的代码中工作。

$tablename = "run_time_variable";     
$stmt = $conn->prepare("INSERT INTO `{$tablename}` (name, address, phone ) VALUES (?,?,?)");
$stmt->bind_param("sss", $name, $address, $phone );
$stmt->execute();

#1