分享一个PHP PDO 的工具类，采用预编译有效防止SQL注入

分享一个PHP PDO 的工具类，采用预编译有效防止SQL注入

先上源码

<?php
class DB
{
    # @object, The PDO object
    private $pdo;

    # @object, PDO statement object
    private $sQuery;

    # @array,  The database settings
    private $settings;

    # @bool ,  Connected to the database
    private $bConnected = false;

    # @array, The parameters of the SQL query
    private $parameters;

    /**
     *   Default Constructor 
     *
     *  1. Instantiate Log class.
     *  2. Connect to database.
     *  3. Creates the parameter array.
     */
    public function __construct()
    {
        $this->Connect();
        $this->parameters = array();
    }

    /**
     *  This method makes connection to the database.
     *  
     *  1. Reads the database settings from a ini file. 
     *  2. Puts  the ini content into the settings array.
     *  3. Tries to connect to the database.
     *  4. If connection failed, exception is displayed and a log file gets created.
     */
    private function Connect()
    {
        $this->settings = parse_ini_file("settings.ini.php");
        $dsn            = 'mysql:dbname=' . $this->settings["dbname"] . ';host=' . $this->settings["host"] . '';
        try {
            # Read settings from INI file, set UTF8
            $this->pdo = new PDO($dsn, $this->settings["user"], $this->settings["password"], array(
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
            ));

            # We can now log any exceptions on Fatal error. 
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            # Disable emulation of prepared statements, use REAL prepared statements instead.
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

            # Connection succeeded, set the boolean to true.
            $this->bConnected = true;
        }
        catch (PDOException $e) {
            # Write into log
            echo $this->ExceptionLog($e->getMessage());
            die();
        }
    }
    /*
     *   You can use this little method if you want to close the PDO connection
     *
     */
    public function CloseConnection()
    {
        # Set the PDO object to null to close the connection
        # http://www.php.net/manual/en/pdo.connections.php
        $this->pdo = null;
    }

    /**
     *  Every method which needs to execute a SQL query uses this method.
     *  
     *  1. If not connected, connect to the database.
     *  2. Prepare Query.
     *  3. Parameterize Query.
     *  4. Execute Query.   
     *  5. On exception : Write Exception into the log + SQL query.
     *  6. Reset the Parameters.
     */
    private function Init($query, $parameters = "")
    {
        # Connect to database
        if (!$this->bConnected) {
            $this->Connect();
        }
        try {
            # Prepare query
            $this->sQuery = $this->pdo->prepare($query);

            # Add parameters to the parameter array 
            $this->bindMore($parameters);

            # Bind parameters
            if (!empty($this->parameters)) {
                foreach ($this->parameters as $param => $value) {
                    if(is_int($value[1])) {
                        $type = PDO::PARAM_INT;
                    } else if(is_bool($value[1])) {
                        $type = PDO::PARAM_BOOL;
                    } else if(is_null($value[1])) {
                        $type = PDO::PARAM_NULL;
                    } else {
                        $type = PDO::PARAM_STR;
                    }
                    // Add type when binding the values to the column
                    $this->sQuery->bindValue($value[0], $value[1], $type);
                }
            }

            # Execute SQL 
            $this->sQuery->execute();
        }
        catch (PDOException $e) {
            # Write into log and display Exception
            echo $this->ExceptionLog($e->getMessage(), $query);
            die();
        }

        # Reset the parameters
        $this->parameters = array();
    }

