<?php
class Mysql
{
    public $link;

    public function __construct()
    {
        $this->conn();
    }

    /**
     * 连接数据库，从配置文件读取配置信息
     */
    public function conn()
    {
        $cfg = require 'config.php';
        try {
            $this->link = new PDO("mysql:dbname={$cfg['databaseName']};host={$cfg['host']};charset={$cfg['charset']};port={$cfg['port']}", $cfg['name'], $cfg['password']);
            $this->link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // 设置禁止本地模拟prepare
            //$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// 设置捕获异常
        } catch (PDOException $e) {
            die("Error: " . $e->getMessage());
        }
    }

    /**
     * 查询多行数据
     * @param string $table 表名字
     * @param string $where where条件
     * @param string $field 字段
     * @param string $additional 附加sql语句
     * @return array
     */
    public function getAll($table, $where = [], $field = "*", $additional = '')
    {
        if (strpos($field, ",") !== false) {
            $arr = explode(",", $field);
            $str = '';
            foreach ($arr as $v) {
                $str .= "`{$v}`,";
            }
            $field = substr($str, 0, -1);
        } else if ($field != "*") {
            $field = "`{$field}`";
        }
        $sql = "SELECT {$field} FROM `{$table}`";
        $sql2 = '';
        $value = [];
        if ($where) {
            if (!is_array($where[0])) {
                if (strtolower($where[1]) == 'in') {
                    $where[1] = 'IN';
                    $sql2 = " `{$where[0]}` {$where[1]} (";
                    if (is_array($where[2])) {
                        foreach ($where[2] as $v) {
                            $value[] = $v;
                            $sql2 .= '?,';
                        }
                    } else {
                        $value[] = $where[2];
                        $sql2 .= '?';
                    }
                    $sql2 = rtrim($sql2, ',') . ')';
                } else {
                    $value[] = $where[2];
                    $sql2 = " `{$where[0]}` {$where[1]} ?";
                }
            } else {
                foreach ($where as $v) {
                    if (strtolower($v[1]) == 'in') {
                        $v[1] = 'IN';
                        $sql2 .= " `{$v[0]}` {$v[1]} (";
                        if (is_array($v[2])) {
                            foreach ($v[2] as $v2) {
                                $value[] = $v2;
                                $sql2 .= "?,";
                            }
                        } else {
                            $value[] = $v[2];
                            $sql2 .= "?";
                        }
                        $sql2 = rtrim($sql2, ',') . ') AND';
                    } else {
                        $value[] = $v[2];
                        $sql2 .= " `{$v[0]}` {$v[1]} ? AND";
                    }
                }
                $sql2 = substr($sql2, 0, -4);
            }
            if ($sql2) {
                $sql .= " WHERE " . $sql2;
            }
        }
        if ($additional) {
            $sql .= ' ' . $additional;
        }
        $res = $this->link->prepare($sql);
        $res->execute($value);
        $data = $res->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }

