我可以在准备好的语句中参数化表名吗? [英] Can I parameterize the table name in a prepared statement?

问题描述

我已经多次使用 mysqli_stmt_bind_param 函数.但是，如果我将试图防止 SQL 注入的变量分开，我就会遇到错误.

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.

这是一些代码示例:

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();
}

是否有可能以某种方式将 .$new_table. 连接替换为另一个问号语句，创建另一个绑定参数语句，或者添加到现有语句中以防止 SQL 注入?

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?

像这样或像这样:

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();
}

推荐答案

对您的问题的简短回答是否".

Short answer to your question is "no".

在最严格的意义上，在数据库级别，准备好的语句只允许为 SQL 语句的值"位绑定参数.

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

一种思考方式是可以在语句的运行时执行时替换而不改变其含义的东西".表名不是这些运行时值之一，因为它决定了 SQL 语句本身的有效性(即，哪些列名有效)并且在执行时更改它可能会改变 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.

在稍高的层次上，即使在模拟准备语句参数替换而不是实际将准备好的语句发送到数据库的数据库接口中，例如 PDO，可以想象它可以允许您在任何地方使用占位符(因为占位符之前已被替换被发送到那些系统中的数据库)，表占位符的值将是一个字符串，并被包含在发送到数据库的 SQL 中，所以 SELECT * FROM ? 和 mytable 因为参数实际上最终会将 SELECT * FROM 'mytable' 发送到数据库，这是无效的 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.

最好的办法就是继续

SELECT * FROM {$mytable}

但是你绝对应该有一个表的白名单，如果$mytable来自用户输入，你首先要检查这些表.

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

这篇关于我可以在准备好的语句中参数化表名吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！