    /**
     *  @void 
     *
     *  Add the parameter to the parameter array
     *  @param string $para  
     *  @param string $value 
     */
    public function bind($para, $value)
    {
        $this->parameters[sizeof($this->parameters)] = [":" . $para , $value];
    }
    /**
     *  @void
     *  
     *  Add more parameters to the parameter array
     *  @param array $parray
     */
    public function bindMore($parray)
    {
        if (empty($this->parameters) && is_array($parray)) {
            $columns = array_keys($parray);
            foreach ($columns as $i => &$column) {
                $this->bind($column, $parray[$column]);
            }
        }
    }
    /**
     *  If the SQL query  contains a SELECT or SHOW statement it returns an array containing all of the result set row
     *  If the SQL statement is a DELETE, INSERT, or UPDATE statement it returns the number of affected rows
     *
     *      @param  string $query
     *  @param  array  $params
     *  @param  int    $fetchmode
     *  @return mixed
     */
    public function query($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
    {
        $query = trim(str_replace("\r", " ", $query));

        $this->Init($query, $params);

        $rawStatement = explode(" ", preg_replace("/\s+|\t+|\n+/", " ", $query));

        # Which SQL statement is used 
        $statement = strtolower($rawStatement[0]);

        if ($statement === 'select' || $statement === 'show') {
            return $this->sQuery->fetchAll($fetchmode);
        } elseif ($statement === 'insert' || $statement === 'update' || $statement === 'delete') {
            return $this->sQuery->rowCount();
        } else {
            return NULL;
        }
    }

    /**
     *  Returns the last inserted id.
     *  @return string
     */
    public function lastInsertId()
    {
        return $this->pdo->lastInsertId();
    }

    /**
     * Starts the transaction
     * @return boolean, true on success or false on failure
     */
    public function beginTransaction()
    {
        return $this->pdo->beginTransaction();
    }

    /**
     *  Execute Transaction
     *  @return boolean, true on success or false on failure
     */
    public function executeTransaction()
    {
        return $this->pdo->commit();
    }

    /**
     *  Rollback of Transaction
     *  @return boolean, true on success or false on failure
     */
    public function rollBack()
    {
        return $this->pdo->rollBack();
    }

    /**
     *  Returns an array which represents a column from the result set 
     *
     *  @param  string $query
     *  @param  array  $params
     *  @return array
     */
    public function column($query, $params = null)
    {
        $this->Init($query, $params);
        $Columns = $this->sQuery->fetchAll(PDO::FETCH_NUM);

        $column = null;

        foreach ($Columns as $cells) {
            $column[] = $cells[0];
        }

        return $column;

    }
    /**
     *  Returns an array which represents a row from the result set 
     *
     *  @param  string $query
     *  @param  array  $params
     *      @param  int    $fetchmode
     *  @return array
     */
    public function row($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
    {
        $this->Init($query, $params);
        $result = $this->sQuery->fetch($fetchmode);
        $this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued,
        return $result;
    }
    /**
     *  Returns the value of one single field/column
     *
     *  @param  string $query
     *  @param  array  $params
     *  @return string
     */
    public function single($query, $params = null)
    {
        $this->Init($query, $params);
        $result = $this->sQuery->fetchColumn();
        $this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued
        return $result;
    }
    /** 
     * Writes the log and returns the exception
     *
     * @param  string $message
     * @param  string $sql
     * @return string
     */
    private function ExceptionLog($message, $sql = "")
    {
        $exception = 'Unhandled Exception. <br />';


        if (!empty($sql)) {
            # Add the Raw SQL to the Log
            $message .= "\r\nRaw SQL : " . $sql;
        }
        $exception .= $message;
        $exception .= "<br /> You can find the error back in the log.";
        return $exception;
    }
}
?>

使用方法

1. 同目录下编写settings.ini.php

[SQL]
host = 127.0.0.1
user = root
password = 
dbname = yourdatabase

2. 引入文件

<?php
require("Db.class.php");

3. 创建对象

<?php
// The instance
$db = new Db();

示例

数据表

id	firstname	lastname	sex	age
1	John	Doe	M	19
2	Bob	Black	M	41
3	Zoe	Chan	F	20
4	Kona	Khan	M	14
5	Kader	Khan	M	56

查询所有

<?php
// Fetch whole table
$persons = $db->query("SELECT * FROM persons");

条件查询 (免疫SQL注入):

有3中不同的方式查询

<?php
// 1. 方式一  
$db->bind("id","1");
$db->bind("firstname","John");
$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id");

// 2. 方式二 
$db->bindMore(array("firstname"=>"John","id"=>"1"));
$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id"));

// 3. 方式三
$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id",array("firstname"=>"John","id"=>"1"));

查询行:

通常返回单行

<?php
$ages     =  $db->row("SELECT * FROM Persons WHERE  id = :id", array("id"=>"1"));

Result

id	firstname	lastname	sex	age
1	John	Doe	M	19

查询单个值:

<?php
// Fetch one single value
$db->bind("id","3");
$firstname = $db->single("SELECT firstname FROM Persons WHERE id = :id");

Result

firstname
Zoe

模糊查询

<?php
// 注意必须以通配符结尾
$like = $db->query("SELECT * FROM Persons WHERE Firstname LIKE :firstname ", array("firstname"=>"sekit%"));

Result

id	firstname	lastname	sex	age
4	Sekito	Khan	M	19

查询列:

<?php
// Fetch a column
$names    =  $db->column("SELECT Firstname FROM Persons");

Result

firstname
John
Bob
Zoe
Kona
Kader

删除/插入/更新

<?php

// Delete
$delete   =  $db->query("DELETE FROM Persons WHERE Id = :id", array("id"=>"1"));

// Update
$update   =  $db->query("UPDATE Persons SET firstname = :f WHERE Id = :id", array("f"=>"Jan","id"=>"32"));

// Insert
$insert   =  $db->query("INSERT INTO Persons(Firstname,Age) VALUES(:f,:age)", array("f"=>"Vivek","age"=>"20"));

// Do something with the data 
if($insert > 0 ) {
  return 'Succesfully created a new person !';
}

Method parameters

每个查询方法都有2个可选参数，第一个是绑定的数据，第2个参数PDO fetch_style决定 PDO 如何返回行

<?php
  // 第3个参数 Fetch style
  $person_num =     $db->row("SELECT * FROM Persons WHERE id = :id", array("id"=>"1"), PDO::FETCH_NUM);

  print_r($person_num);
  // Array ( [0] => 1 [1] => Johny [2] => Doe [3] => M [4] => 19 )

更多关于PDO fetch_style说明（官方翻译貌似有移除把PDO写成POD了(￣▽￣)”） : http://php.net/manual/zh/pdostatement.fetch.php