    /**
     * 查询单行数据
     * @param string $table 表名字
     * @param string $where where条件
     * @param string $field 字段
     * @param string $additional 附加sql语句
     * @return array
     */
    public function getRow($table, $where = [], $field = "*", $additional = '')
    {
        if (strpos($field, ",") !== false) {
            $arr = explode(",", $field);
            $str = '';
            foreach ($arr as $v) {
                $str .= "`{$v}`,";
            }
            $field = substr($str, 0, -1);
        } else if ($field != "*") {
            $field = "`{$field}`";
        }
        $sql = "SELECT {$field} FROM `{$table}`";
        $sql2 = '';
        $value = [];
        if ($where) {
            if (!is_array($where[0])) {
                if (strtolower($where[1]) == 'in') {
                    $where[1] = 'IN';
                    $sql2 = " `{$where[0]}` {$where[1]} (";
                    if (is_array($where[2])) {
                        foreach ($where[2] as $v) {
                            $value[] = $v;
                            $sql2 .= '?,';
                        }
                    } else {
                        $value[] = $where[2];
                        $sql2 .= '?';
                    }
                    $sql2 = rtrim($sql2, ',') . ')';
                } else {
                    $value[] = $where[2];
                    $sql2 = " `{$where[0]}` {$where[1]} ?";
                }
            } else {
                foreach ($where as $v) {
                    if (strtolower($v[1]) == 'in') {
                        $v[1] = 'IN';
                        $sql2 .= " `{$v[0]}` {$v[1]} (";
                        if (is_array($v[2])) {
                            foreach ($v[2] as $v2) {
                                $value[] = $v2;
                                $sql2 .= "?,";
                            }
                        } else {
                            $value[] = $v[2];
                            $sql2 .= "?";
                        }
                        $sql2 = rtrim($sql2, ',') . ') AND';
                    } else {
                        $value[] = $v[2];
                        $sql2 .= " `{$v[0]}` {$v[1]} ? AND";
                    }
                }
                $sql2 = substr($sql2, 0, -4);
            }
            if ($sql2) {
                $sql .= " WHERE " . $sql2;
            }
        }
        if ($additional) {
            $sql .= ' ' . $additional;
        }
        $res = $this->link->prepare($sql);
        $res->execute($value);
        $data = $res->fetch(PDO::FETCH_ASSOC);
        return $data;
    }

    /**
     * 自动创建sql语句并执行
     * @param string $table 表名字
     * @param array $data 关联数组 键/值与表的列/值对应
     * @param string $act 1为insert，2为update
     * @param array $where 条件，用于update
     * @return int 成功为insert产生的主键值,update是影响的行数，失败为0
     */
    public function exec($table, $data, $act = 1, $where = [])
    {
        $value = [];
        if ($act == 1) {
            $sql = "INSERT INTO `{$table}` (`";
            $sql .= implode('`,`', array_keys($data)) . '`)';
            $str = '';
            foreach ($data as $v) {
                $str .= "?,";
            }
            $str = substr($str, 0, -1);
            $sql .= " VALUES ({$str})";
            $value = array_values($data);
        } else {
            $sql = "UPDATE `{$table}` SET ";
            foreach ($data as $k => $v) {
                $sql .= "`" . $k . '`= ' . " ?,";
                $value[] = $v;
            }
            $sql = rtrim($sql, ',');
            $sql2 = '';
            if ($where) {
                if (!is_array($where[0])) {
                    if (strtolower($where[1]) == 'in') {
                        $where[1] = 'IN';
                        $sql2 = " `{$where[0]}` {$where[1]} (";
                        if (is_array($where[2])) {
                            foreach ($where[2] as $v) {
                                $value[] = $v;
                                $sql2 .= '?,';
                            }
                        } else {
                            $value[] = $where[2];
                            $sql2 .= '?';
                        }
                        $sql2 = rtrim($sql2, ',') . ')';
                    } else {
                        $value[] = $where[2];
                        $sql2 = " `{$where[0]}` {$where[1]} ?";
                    }
                } else {
                    foreach ($where as $v) {
                        if (strtolower($v[1]) == 'in') {
                            $v[1] = 'IN';
                            $sql2 .= " `{$v[0]}` {$v[1]} (";
                            if (is_array($v[2])) {
                                foreach ($v[2] as $v2) {
                                    $value[] = $v2;
                                    $sql2 .= "?,";
                                }
                            } else {
                                $value[] = $v[2];
                                $sql2 .= "?";
                            }
                            $sql2 = rtrim($sql2, ',') . ') AND';
                        } else {
                            $value[] = $v[2];
                            $sql2 .= " `{$v[0]}` {$v[1]} ? AND";
                        }
                    }
                    $sql2 = substr($sql2, 0, -4);
                }
                if ($sql2) {
                    $sql .= " WHERE " . $sql2;
                }
            }
        }
        $res = $this->link->prepare($sql);
        $run = $res->execute($value);
        if ($run) {
            if ($act == 1) {
                return $this->link->lastInsertId();
            } else {
                return $res->rowCount();
            }
        } else {
            return 0;
        }
    }

