dongzipu7517
dongzipu7517
2019-04-03 21:58

如何动态准备SQL查询（列名也），避免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.

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" >

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')
    */

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • ds753947 ds753947 2年前

    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.

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

    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);
    点赞 评论 复制链接分享
  • dongzhangji4824 dongzhangji4824 2年前

    I noticed you included the mysqli tag on your question, so assuming your database is MySQL and you are using the native MySQL functions, then you can do something like this:

    $stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

/* execute prepared statement */
mysqli_stmt_execute($stmt);

    And yes, I ripped that straight out of the PHP manual page on mysqli_stmt_bind_param.

    点赞 评论 复制链接分享
提交

为你推荐