php - 如何为自己的 PDO 数据库类验证论坛帖子的内容？ (防止 SQL 注入(inject))

Question

我仍在使用 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 注入(inject)。

所以我认为如果我在插入查询之前使用静态函数来验证数据会很有帮助:

public static function validate($unsafeData){
    //validate $unsafeData
    return $safeData
    }

因为我希望能够搜索具有相似名称和内容的用户名 bin2hex() 和 hex2bin() 是一个糟糕的选择，对于某些属性(例如用户名)，很容易找到一些验证起点。例如，我会搜索空格、'、"和 =...

但是我应该如何验证包含大量用于 SQL 查询的标志的论坛帖子的内容？我的意思是它也可以是一篇关于 sql 本身的帖子。

我看到了很多 SQL 注入(inject)的示例，但所有示例都忽略了主要操作也可以在内容框中这一点。

那么论坛如何防止引用帖子内容的 SQL 注入(inject)和错误？

Answer 1

你的弱点在这里:

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 注入(inject)的最佳方法是使用 PDO::bindParam .只要您使用准备好的查询和绑定(bind)参数，字符串字段是否包含有效 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。