6°

php pdo类 防注入,事务,多库自动选择,链式操作

因为某些原因目前业务不用框架开发,自己写了一个pdo类实现防注入,事务等,后续还会继续完善 

<?php
/**
 * Created by PhpStorm.
 * User: ywx
 * Date: 2019/7/10
 * Time: 14:57
 */

class Db { private $conn; private $sql = [ 'where' => null, 'wheresql' => null, 'orderBy' => null, 'limit' => null, 'up' => null, 'ins' => null, ]; private $type;

public function __construct( $data = [])
{
    if($data){
        $this-&gt;_setDbDrive($data);
    }
}

private function _setDbDrive($dbConf){
    $dns = 'mysql:host='.$dbConf['DB_IP'].';dbname='.$dbConf['DB_NAME'].';charset=utf8mb4';

    try {
        $db = new PDO($dns,$dbConf['DB_USER'],$dbConf['DB_PASS']);
    } catch(PDOException $e) {
        die('Could not connect to the database:&lt;br/&gt;' . $e);
    }
    $this-&gt;conn[$dbConf['name']] = $db;
}

public function table($tablename) {
    //获取表库信息
    $db_info = $this-&gt;_getDBIP($tablename);
    $this-&gt;sql = [];//重置缓存
    $this-&gt;type = $db_info['name'];
    if(!isset($this-&gt;conn[$db_info['name']])){
        $this-&gt;_setDbDrive($db_info);
    }

    $this-&gt;tablename = $tablename;
    return $this;
}

public function select($fields = '*') {
    $querySql = sprintf("SELECT %s FROM %s", $fields, $this-&gt;tablename);
    if(!empty($this-&gt;sql['where'])) {
        $querySql .= ' WHERE ' . $this-&gt;sql['wheresql'];
    }
    if(!empty($this-&gt;sql['orderBy'])) {
        $querySql .= ' ORDER BY ' . $this-&gt;sql['orderBy'];
    }
    if(!empty($this-&gt;sql['limit'])) {
        $querySql .= ' LIMIT ' . $this-&gt;sql['limit'];
    }

    return $this-&gt;_query($querySql);
}

public function find($fields = '*') {
    $result = $this-&gt;select($fields);
    return isset($result[0]) ? $result[0] : null;
}

public function insert($data) {
    foreach ($data as $key =&gt; &amp;$value) {
        $value = addslashes($value);
    }
    $keys = "`".implode('`,`', array_keys($data))."`";
    $values = ":i".implode(", :i", array_keys($data));
    $this-&gt;sql['ins'] = $data;

    $querySql = sprintf("INSERT INTO %s ( %s ) VALUES ( %s )", $this-&gt;tablename, $keys, $values);
    return $this-&gt;_query($querySql);
}

public function delete() {
    $querySql = sprintf("DELETE FROM %s WHERE ( %s )", $this-&gt;tablename, $this-&gt;sql['wheresql']);
    return $this-&gt;_query($querySql);
}

/**
 * 传入数组 ['apid'=&gt;['+',15],'bpid'=&gt;105] 目前二位数组仅支持 加减
 * @param $data
 * @return mixed
 */
public function update($data) {
    $updateFields = [];
    foreach ($data as $key =&gt; $value) {
        if(!is_array($value)){
            $updateFields[] = "`$key`=:u{$key} ";
        }else{
            $updateFields[] = "`$key`= `{$key}` {$value[0]} {$value[1]}";
            unset($data[$key]);
        }
    }
    $this-&gt;sql['up'] = $data;
    $updateFields = implode(',', $updateFields);
    $querySql = sprintf("UPDATE %s SET %s", $this-&gt;tablename, $updateFields);

    if(!empty($this-&gt;sql['where'])) {
        $querySql .= ' WHERE ' . $this-&gt;sql['wheresql'];
    }

    return $this-&gt;_query($querySql);
}

private function _query($querySql) {
    $querystr = strtolower(trim(substr($querySql,0,6)));
    $stmt = $this-&gt;conn[$this-&gt;type]-&gt;prepare($querySql);
    //绑定参数 where
    if($this-&gt;sql['wheresql']){
        foreach($this-&gt;sql['where'] as $k=&gt;$v){
            if(!is_array($v)){
                $val = $v;
            }else{
                $val = $v[1];
            }
            $stmt-&gt;bindValue('w'.$k,addslashes($val));
        }
    }

    //update
    if($this-&gt;sql['up']){
        foreach($this-&gt;sql['up'] as $k=&gt;$v){
            $stmt-&gt;bindValue('u'.$k,trim($v,'\''));
        }
    }

    //install
    if($this-&gt;sql['ins']){
        foreach($this-&gt;sql['ins'] as $k=&gt;$v){
            $stmt-&gt;bindValue('i'.$k,addslashes($v));
        }
    }

    $ret = $stmt-&gt;execute();
    $this-&gt;sql = [];
    if(!$ret) var_dump($stmt-&gt;errorInfo());

    if($querystr == 'select') {
        $retData = $stmt-&gt;fetchAll(PDO::FETCH_ASSOC);
        return $retData;
    }elseif($ret &amp;&amp; $querystr == 'insert') {
        return $this-&gt;conn[$this-&gt;type]-&gt;lastInsertId();
    }else{
        return $ret;
    }
}

public function limit($limit, $limitCount = null) {
    if(!$limitCount) {
        $this-&gt;sql['limit'] = $limit;
    }else{
        $this-&gt;sql['limit'] = $limit .','. $limitCount;
    }
    return $this;
}

public function orderBy($orderBy) {
    $this-&gt;sql['orderBy'] = $orderBy;
    return $this;
}

public function where($where) {
    if(!is_array($where)) {
        return null;
    }

    $crondsArr = [];
    foreach ($where as $key =&gt; $value) {
        $fieldValue = $value;
        if(is_array($fieldValue)) {
            $crondsArr[] = "`$key` ".$fieldValue[0]. ' :w' . $key;
        }else{
            $crondsArr[] = "`$key`=:w{$key}";
        }
    }

    $this-&gt;sql['wheresql'] = implode(' AND ', $crondsArr);
    $this-&gt;sql['where'] = $where;
    return $this;
}

public function close() {
    return $this-&gt;conn = null;
}

//开启事务
public function startTrans(){
    if(empty($this-&gt;conn[$this-&gt;type])){
        exit();
    }
    $this-&gt;conn[$this-&gt;type]-&gt;beginTransaction();
}

//提交事务
public function dbCommit(){
    if(empty($this-&gt;conn[$this-&gt;type])){
        exit();
    }
    $this-&gt;conn[$this-&gt;type]-&gt;commit();
}

//回滚事务
public function dbRollBack(){
    if(empty($this-&gt;conn[$this-&gt;type])){
        exit();
    }
    $this-&gt;conn[$this-&gt;type]-&gt;rollBack();
}

/**
 * 原生查询
 * @param $querySql
 * @return mixed
 */
public function query($querySql) {
    $querystr = strtolower(trim(substr($querySql,0,6)));
    $stmt = $this-&gt;conn[$this-&gt;type]-&gt;prepare($querySql);

    $ret = $stmt-&gt;execute();
    $this-&gt;sql = [];
    if(!$ret) var_dump($stmt-&gt;errorInfo());

    if($querystr == 'select') {
        $retData = $stmt-&gt;fetchAll(PDO::FETCH_ASSOC);
        return $retData;
    }elseif($ret &amp;&amp; $querystr == 'insert') {
        return $this-&gt;conn[$this-&gt;type]-&gt;lastInsertId();
    }else{
        return $ret;
    }
}

private function _getDBIP($tableName){
    switch($tableName){
        default:
            return array(
                'name' =&gt;'server',
                'DB_IP'   =&gt; DB_IP,
                'DB_NAME' =&gt; DB_NAME,
                'DB_USER' =&gt; DB_USER,
                'DB_PASS' =&gt; DB_PASS,
            );
            break;

        case 'xxxx':
        case 'xxxx':
            return array(
                'name' =&gt;'master',
                'DB_IP'   =&gt; DB_MAS_IP,
                'DB_NAME' =&gt; DB_MAS_NAME,
                'DB_USER' =&gt; DB_MAS_USER,
                'DB_PASS' =&gt; DB_MAS_PASS,
            );
            break;

        case 'xxx':
        case 'xxx':
            return array(
                'name' =&gt;'manage',
                'DB_IP'   =&gt; DB_MNG_IP,
                'DB_NAME' =&gt; DB_MNG_NAME,
                'DB_USER' =&gt; DB_MNG_USER,
                'DB_PASS' =&gt; DB_MNG_PASS,
            );
            break;

        // LOGS
        case 'xxx':
            return array(
                'name' =&gt;'log',
                'DB_IP'   =&gt; DB_LOG_IP,
                'DB_NAME' =&gt; DB_LOG_NAME,
                'DB_USER' =&gt; DB_LOG_USER,
                'DB_PASS' =&gt; DB_LOG_PASS,
            );
            break;
    }
}

}

 

本文由【曲文】发布于开源中国,原文链接:https://my.oschina.net/u/3538113/blog/3073523

全部评论: 0

    我有话说: