至尊技术网 - 预处理
https://www.zzwws.cn/tag/%E9%A2%84%E5%A4%84%E7%90%86/
-
PHP面向对象封装MySQL PDO（已使用预处理）
https://www.zzwws.cn/archives/6343/
2022-06-10T11:38:00+08:00
Mysql.class.php<?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);
-
PHP防止SQL注入
https://www.zzwws.cn/archives/5551/
2021-08-14T21:02:00+08:00
SQL注入的万能语句' or 1=1#当我们在用户名处输入' or 1=1#，密码随便写，把它带入到语句中，就变成了select * from user where uername='' or1=1#' and password='123456'
在SQL语句中#是注释符，所以后面的语句都会被注释掉select * from user where username='' or1=1
也就是说可以用 'or 1=1# 这么一个字符串就可以绕开登录的密码，直接进入程序，防止这种情况出现，可以使用以下的预处理机制！使用 PDO1、通过传递一个插入值的数组执行一条预处理语句$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/