萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

動態 SQL 拼接最佳實務:WHERE 1=1 技巧、參數化防注入、避免排列組合爆炸

常見反模式:參數排列組合寫死 SQL

// 錯誤示範:每種參數組合寫一條 SQL
if (id > 0 && string.IsNullOrEmpty(name)) {
    cmd.CommandText = "SELECT * FROM T WHERE Id = @id";
    cmd.Parameters.AddWithValue("@id", id);
}
else if (id <= 0 && !string.IsNullOrEmpty(name)) {
    cmd.CommandText = "SELECT * FROM T WHERE Name = @name";
    cmd.Parameters.AddWithValue("@name", name);
}
else if (id > 0 && !string.IsNullOrEmpty(name)) {
    cmd.CommandText = "SELECT * FROM T WHERE Id = @id AND Name = @name";
    cmd.Parameters.AddWithValue("@id", id);
    cmd.Parameters.AddWithValue("@name", name);
}
// 參數越多,組合爆炸指數級增長!

正確模式:動態拼接 + 參數化

string sql = "SELECT * FROM T WHERE 1=1";  // 關鍵技巧:永真條件
var parameters = new List();

if (id != null) {
    sql += " AND Id = @id";
    parameters.Add(new SqlParameter("@id", id));
}
if (!string.IsNullOrEmpty(name)) {
    sql += " AND Name = @name";
    parameters.Add(new SqlParameter("@name", name));
}

cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters.ToArray());

核心技巧解析

技巧作用注意事項
WHERE 1=1消除首個條件前是否需 AND 的判斷邏輯優化器會自動忽略常數條件,零效能影響
參數化 @param防止 SQL 注入、利用計畫快取嚴禁字串拼接數值:sql += " AND Id = " + id ❌
集合收集參數動態對應欄位,避免重複 AddWithValue建議明確指定 SqlDbType 避免型別推斷偏差
NULL 代表「未指定」符合資料庫三值邏輯,避免魔術數字 (如 0/-1) 污染業務邏輯前端傳遞 null 而非 0 或空字串

進階封裝:通用動態查詢輔助類

public class DynamicQueryBuilder
{
    private readonly StringBuilder _sql = new StringBuilder();
    private readonly List _params = new List();

    public DynamicQueryBuilder(string baseSql)
    {
        _sql.Append(baseSql);
        if (!baseSql.Contains("WHERE", StringComparison.OrdinalIgnoreCase))
            _sql.Append(" WHERE 1=1");
    }

    public DynamicQueryBuilder AddWhere(string column, object value, string op = "=")
    {
        if (value != null && !(value is string s && string.IsNullOrEmpty(s)))
        {
            string paramName = "@" + column + _params.Count;
            _sql.Append($" AND {column} {op} {paramName}");
            _params.Add(new SqlParameter(paramName, value));
        }
        return this;
    }

    public DynamicQueryBuilder AddIn(string column, IEnumerable values)
    {
        var vals = values?.Where(v => v != null).ToList();
        if (vals?.Count > 0)
        {
            var paramNames = vals.Select((v, i) => $"@{column}{i}").ToList();
            _sql.Append($" AND {column} IN ({string.Join(",", paramNames)})");
            for (int i = 0; i < vals.Count; i++)
                _params.Add(new SqlParameter(paramNames[i], vals[i]));
        }
        return this;
    }

    public (string Sql, SqlParameter[] Parameters) Build()
        => (_sql.ToString(), _params.ToArray());
}

使用範例

var builder = new DynamicQueryBuilder("SELECT * FROM Orders")
    .AddWhere("CustomerId", customerId)
    .AddWhere("Status", status)
    .AddIn("ProductId", productIds)
    .AddWhere("CreatedAt", startDate, ">=")
    .AddWhere("CreatedAt", endDate, "<=");

var (sql, params) = builder.Build();
using (var cmd = new SqlCommand(sql, conn)) {
    cmd.Parameters.AddRange(params);
    // ExecuteReader...
}

結語

WHERE 1=1 + 參數化集合 是動態 SQL 的黃金標準:
① 邏輯清晰、零排列組合;
② 參數化防注入、利用計畫快取;
③ 語意明確 (NULL = 未篩選);
④ 易於擴展 (IN、BETWEEN、LIKE 等運算子)。

copyright © 萬盛學電腦網 all rights reserved