問題描述：

有什么方法可以杜絕sql注入的問題？

解決方法：

用htmlspecialchars/HTMLPurifier防御XSS注入,用預(yù)處理參數(shù)化查詢防御SQL注入

調(diào)用HTMLPurifier過濾XSS后輸出HTML:

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

MySQLi綁定參數(shù)查詢:

$db = @new mysqli();
$stmt = $db->prepare('SELECT * FROM posts WHERE id=?'); //預(yù)處理
$stmt->bind_param('i', $id); //綁定參數(shù)
$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 內(nèi)容為:
Type: FIELD_TYPE_LONGLONG (8)
Unsigned: 0
Value: 1
可見PHP將SQL模板和變量分兩次發(fā)送給MySQL,由MySQL完成變量的轉(zhuǎn)義處理.
既然SQL模板和變量是分兩次發(fā)送的,那么就不存在SQL注入的問題了.

在MySQL的general_log里可以看到:
Prepare SELECT username FROM pb_users WHERE id=?
Execute SELECT username FROM pb_users WHERE id=1
如果ID綁定為string,則Execute時(shí)id賦值是這樣的: id='1'
如果PDO沒有關(guān)閉模擬預(yù)處理,則可以看到:
Query SELECT username FROM pb_users WHERE id=1