PDO类的通用插入方法

本文介绍了PDO类的通用插入方法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我在PDO中的插入方法，它可以100％工作.此插入"方法接受表，列和值，但我想使其具有通用性. (我要插入带有或不带有列名的值)

this is my insertion method in PDO, it is working 100%. this 'insert' method accepts table, column and value but i want to make it versatile. (i want to insert values with or without the column names)

public function insert($table, $pair = array()){ try{ $Sql = "INSERT INTO $table ( "; $Sql .= implode(", ", array_keys($pair)); $Sql .= " )"; $Sql .= " VALUES ("; $Sql .= implode(", ", array_fill("0", count($pair), " ?")); $Sql .= " )"; $array = array_combine(array_keys(array_fill("1", count($pair), ":")), $pair); $ready = $this->conn->prepare($Sql); foreach($array as $key => $value) { $ready->bindValue($key, $value, PDO::PARAM_STR); } $ready->execute(); } catch(Exception $e){ $this->trace .= " • ". $e->getMessage(); } } $new = new community(); echo $new->insert("table", array("Col1" => "value1", "col1" => "value1"));

推荐答案

函数有两个问题.

它容易受到SQL注入的攻击.

它不灵活.按照该模式，您将拥有上千种此类功能，这将使您的代码陷入混乱.然而，相对于实际的SQL，它总是受限子集.

It is vulnerable to SQL injection.

It is not flexible. Following the pattern, you are going to have a thousand functions of this kind, which will make your code into mess. Yet it would be always limited subset against real SQL.

您真正需要的功能是可以创建数组外的SET语句和允许的字段列表. 作为进一步的改进，您可以为此语句设计一个自定义的占位符.

What you really need is a function that can create a SET statement out of array and a list of allowed fields. As a further improvement you may devise a custom placeholder for this statement.

具有这两件事，您可以制定一个单个通用函数来运行所有DML查询，如下所示:

Having these two things you can work out a single general purpose function to run all the DML queries like this:

$db->query("INSERT INTO t SET %u", array("Col1" => "value1", "col1" => "value1"));

这将使您多花3个字(插入，插入和设置)，但将会是

It will cost you 3 additional words (insert, into and set), but it will be

• 可读.每个人都可以理解SQL.在阅读功能时，需要一个文档
• 灵活.它可以支持任何查询和修饰符，不仅支持一个单一形式的插入.

您希望可以使用此单个功能运行的每个查询:

Every query you wish you can run with this single function:

$data = array("Col1" => "value1", "col1" => "value1"); $db->query("INSERT IGNORE INTO t SET %u", $data); $db->query("REPLACE INTO t SET %u", $data); $db->query("DELETE FROM t WHERE id = ?", $id); // and so on

实际上不需要专用功能.

No dedicated functions actually needed.

此外，您还必须始终根据一组硬编码的白名单来验证一组字段，以使用户只能插入他们允许的字段.不要让用户更改特权，消息计数等.

Also, you have to always verify a set of fields against a hardcoded white list, to let a user insert only fields they are allowed to. Do not let a user to alter privileges, messages count and so on.

但是，即使没有自定义占位符，它也不需要SQL映射函数集，而只需要一个用于创建SET的函数和一个通用查询执行函数:

But even without custom placeholder it would require no set of SQL-mapped functions but just a function to create a SET and a general purpose query execution function:

$allowed = array("name","surname","email"); // allowed fields $sql = "INSERT INTO users SET ".pdoSet($fields,$values); $stm = $dbh->query($sql ,$values);