SQL 注入 (SQLi) 是 Web 應用最危險、最普遍的漏洞 (OWASP Top 10 長期榜首)。攻擊者通過構造惡意輸入,改變 SQL 語句語義,實現繞過認證、數據竊取/篡改/刪除、命令執行甚至服務器接管。核心修復原則:**永不信任用戶輸入,嚴格參數化查詢,多層過濾驗證。**
$sql = "SELECT * FROM users WHERE username = '$_POST[username]' AND password = '$_POST[password]'";
$mysqli->query($sql);
<?php
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "app_user", "strong_password", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false, // 關鍵:使用真正預編譯而非模擬
]);
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->execute([$_POST['username'], $_POST['password']]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
$mysqli = new mysqli("localhost", "app_user", "strong_password", "test");
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $_POST['username'], $_POST['password']);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
<?php
function dowith_sql($str) {
$refuse_str = "exec|and|or|select|update|from|where|order|by|*|delete|insert|into|values|create|table|database|set|char|asc|cast|declare";
$arr = explode("|", $refuse_str);
for ($i = 0; $i < count($arr); $i++) {
$replace = "[" . $arr[$i] . "]";
$str = str_ireplace($arr[$i], $replace, $str); // 不區分大小寫替換
}
return $str;
}
foreach (['_GET', '_POST', '_COOKIE', '_REQUEST'] as $superglobal) {
foreach ($GLOBALS[$superglobal] as $key => $value) {
if (is_string($value)) {
$GLOBALS[$superglobal][$key] = dowith_sql($value);
}
}
}
?>
此法缺陷:
• 破壞正常數據 (如用戶名含 "select")
• 易繞過:大小寫變形、編碼繞過 (URL encode/Unicode)、註釋截斷、二次注入
• 未覆蓋 $_COOKIE/$_REQUEST/$_SERVER/$_FILES 等超全局變量
• 破壞 JSON/XML/序列化數據結構
<?php
if (isset($_COOKIE['uid'])) {
$uid = $_COOKIE['uid'];
if (!ctype_digit($uid)) { // 僅允許純數字
die("Invalid UID");
}
// 後續查詢仍必須用參數化
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$uid]);
}
?>
<%
' 連接字符串
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=test;User ID=app_user;Password=strong_password"
' 參數化查詢
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM users WHERE username = ? AND password = ?"
cmd.CommandType = 1 ' adCmdText
cmd.Prepared = True
' 追加參數 (類型, 方向, 大小, 值)
cmd.Parameters.Append cmd.CreateParameter("@username", 200, 1, 50, Request.Form("username")) ' adVarChar=200
cmd.Parameters.Append cmd.CreateParameter("@password", 200, 1, 50, Request.Form("password"))
Set rs = cmd.Execute
If Not rs.EOF Then
' 登入成功
End If
rs.Close: conn.Close
%>
原文提供的黑名單過濾代碼 (sql2.asp) 同樣屬臨時補丁,**強烈建議改用參數化查詢**。
<%@ page import="java.sql.*" %>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "app_user", "strong_password");
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, request.getParameter("username"));
pstmt.setString(2, request.getParameter("password"));
rs = pstmt.executeQuery();
if (rs.next()) {
// 登入成功
}
} catch (Exception e) {
log.error("SQL Error", e); // 記錄日誌,不回顯
} finally {
if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
if (pstmt != null) try { pstmt.close(); } catch (SQLException ignore) {}
if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
}
%>
框架層面:
• MyBatis: `