    /**
     * 删除数据
     * @param string $table 表名字
     * @param array $where where条件
     * @return bool
     */
    public function delete($table, $where = [])
    {
        $sql = "DELETE FROM `{$table}`";
        $sql2 = '';
        $value = [];
        if ($where) {
            if (!is_array($where[0])) {
                if (strtolower($where[1]) == 'in') {
                    $where[1] = 'IN';
                    $sql2 = " `{$where[0]}` {$where[1]} (";
                    if (is_array($where[2])) {
                        foreach ($where[2] as $v) {
                            $value[] = $v;
                            $sql2 .= '?,';
                        }
                    } else {
                        $value[] = $where[2];
                        $sql2 .= '?';
                    }
                    $sql2 = rtrim($sql2, ',') . ')';
                } else {
                    $value[] = $where[2];
                    $sql2 = " `{$where[0]}` {$where[1]} ?";
                }
            } else {
                foreach ($where as $v) {
                    if (strtolower($v[1]) == 'in') {
                        $v[1] = 'IN';
                        $sql2 .= " `{$v[0]}` {$v[1]} (";
                        if (is_array($v[2])) {
                            foreach ($v[2] as $v2) {
                                $value[] = $v2;
                                $sql2 .= "?,";
                            }
                        } else {
                            $value[] = $v[2];
                            $sql2 .= "?";
                        }
                        $sql2 = rtrim($sql2, ',') . ') AND';
                    } else {
                        $value[] = $v[2];
                        $sql2 .= " `{$v[0]}` {$v[1]} ? AND";
                    }
                }
                $sql2 = substr($sql2, 0, -4);
            }
            if ($sql2) {
                $sql .= " WHERE " . $sql2;
            }
        }
        $res = $this->link->prepare($sql);
        return $res->execute($value);
    }

    /**
     * count数据
     * @param string $table 表名字
     * @param array $where where条件
     * @param string $field 字段
     * @return int
     */
    public function count($table, $where = [],$field = '*')
    {
        $sql = "SELECT COUNT({$field}) FROM `{$table}`";
        $sql2 = '';
        $value = [];
        if ($where) {
            if (!is_array($where[0])) {
                if (strtolower($where[1]) == 'in') {
                    $where[1] = 'IN';
                    $sql2 = " `{$where[0]}` {$where[1]} (";
                    if (is_array($where[2])) {
                        foreach ($where[2] as $v) {
                            $value[] = $v;
                            $sql2 .= '?,';
                        }
                    } else {
                        $value[] = $where[2];
                        $sql2 .= '?';
                    }
                    $sql2 = rtrim($sql2, ',') . ')';
                } else {
                    $value[] = $where[2];
                    $sql2 = " `{$where[0]}` {$where[1]} ?";
                }
            } else {
                foreach ($where as $v) {
                    if (strtolower($v[1]) == 'in') {
                        $v[1] = 'IN';
                        $sql2 .= " `{$v[0]}` {$v[1]} (";
                        if (is_array($v[2])) {
                            foreach ($v[2] as $v2) {
                                $value[] = $v2;
                                $sql2 .= "?,";
                            }
                        } else {
                            $value[] = $v[2];
                            $sql2 .= "?";
                        }
                        $sql2 = rtrim($sql2, ',') . ') AND';
                    } else {
                        $value[] = $v[2];
                        $sql2 .= " `{$v[0]}` {$v[1]} ? AND";
                    }
                }
                $sql2 = substr($sql2, 0, -4);
            }
            if ($sql2) {
                $sql .= " WHERE " . $sql2;
            }
        }
        $res = $this->link->prepare($sql);
        $res->execute($value);
        $data = $res->fetch(PDO::FETCH_NUM);
        return $data[0];
    }
}

config.php

<?php
return array(
    'host' => 'localhost',
    'name' => 'root',
    'password' => 'root',
    'databaseName' => 'cs_cn',
    'port' => '3306',
    'charset' => 'utf8'
);

