扫码加入数据分析学习群

问题描述：

有什么方法可以杜绝sql注入的问题？

解决方法：

用htmlspecialchars/HTMLPurifier防御XSS注入,用预处理参数化查询防御SQL注入

调用HTMLPurifier过滤XSS后输出HTML:

require dirname(__FILE__).'/htmlpurifier/library/HTMLPurifier.auto.php';
$purifier = new HTMLPurifier();
echo $purifier->purify($html);

MySQLi绑定参数查询:

$db = @new mysqli();
$stmt = $db->prepare('SELECT * FROM posts WHERE id=?'); //预处理
$stmt->bind_param('i', $id); //绑定参数
$stmt->execute(); //查询
var_export($stmt->get_result()->fetch_all());

WireShark里用tcp.port==3306过滤分析PHP和MySQL通信

<?php
//MySQLi
$id = 1;
$mysqli = new mysqli('127.0.0.1', 'punbb', 'punbb', 'punbb');
$mysqli->set_charset('utf8');
$stmt = $mysqli->prepare("SELECT `username` FROM `pb_users` WHERE `id`=?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($username);
while ($stmt->fetch()) echo $username;
$stmt->close();
$mysqli->close();

<?php
//PDO
$id = 1;
//$dbh = new PDO('sqlite:/path/to/punbb.db3');
$dsn = "mysql:dbname=punbb;host=127.0.0.1;port=3306;charset=utf8";
$dbh = new PDO($dsn, 'punbb', 'punbb');
$dbh->query('SET NAMES utf8');
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$sth = $dbh->prepare("SELECT `username` FROM `pb_users` WHERE `id`=?");
$sth->bindParam(1, $id, PDO::PARAM_INT);
$sth->execute();
print_r($sth->fetchAll(PDO::FETCH_ASSOC));
$sth = null;
$dbh = null;

在 Request Prepare Statement 里可以看到 SELECT username FROM pb_users WHERE id=?
在 Request Execute Statement 里可以看到 Parameter 内容为:
Type: FIELD_TYPE_LONGLONG (8)
Unsigned: 0
Value: 1
可见PHP将SQL模板和变量分两次发送给MySQL,由MySQL完成变量的转义处理.
既然SQL模板和变量是分两次发送的,那么就不存在SQL注入的问题了.

在MySQL的general_log里可以看到:
Prepare SELECT username FROM pb_users WHERE id=?
Execute SELECT username FROM pb_users WHERE id=1
如果ID绑定为string,则Execute时id赋值是这样的: id='1'
如果PDO没有关闭模拟预处理,则可以看到:
Query SELECT username FROM pb_users WHERE id=1