雖然參數化查詢是防禦 SQL Injection 的根本之道,但舊系統或動態 SQL 場景仍需字串層面的過濾。本文提供三個互補的靜態方法:關鍵字黑名單檢測、危險字元檢測、單引號轉義,並說明其適用場景與局限。
using System;
using System.Text.RegularExpressions;
namespace CLB.Utility.Security
{
public static class SqlSanitizer
{
// 1. 關鍵字黑名單 (不區分大小寫)
private static readonly string[] SqlKeywords = new[]
{
"exec", "execute", "insert", "select", "delete", "update", "drop", "create", "alter",
"truncate", "declare", "xp_", "sp_", "--", ";", "/*", "*/", "@@", "char", "nchar",
"varchar", "nvarchar", "cast", "convert", "substring", "ascii", "sysobjects", "syscolumns"
};
///
/// 檢查字串是否包含高風險 SQL 關鍵字 (黑名單模式)
///
/// true = 相對安全, false = 疑似含攻擊語句
public static bool IsSafeSqlString(string input)
{
if (string.IsNullOrWhiteSpace(input)) return true;
string lower = input.ToLowerInvariant();
foreach (string kw in SqlKeywords)
{
if (lower.Contains(kw)) return false;
}
return true;
}
///
/// 使用正則檢測危險字元 (單引號、分號、註解符號等)
///
public static bool HasDangerousChars(string input)
{
if (string.IsNullOrWhiteSpace(input)) return false;
// 單引號、分號、雙破折號、斜線、括號、大括號、百分號、@、星號、驚嘆號
return Regex.IsMatch(input, @"[';\\\-/*(){}%@*!]");
}
///
/// 單引號轉義:' → '' (SQL Server 標準轉義)
///
public static string EscapeSingleQuotes(string input)
{
if (string.IsNullOrEmpty(input)) return string.Empty;
return input.Replace("'", "''");
}
///
/// 綜合檢查:回傳 (是否安全, 風險描述)
///
public static (bool Safe, string Reason) Validate(string input)
{
if (string.IsNullOrWhiteSpace(input)) return (true, "空值");
if (!IsSafeSqlString(input)) return (false, "包含高風險 SQL 關鍵字");
if (HasDangerousChars(input)) return (false, "包含危險字元");
return (true, "通過");
}
}
}
string userInput = "admin' --";
var (safe, reason) = SqlSanitizer.Validate(userInput);
if (!safe)
{
Log.Warn($"SQL 注入嘗試: {reason} | Input: {userInput}");
return BadRequest("輸入包含非法字元");
}
// 若必須拼接 SQL (不推薦),至少轉義
string safeSql = $"SELECT * FROM Users WHERE Name = '{SqlSanitizer.EscapeSingleQuotes(userInput)}'";
SeLeCtSELECTSEL/**/ECT' + 'SEL' + 'ECT' + '唯一可靠防禦:參數化查詢 / 預存程序 / ORM 參數繫結
// ADO.NET
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE Username=@u AND Pwd=@p", conn))
{
cmd.Parameters.AddWithValue("@u", username);
cmd.Parameters.AddWithValue("@p", passwordHash);
using (var reader = cmd.ExecuteReader()) { /* ... */ }
}
// Dapper
var user = conn.QuerySingleOrDefault(
"SELECT * FROM Users WHERE Username=@u AND Pwd=@p", new { u = username, p = passwordHash });
// EF Core
var user = await ctx.Users.FirstOrDefaultAsync(u => u.Username == username && u.PasswordHash == passwordHash);
| 場景 | 建議方案 |
|---|---|
| 新開發專案 | 全面使用參數化 / ORM,禁用字串拼接 |
| 維護舊系統 | 漸進式重構:先加上本工具類做二線防禦,再逐步改參數化 |
| 動態報表/查詢建構器 | 白名單驗證欄位名、資料表名;數值型參數化;嚴格限制 WHERE 子句結構 |
| WAF/入侵偵測 | 部署 ModSecurity、Azure WAF、Cloudflare WAF 等網路層防護 |