如何动态准备SQL查询(列名称也是如此)，避免SQL注入 [英] How to prepare SQL query dynamically (column names too) avoiding SQL injection

问题描述

我最近使用prepare()和bind_param()了解了有关SQL Injection和避免使用PHP的建议. 现在，我想动态地准备SQL查询，同时添加列名和值.

I recently learned about SQL Injection and the PHP recommendation to avoid it, using prepare() and bind_param(). Now, I want to prepare SQL queries dynamically, adding both column names and values.

我希望这样做，因为HTML输入的name字段与MySQL数据库列的名称相同.

I usted to do it like this, having the name field of the HTML input with the same name as the MySQL database column.

    <input type="text" name="firstname" >
    <input type="text" name="lastname" >

然后，使用mysqli动态创建SQL查询.

And the, create the SQL query dynamically using mysqli.

    // Extract values from POST
    $parameters = $_POST;
    // Organize the values in two strings
    foreach ($parameters as $id => $value) {
        $fields = $fields . "`" . $id . "`,";
        $values = $values . "'" . $value . "',"; 

        /*e.g.
            $fields = `firstname`,`lastname`
            $values = 'John','Wick'
        */
    }

    // Write into the database
    $sql = "INSERT INTO `user` ($fields) VALUES ($values)";

    /*e.g.
        INSERT INTO `user` (`firstname`,`lastname`) VALUES ('John','Wick')
    */

我想知道是否有一种方法可以使用prepare()和bind_param()来避免SQL注入，可能是在HTML输入标签中添加了一些data-type="s"，或者是否有更好，更多的方法最佳做法，做到这一点的方法.

I would like to know if there is a way to do this using prepare() and bind_param() to avoid SQL injection, may be adding adding some data-type="s" to the HTML input tag or if there is a better, more best-practices, way to do it.

推荐答案

您只能将绑定参数用于将为常量值的元素(带引号的字符串，带引号的日期时间或数字文字).

You can use bound parameters only for an element that would be a constant value — a quoted string, a quoted datetime, or a numeric literal.

您不能在SQL中的其他任何内容上使用参数占位符，例如列名，表名，值列表，SQL关键字或表达式或其他语法.

You can't use a parameter placeholder for anything else in SQL, like column names, table names, lists of values, SQL keywords or expressions, or other syntax.

如果您需要使列名动态化，唯一的选择是针对已知列的列表对它们进行验证.

If you need to make column names dynamic, the only option is to validate them against a list of known columns.

$columns_in_user_table = [
  'userid'=>null,
  'username'=>'',
  'firstname'=>'',
  'lastname'=>''
];
// Extract values from POST, but only those that match known columns
$parameters = array_intersect_key($_POST, $columns_in_user_table);
// Make sure no columns are missing; assign default values as needed
$parameters = array_merge($columns_in_user_table, $parameters);

如果使用PDO代替mysqli，则可以跳过绑定.只需使用命名参数，然后将您的列值对的关联数组直接传递给execute():

If you use PDO instead of mysqli, you can skip the binding. Just use named parameters, and pass your associative array of column-value pairs directly to execute():

$columns = [];
$placeholders = [];
foreach ($parameters as $col => $value) {
    $columns[] = "`$col`";
    $placeholders[] = ":$col";
}
$column_list = implode($columns, ',');
$placeholder_list = implode($placeholders, ',');

// Write into the database
$sql = "INSERT INTO `user` ($column_list) VALUES ($placeholder_list)";

$stmt = $pdo->prepare($sql);
$stmt->execute($parameters);

这篇关于如何动态准备SQL查询(列名称也是如此)，避免SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！