使用方法

<?php
require 'Mysql.class.php';

$mysql = new Mysql();

// 添加
$data = [
    'code' => mt_rand(1000000000,9999999999),
    'url' => 'https://www.zzwws.cn',
    'ip' => mt_rand(1000000000,9999999999),
    'add_time' => time()
];
$res = $mysql->exec('zz_url',$data);
if($res){
    echo '添加成功';
}else{
    echo '添加失败';
}

// 修改
$data = [
    'code' => mt_rand(1000000000,9999999999)
];
$where = ['id','=',51];
// 或者
// $where = [
//     ['id','=',51],
//     ['ip','=','3755406202']
// ];

$res = $mysql->exec('zz_url',$data,2,$where);
if($res){
    echo '修改成功';
}else{
    echo '修改失败';
}

// 查询一行数据
$row = $mysql->getRow('zz_url',['id','=',51],'id,code');
if(!$row){
    echo '获取失败';
}
print_r($row);

// 查询多行数据
$rows = $mysql->getAll('zz_url');
print_r($rows);

// 删除
$where = [
    ['id','=',52]
];
// 或者
// $where = [
//     ['id','in',[1,2,3]]
// ];
$res = $mysql->delete('zz_url',$where);
var_dump($res);

// count
$count = $mysql->count('zz_url',['url','=','https://www.zzwws.cn']);
var_dump($count);

// query方法（没有预处理）
$res = $mysql->link->query("SELECT * FROM zz_url WHERE id = '1'");
$row = $res->fetch(PDO::FETCH_ASSOC);
print_r($row);

当我们在用户名处输入' or 1=1#，密码随便写，把它带入到语句中，就变成了

select * from user where uername='' or1=1#' and password='123456'

在SQL语句中#是注释符，所以后面的语句都会被注释掉

select * from user where username='' or1=1

也就是说可以用 'or 1=1# 这么一个字符串就可以绕开登录的密码，直接进入程序，防止这种情况出现，可以使用以下的预处理机制！

使用 PDO

1、通过传递一个插入值的数组执行一条预处理语句

$st = $db->prepare('select * from zz_order where id = ? and name = ?');
$st->execute([$id,$name]);// 成功时返回 true，失败时返回 false
$data = $st->fetch(PDO::FETCH_ASSOC);
print_r($data);

2、在 prepare 函数里面把参数用 ‘:name’ 这样的形式来替代，然后使用 execute 绑定参数。

$st = $db->prepare('select * from zz_order where id = :id');
$st->execute([':id' => $id]);
$data = $st->fetch(PDO::FETCH_ASSOC);
print_r($data);

3、通过绑定 PHP 变量执行一条预处理语句

$st = $db->prepare("select * from zz_order where id = ?");
$st->bindParam(1, $id);
$st->execute();
$data = $st->fetchAll(PDO::FETCH_ASSOC);
print_r($data);

使用 Mysqli

在 prepare 函数里面把参数用 ‘?’ 来替代，然后使用 bind_param 绑定参数。在 bind_param 中，第一个参数 's' 代表了参数的类型与个数（此处为一个字符串类型）。

$stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

//bind_param参数类型
i - integer（整型）
d - double（双精度浮点型）
s - string（字符串）
b - BLOB（布尔值）

PDO

需要注意的是使用PDO去访问MySQL数据库时，真正的prepared statements默认情况下是不使用的。为了解决这个问题，你需要禁用模拟的prepared statements。下面是使用PDO创建一个连接的例子：

try {
    $db = new PDO('mysql:dbname=cs_cn;host=127.0.0.1;charset=utf8', 'root', 'root');
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);// 设置禁止本地模拟prepare
    //$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// 设置捕获异常
} catch (PDOException $e) {
    die("Error!: " . $e->getMessage() . "<br/>");
}

ThinkPHP防止sql注入：https://www.zzwws.cn/archives/5488/