我仍在使用pdo:
创建自己的数据库类。class Database {
private $databaseConnection;
public function __construct($path = "", $dbUsername = "", $dbPassword = ""){
$parts = explode('.',$path);
$documentType = array_pop($parts);
if(($path == "") || ((strcmp($documentType, "sq3") !== 0) && (strcmp($documentType, "sqlite") !== 0))) {
throw new OwnException("The Database must bee .sq3 or .sqlite and Path must be stated");
}
$this->databaseConnection = new PDO('sqlite:' . $path, $dbUsername, $dbPassword);
$this->databaseConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->databaseConnection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$this->databaseConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
self::query('CREATE TABLE IF NOT EXISTS User(
id INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR(40) NOT NULL UNIQUE,
numberoflogins INTEGER DEFAULT 0,
bannedstatus BOOLEAN DEFAULT FALSE,
dateofjoining TIME
)');//password field coming soon
//self::query('CREATE TABLE...');
//self::query('CREATE TABLE...');
}
private function query($sql, $params = NULL){
$pdoStatement = $this->databaseConnection->prepare($sql);
$pdoStatement->execute(array_values((array) $params));
return $pdoStatement;
}
public function getObjects($objectTable, $searchedForAttribute, $attributeValue){
$pdoStatement = $this->databaseConnection->prepare("SELECT * FROM $objectTable WHERE $searchedForAttribute = ?");
$pdoStatement->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, $objectTable);
$pdoStatement->execute(array($attributeValue));
$resultObjects = array();
while($resultObject = $pdoStatement->fetch()){
array_push($resultObjects, $resultObject);
}
if(empty($resultObjects)){
return false;
}
return $resultObjects;
}
public function getObject($objectTable, $searchedForAttribute, $attributeValue){
//...returns only the first Object from getObjects()
}
public function insertObject($object){
$objectTable = get_class($object);
$objectData = $object->getAttributes();
return $this->query("INSERT INTO $objectTable("
. join(',', array_keys($objectData)) . ")VALUES("
. str_repeat('?,', count($objectData)-1). '?)', $objectData);
}
public function updateAttribute($objectTable, $setData, $searchedAttribute, $searchedAttributeValue){
...
}
public function updateObject($object){
...
}
public function attributeRemoveObject($objectTable, $searchedForAttribute, $attributeValue){
...
}
public function __destruct(){
unset($this->databaseConnection);
}
}
可以看到,函数仍然没有数据验证(也没有异常处理,工作正在进行中),比如getObjects(),所以变量$objectTable、$searchedForAttribute和$attributeValue直接进入查询。这意味着没有针对SQL注入的保护。
所以我认为如果我在插入查询之前使用静态函数来验证数据会很有帮助:
public static function validate($unsafeData){
//validate $unsafeData
return $safeData
}
因为我希望能够搜索具有类似名称和内容的用户名bin2hex()和hex2bin()是一个糟糕的选择,对于一些属性,如用户名,很容易找到一些验证的起点。例如,我将搜索空白空间,',"和=…
但是我应该如何验证论坛的内容,其中包含很多用于SQL查询的符号?我的意思是,它也可以是一个关于sql本身的帖子。
我看到了很多SQL注入的例子,但他们都忽略了一点,即主要操作也可以在内容框中。
那么一个论坛如何防止SQL注入和错误涉及到一个帖子的内容?
你的弱点在这里:
public function insertObject($object){
$objectTable = get_class($object);
$objectData = $object->getAttributes();
return $this->query("INSERT INTO $objectTable("
. join(',', array_keys($objectData)) . ")VALUES("
. str_repeat('?,', count($objectData)-1). '?)', $objectData);
}
避免SQL注入的最好方法是使用PDO::bindParam。如果字符串字段包含有效的SQL,只要使用准备好的查询和绑定的参数,并不重要:
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pquery = $pdo->prepare(
'INSERT INTO table(column1, column2) VALUES(:column1, :column2)');
// PDO::PARAM_INT, PDO::PARAM_STR, PDO::PARAM_BOOL, etc.
$pquery->bindValue(':column1', $column1, PDO::PARAM_INT); // error if $column1 isn't integer value
$pquery->bindValue(':column2', $column2, PDO::PARAM_STR); // string are sanitized
$pquery->execute();
对于任意对象,您必须使用某种元数据来选择正确的PDO::PARAM_X
值(默认为PDO::PARAM_STR
):
<?php
class User
{
public $username = 'foo''; DROP TABLE User; --';
public $email = 'bar@gmail.com';
public $age = 500;
}
function getColumnType()
{
return PDO::PARAM_STR; // just for demo
}
$object = new User;
$ref = new ReflectionObject($object);
$table = $ref->getShortName(); // to avoid FQN
$properties = $ref->getProperties(ReflectionProperty::IS_PUBLIC);
$params = []; $columns = [];
foreach ($properties as $property) {
$params[] = ':'.($columns[] = $property->getName());
}
// in memory db for demo
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('create table User(id INTEGER PRIMARY_KEY, username VARCHAR(250) NOT NULL,email VARCHAR(250) NOT NULL,age INT)');
// your answer
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pquery = $pdo->prepare("INSERT INTO $table(".implode(',', $columns).") VALUES(".implode(',', $params).")");
foreach ($properties as $property) {
$paramName = ':'.$property->getName();
$paramValue = $property->getValue($object);
$paramType = getColumnType($object, $property); // return PDO::PARAM_X
$pquery->bindValue($paramName, $paramValue, $paramType);
}
$pquery->execute();
// check
$all = $pdo->prepare('select * from User');
$all->execute();
var_dump($all->fetchAll(PDO::FETCH_CLASS, 'User'));
输出:array(1) {
[0] =>
class User#10 (4) {
public $id =>
NULL
public $username =>
string(25) "foo'; DROP TABLE User; --"
public $email =>
string(13) "bar@gmail.com"
public $age =>
string(3) "500"
}
}
您必须实现getColumnType
以获得正确的列数据类型,例如解析带注释的注释。但此时您最好使用一些ORM,如Eloquent或Doctrine。