代码之家 › 专栏 › 技术社区 › KianQ

如何从数组生成SQL Update语句

methods arrays sql mysql php

KianQ · 技术社区 · 1 年前

基本上,我有一个数据库类,可以在其中执行基本操作(创建、读取、更新和删除),这就是我为更新方法提出的内容:

public function updateById($table, $id, $updates = []): void
{
        // Generate the SQL statement based on the updates array
        // e.g: updateById('test', 1, [
        //     'column1' => 'newValue1',
        //     'column2' => 'newValue2'
        // ]);

        $st = "UPDATE $table SET ";
        $keys = array_keys($updates);
        $values = array_values($updates);

        for ($i = 0; $i < count($keys); $i++) {
            if ($i != count($keys) - 1) {
                $st .= $keys[$i] . "='" . $values[$i] . "', ";
            } else {
                $st .= $keys[$i] . "='" . $values[$i] . "' ";
            }
        }

        $st .= "WHERE id=$id";

        $query = $this->connection()->prepare($st);
        $query->execute();
}

它确实起到了作用,但它看起来杂乱无章,效率低下。有什么方法可以改进这段代码吗?

1 回复 | 直到 1 年前

GrumpyCrouton 1 年前

此外,您使用了 prepare 但实际上你根本没有利用它。由于将值连接到查询中而不是参数化,因此仍然容易受到SQL注入的攻击。

这段代码应该以更安全的方式完成您想要的任务。我一路上留下了评论,这样你就可以知道发生了什么。

它还允许您传入数组,而不是 $id 。这意味着您可以基于任何列甚至多个列进行更新,而不仅仅是基于ID。

//pass in the table name and an array of ALL columns used, whether in `$update_ids` or `$updates`
private function verify_table_and_columns($table, $columns) {

    $db = $this->connection();

    //make sure table actually exists in your database
    $query = $db->query('SHOW TABLES');
    $all_tables = $query->fetchAll(PDO::FETCH_COLUMN);

    //if table does not exist, return false, thus preventing the query
    if(!in_array($table, $all_tables)) {
        return false;
    }

    //make sure all the columns used in the query are really in the table
    //concatenating the table should be safe here since it's already verified above
    $query = $db->query("SHOW COLUMNS FROM {$table}");
    $all_columns = array_column($query->fetchAll(), 'Field');

    //if any of the columns in the query do not exist in the table, return false, thus preventing the query
    foreach($columns as $column) {
        if(!in_array($column, $all_columns)) {
            return false;
        }
    }

    //if it gets to this point, return true
    return true;
}

public function updateTable($table, $update_ids, $updates = array())
{

    $db = $this->connection();

    if(!$this->verify_table_and_columns($table, array_merge(array_keys($update_ids), array_keys($updates)))) {
        return false;
    }

    //catch all values to be passed to the query at the end, in the correct order.
    $params = [];

    //loop over update fields to generate the SET clause. 
    //Also add the values to $params in the same order as the keys get added to the query.
    $set_clause_parts = [];
    foreach($updates as $key => $value) {
        $set_clause_parts[] = "`{$key}`=?";
        $params[] = $value;
    }
    $set_clause = implode(', ', $set_clause_parts);

    //add the ids to the query.
    //Also add the values to the $params in the same order as the keys get added to the query.
    //NOTE: MUST BE DONE AFTER THE ABOVE LOOP FOR THE ORDER OF THE VALUES TO BE CORRECT
    $where_clause_parts = [];
    foreach($update_ids as $key => $value) {
        $where_clause_parts[] = "`{$key}`=?";
        $params[] = $value;
    }
    $where_clause = implode(' AND ', $where_clause_parts);

    //prepare the query
    $query = $db->prepare("UPDATE {$table} SET {$set_clause} WHERE {$where_clause}");

    //execute the query, pass named parameters to execute.
    return $query->execute($params);

}