有什么更好的方法可以让这个插入物更安全，更安全，不会被注射和操纵 [英] What's a better way to make this insert more secure and safe from injection and manipulation

问题描述

我一直在尝试以更安全的方式将函数组合在一起，通过调用不同的列进行更新，从而使我们免受注入或操作插入的影响.在您看来，此功能是否安全，如果不安全，您会建议什么更好的方法，以及为什么.

I've been trying to put together functions in a more secure way that keeps us safe from injection or manipulating inserts by calling different columns to be updated. In your opinion, is this function safe at all, and if not what would you suggest is a better way to do it, and why.

当用户更新他们的个人资料或他们个人资料的特定部分时会调用此函数，正如您所看到的，我已经创建了一个包含他们可以在该表中更新的项目的数组.此外，我得到的 user_id 来自附加到他们会话的安全加密 JSON 令牌，他们没有发送.感谢您抽出宝贵时间.

This function is called when a user updates their profile, or specific parts of their profile, as you can see I've made an array with items which is all they can update in that table. Also, the user_id I am getting is from the secure encrypted JSON token that's attached to their session, they are not sending that. Thanks for your time.

function updateProfile( $vars, $user_id ) {
    $db = new Database();
    $update_string = '';
    $varsCount = count($vars);
    $end = ',';
    $start = 1;
    $safeArray = array( "gradYear", "emailAddress", "token", "iosToken", "country", 
"birthYear", "userDescription" );
    foreach($vars as $key => $value) {
        if(in_array( $key, $safeArray )) {
            if($start == $varsCount) {
                $end = '';
            }
         
            $update_string .= $key . '=' . '"' . $value . '"' . $end;
        }
            $start++;
    }

    if($start > 0) {
        $statement = "update users set " . $update_string . " where userId = '$user_id'";
        $query = $db->updateQuery( $statement );
        if($query) {
            $response  = array( "response" => 200 );
        } else {
            $response  = array( "response" => 500, "title" => "An unknown error occured, 
please try again");
        }

    }

推荐答案

正如上面的评论所表明的，使用查询参数来保护自己免受 SQL 注入是值得的.

As the comments above suggest, it's worth using query parameters to protect yourself from SQL injection.

您要求举例说明如何进行恶意操作.事实上，它甚至不需要是恶意的.任何合法包含撇号的无害字符串都可能破坏您的 SQL 查询.恶意 SQL 注入利用了这个弱点.

You asked for an example of how anything malicious could be done. In fact, it doesn't even need to be malicious. Any innocent string that legitimately contains an apostrophe could break your SQL query. Malicious SQL injection takes advantage of that weakness.

通过在解析查询之前将动态值与 SQL 查询分开来修复该弱点.我们在 SQL 字符串中使用查询参数占位符，然后使用 prepare() 对其进行解析，然后在您 execute() 准备好的查询时组合这些值.这样就安全了.

The weakness is fixed by keeping dynamic values separate from your SQL query until after the query is parsed. We use query parameter placeholders in the SQL string, then use prepare() to parse it, and after that combine the values when you execute() the prepared query. That way it remains safe.

这是我如何编写您的函数.我假设使用支持命名查询参数的 PDO.我建议使用 PDO 而不是 Mysqli.

Here's how I would write your function. I'm assuming using PDO which supports named query parameters. I recommend using PDO instead of Mysqli.

function updateProfile( $vars, $userId ) {
    $db = new Database();
    $safeArray = [
        "gradYear",
        "emailAddress",
        "token",
        "iosToken",
        "country",
        "birthYear",
        "userDescription",
    ];
    // Filter $vars to include only keys that exist in $safeArray.
    $data = array_intersect_keys($vars, array_flip($safeArray));

    // This might result in an empty array if none of the $vars keys were valid.
    if (count($data) == 0) {
        trigger_error("Error: no valid columns named in: ".print_r($vars, true));
        $response = ["response" => 400, "title" => "no valid fields found"];
        return $response;
    }
    
    // Build list of update assignments for SET clause using query parameters.
    // Remember to use back-ticks around column names, in case one conflicts with an SQL reserved keyword.
    $updateAssignments = array_map(function($column) { return "`$column` = :$column"; }, array_keys($data));
    $updateString = implode(",", $updateAssignments);

    // Add parameter for WHERE clause to $data. 
    // This must be added after $data is used to build the update assignments.
    $data["userIdWhere"] = $userId;
    
    $sqlStatement = "update users set $updateString where userId = :userIdWhere";

    $stmt = $db->prepare($sqlStatement);
    if ($stmt === false) {
        $err = $db->errorInfo();
        trigger_error("Error: {$err[2]} preparing SQL query: $sqlStatement");
        $response = ["response" => 500, "title" => "database error, please report it to the site administrator"];
        return $response;
    }
    
    $ok = $stmt->execute($data);
    if ($ok === false) {
        $err = $stmt->errorInfo();
        trigger_error("Error: {$err[2]} executing SQL query: $sqlStatement");
        $response = ["response" => 500, "title" => "database error, please report it to the site administrator"];
        return $response;
    }

    $response = ["response" => 200, "title" => "update successful"];
    return $response;
}

这篇关于有什么更好的方法可以让这个插入物更安全，更安全，不会被注射和操纵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！