检测SQL注入的正则表达式或替代方法咨询（IIS部署的.NET应用）

Hey there, based on your scenario—15 mixed ASP.NET/ASP.NET MVC apps running on IIS 7.5, relying on a third-party service that now blocks SQL injection attempts—let’s break down practical ways to detect and address this, beyond just regex (though we’ll cover that too).

一、正则表达式检测（快速但有局限性） #

Regex can work as a first line of defense, but it’s not a silver bullet—SQL injection comes in so many variants that no regex can catch everything. That said, here are some patterns you can use for basic detection:

• Core SQL keyword detection (matches common manipulation verbs):

\b(ALTER|CREATE|DELETE|DROP|EXEC(UTE)?|INSERT( +INTO)?|MERGE|SELECT|UPDATE|UNION( +ALL)?)\b

When using this in .NET, pair it with RegexOptions.IgnoreCase to catch uppercase/lowercase variations.

• Patterns with suspicious syntax (targets logical operators, quotes, or execution calls):

(\b(AND|OR)\b\s*=\s*[\'"]?|\b(SELECT|INSERT|UPDATE|DELETE)\b.*[\'"]|\bEXEC\b.*\()

⚠️ Important caveat: Regex is prone to false positives. For example, a user entering "Select your subscription plan" might get flagged. So always apply these checks only to user input parameters (not free-text fields where such words are legitimate) and tune the regex to your specific use case.

二、更可靠的检测与预防方案 #

Regex is a band-aid—these approaches get to the root of the problem:

1. 强制使用参数化查询 #

这是预防SQL注入的黄金标准。在ASP.NET中，使用SqlCommand配合命名参数，而非字符串拼接：

// 安全写法：参数化查询
string query = "SELECT Username FROM Users WHERE UserId = @UserId";
using (SqlCommand cmd = new SqlCommand(query, dbConnection))
{
    cmd.Parameters.AddWithValue("@UserId", userInputId);
    // 执行查询...
}

检测技巧：用静态代码分析工具扫描代码库，找出用户输入直接拼接进SQL字符串的场景（比如string.Format("SELECT * FROM Users WHERE Id = {0}", userId)）。

2. 使用ORM框架 #

Entity Framework、Dapper这类库会自动对查询进行参数化，消除手动拼接SQL的风险。
检测技巧：审计代码中所有绕过ORM直接写原生SQL的部分——这些是高风险点，需要优先修复。

3. IIS 7.5 URL重写规则 #

利用IIS的URL Rewrite模块，在请求到达应用前就拦截可疑请求。在web.config中添加以下配置：

<rewrite>
  <rules>
    <rule name="Block SQL Injection Attempts" stopProcessing="true">
      <match url=".*" />
      <conditions logicalGrouping="MatchAny">
        <add input="{QUERY_STRING}" pattern="\b(ALTER|CREATE|DELETE|DROP|EXEC|INSERT|SELECT|UPDATE|UNION)\b" ignoreCase="true" />
        <add input="{REQUEST_BODY}" pattern="\b(ALTER|CREATE|DELETE|DROP|EXEC|INSERT|SELECT|UPDATE|UNION)\b" ignoreCase="true" />
      </conditions>
      <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Potential SQL Injection Attempt" />
    </rule>
  </rules>
</rewrite>

可以调整规则减少误报——比如排除那些合法输入中可能包含SQL类词汇的端点。

4. 静态代码分析工具 #

SonarQube、JetBrains ReSharper或微软代码分析工具，能自动标记代码库中的SQL注入漏洞，比如未参数化的查询、不安全的输入处理逻辑。

5. 运行时Action过滤器（ASP.NET MVC） #

添加全局过滤器，在运行时扫描所有请求参数中的可疑模式：

public class SqlInjectionDetectionFilter : ActionFilterAttribute
{
    private static readonly Regex SqlInjectionRegex = new Regex(
        @"\b(ALTER|CREATE|DELETE|DROP|EXEC|INSERT|SELECT|UPDATE|UNION)\b",
        RegexOptions.IgnoreCase | RegexOptions.Compiled);

    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        // 检查查询字符串参数
        foreach (var qsParam in filterContext.HttpContext.Request.QueryString)
        {
            if (qsParam is string key && SqlInjectionRegex.IsMatch(filterContext.HttpContext.Request.QueryString[key]))
            {
                LogSuspiciousAttempt(filterContext.HttpContext.Request);
                filterContext.Result = new HttpStatusCodeResult(HttpStatusCode.Forbidden);
                return;
            }
        }

        // 检查表单参数
        foreach (var formParam in filterContext.HttpContext.Request.Form)
        {
            if (formParam is string key && SqlInjectionRegex.IsMatch(filterContext.HttpContext.Request.Form[key]))
            {
                LogSuspiciousAttempt(filterContext.HttpContext.Request);
                filterContext.Result = new HttpStatusCodeResult(HttpStatusCode.Forbidden);
                return;
            }
        }

        base.OnActionExecuting(filterContext);
    }

    private void LogSuspiciousAttempt(HttpRequestBase request)
    {
        // 记录IP、请求路径、可疑参数等信息用于审计
        // 示例：Logger.Warn($"SQL injection attempt from {request.UserHostAddress}: {request.RawUrl}");
    }
}

在Global.asax中注册过滤器：

protected void Application_Start()
{
    // ... 其他启动代码
    GlobalFilters.Filters.Add(new SqlInjectionDetectionFilter());
}

三、总结 #

正则可以作为快速检测手段，但参数化查询和ORM才是彻底消除SQL注入风险的唯一方式。结合静态代码扫描、IIS规则和运行时过滤器，构建分层防御体系。另外，别忘了对所有用户输入进行验证和清理——在可能的情况下限制输入的字符类型和长度，进一步降低攻击面。

内容的提问来源于stack exchange，提问作者Venkata Pujarla