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

问题描述

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

下面是一些代码示例:

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注入?

与此类似或某种形式:

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

解决方案

您的问题的简短答案是否".

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

一种思考方式是可以在运行时执行语句时替换其内容而不会改变其含义".表名不是这些运行时值之一，因为它确定SQL语句本身的有效性(即，哪些列名有效)，并且在执行时对其进行更改可能会更改SQL语句是否有效. /p>

在更高的级别上，即使在模拟预准备语句参数替换而不是将预准备语句实际发送到数据库的数据库接口(例如PDO)中，也可以想象到，允许您在任何地方使用占位符(因为占位符在被替换之前(在这些系统中被发送到数据库)，表占位符的值将是一个字符串，并被包含在发送到数据库的SQL中，因此SELECT * FROM ?与mytable作为参数将实际上最终发送SELECT * FROM 'mytable'到数据库，这是无效的SQL.

您最好的选择就是继续

SELECT * FROM {$mytable}

但是您绝对应该有一个白名单表，如果$mytable来自用户输入，则首先要对其进行检查.

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.

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?

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

解决方案

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.

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.

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.

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.

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