我正试图用PHP制作一个基于OOP的论坛,但目前我一直在制作数据库类。特别是,我一直在为Datatable类制作一个"通用"插入类函数(使用PDO btw)。
class DB
{
private $dbconn;
public function __construct(){
}
protected function connect($dbname, $dbhost='127.0.0.1', $dbuser='root', $dbpass=''){
try{
$this->dbconn = new PDO("mysql:host=$dbhost;dbname=$dbname;", $dbuser, $dbpass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"));
}
catch(PDOException $e){
echo 'Connection failed: '.$e->getMessage()."<br />";
}
}
protected function disconnect(){
$this->dbconn = null;
}
public function insert($dbname, ){
$this->connect($dbname);
try{
# prepare
$sql = "INSERT INTO pdodemotable (firstname, lastname, age, reg_date)
VALUES (?, ?, ?, now())";
$stmt = $dbconn->prepare($sql);
# the data we want to insert
$data = array($firstname, $lastname, $age);
# execute width array-parameter
$stmt->execute($data);
echo "New record created successfully";
}
catch(PDOException $e){
echo $sql . "<br>" . $e->getMessage();
}
}
}
insert函数未完成。我不知道如何让insert函数适应任意数量的参数、任意数量的数据库列和任意表。现在函数中的代码取自我的另一个项目,在那里我使用了过程编程。这是第一次在数据库中使用OOP。
我是OOP和PDO的新手。一定有某种方法或函数可以帮助我,但我没有。我现在看到的唯一解决方案是使用ridicoulus量的字符串处理和if语句。。。这不可能是最好的解决方案。。。一定有更简单的方法。。。
首先注意,insert方法不需要$dbname
参数,它应该是一个构造函数参数:
class DB {
private $dbconn;
public function __construct($dbname, $dbhost='127.0.0.1', $dbuser='root', $dbpass='') {
// also don't catch the error here, let it propagate, you will clearly see
// what happend from the original exception message
$this->dbconn = new PDO("mysql:host=$dbhost;dbname=$dbname;", $dbuser, $dbpass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"));
}
...
}
至于insert
方法,首先试着想象一下它将如何使用。例如,它可以是这样的:
$db = new DB('mydb');
$db->insert('mytable', array('firstname'=>'Pete', 'lastname'=>'Smith'));
然后,您可以将表名和数据(键/值)传递到方法中:
public function insert($table, $data) {
// again, no need to try / catch here, let the exceptions
// do their job
// handle errors only in the case you are going to fix them
// and not just to ingnore them and 'echo', this can lead to much worse problems
// see the explanation below regarding the `backtick` method
$table = $this->backtick($table);
$fields = array();
$placeholders = array();
$values = array();
foreach($data as $key=>$value) {
$fields[] = $this->backtick($key);
// you can also process some special values like 'now()' here
$placeholders[] = '?';
}
$fields = implode($fields, ','); // firstname, lastname
$placeholders = implode($placeholders, ','); // ?, ?
$sql = "INSERT INTO $table ($fields) values ($placeholders)";
$stmt = $this->dbconn->prepare($sql);
$stmt->execute(array_values($data));
}
public function update($table, $id, $data) {
$table = $this->backtick($table);
$fields = array();
foreach($data as $key=>$value) {
$fields[] = $this->backtick($key) . " = ?";
}
$fields = implode($fields, ','); // firstname=?, lastname=?
$sql = "UPDATE $table SET $fields where id=?";
$stmt = $this->dbconn->prepare($sql);
$data['id'] = $id;
$stmt->execute(array_values($data));
if ($stmt->execute(array_values($data)) === false) {
print 'Error: ' . json_encode($stmt->errorInfo()). PHP_EOL;
}
while ($row = $stmt->fetchAll()) {
print json_encode($row) . PHP_EOL;
}
}
private function backtick($key) {
return "`".str_replace("`","``",$key)."`";
}
另一种选择是创建一个单独的对象,该对象将表示一个表行(ActiveRecord模式)。使用这种对象的代码可能看起来像这样:
$person = new Person($db);
$person->firstName = 'Pete';
$person->lastName = 'Smith';
$person->save(); // insert or update the table row
关于可能存在SQL注入漏洞的更新
我还添加了update
和backtick
方法来说明可能的SQL注入。如果没有backtick
,update
可能会被这样的东西调用:
$db->updateUnsafe('users', 2, array(
"name=(SELECT'bad guy')WHERE`id`=1#"=>'',
'name'=>'user2', 'password'=>'text'));
这将导致SQL语句如下:
UPDATE users SET name=(SELECT'bad guy')WHERE`id`=1# = ?,name = ?,password = ? where id=?
因此,我们将更改id为1的用户的名称,而不是更新id为2的用户的数据。由于backtick
方法,上面的语句将以Unknown column 'name=(SELECT'bad guy')WHERE
id =2#' in 'field list'
失败。这是我测试的完整代码。
无论如何,这可能不会保护您免受任何可能的SQL注入,因此最好不要将用户输入用于已知参数,如表名和字段名。
与其做$db->insert('mytable', $_POST)
之类的事情,不如做$db->insert('mytable', array('first'=>$_POST['first']))
。
尝试传递具有数组的参数,然后在方法insert内执行foreach。
类似于:
$data['first_name'] = 'your name';
...
$data['twentieth_name'] = 'twentieth name';
foreach( $data as $key => $value )
$final_array[':'.$key] = $value;
$stmt->execute( $final_array );