因SQL Server参数嗅探改用硬编码SQL，求防注入及参数化优化方案

针对你遇到的参数嗅探导致参数化查询性能拉胯，但硬编码又怕注入的问题，我给你分两部分梳理解决方案：先讲如何保留SqlParameter解决性能问题（优先推荐，毕竟规范），再讲硬编码时的防注入要点/类库。

一、保留SqlParameter的参数嗅探解决方案 #

你的查询性能问题核心是SQL Server缓存了某一组参数的执行计划，换参数时不匹配（比如某次传入的SKU列表特别长，生成的计划对短列表不友好，反之亦然）。以下几个方案可以解决：

1. 添加 OPTION (RECOMPILE) 查询提示 #

这是最直接的方案，让SQL Server每次执行查询时都重新生成适配当前参数的执行计划，彻底避免参数嗅探。修改你的查询：

SELECT SKU, Store, ColumnA, ColumnB, ColumnC 
FROM myTable 
WHERE (SKU IN (select * from splitString(@skus)) OR @skus IS NULL) 
AND (Store IN (select * from splitString(@stores)) OR @stores IS NULL)
OPTION (RECOMPILE) -- 加这一行

注意：每次编译会有少量开销，但对你这种参数化时性能从7秒掉到0-5秒的场景，这个开销完全可以忽略。

2. 用局部变量隔离参数 #

把传入的参数赋值给局部变量，SQL Server不会对局部变量做参数嗅探，会生成更通用的执行计划：

DECLARE @localSkus NVARCHAR(MAX) = @skus;
DECLARE @localStores NVARCHAR(MAX) = @stores;

SELECT SKU, Store, ColumnA, ColumnB, ColumnC 
FROM myTable 
WHERE (SKU IN (select * from splitString(@localSkus)) OR @localSkus IS NULL) 
AND (Store IN (select * from splitString(@localStores)) OR @localStores IS NULL)

这个方案没有编译开销，适合参数范围波动不大的场景。

3. 改用表值参数（TVPs）替代splitString函数 #

这是最优解——既解决参数嗅探，又干掉splitString函数的性能损耗。步骤如下：

第一步：在SQL Server创建自定义表类型

CREATE TYPE IdList AS TABLE (Id NVARCHAR(50)); -- 根据你的SKU/Store数据类型调整长度和类型

第二步：C#中传入表值参数

// 构造SKU列表的DataTable
DataTable skuTable = new DataTable();
skuTable.Columns.Add("Id", typeof(string));
foreach (var sku in skuList) // skuList是你要传入的SKU集合
{
    skuTable.Rows.Add(sku);
}

// 同理构造storeTable

// 创建SqlParameter
var skuParam = new SqlParameter("@tvpSkus", SqlDbType.Structured)
{
    TypeName = "IdList",
    Value = skuTable
};
var storeParam = new SqlParameter("@tvpStores", SqlDbType.Structured)
{
    TypeName = "IdList",
    Value = storeTable
};

第三步：修改查询语句

SELECT SKU, Store, ColumnA, ColumnB, ColumnC 
FROM myTable 
WHERE (EXISTS(SELECT 1 FROM @tvpSkus t WHERE t.Id = myTable.SKU) OR @tvpSkus IS NULL) 
AND (EXISTS(SELECT 1 FROM @tvpStores t WHERE t.Id = myTable.Store) OR @tvpStores IS NULL)

TVPs的性能远优于splitString函数，而且SQL Server能基于表值参数的实际数据生成精准的执行计划，参数嗅探的概率极低。

4. 计划指南（Plan Guides） #

如果以上方案都不适用，可以让DBA创建计划指南，强制SQL Server使用你验证过的高效执行计划。不过这个方案维护成本高，适合查询固定、参数范围明确的场景。

二、硬编码SQL防注入方案（迫不得已时用） #

如果一定要走硬编码，绝对不能直接拼接用户输入！以下是防注入的核心要点，也可以基于这些做自己的NuGet包：

1. 现成类库参考 #

• Dapper：虽然Dapper主打参数化，但它的DynamicParameters和安全拼接逻辑可以参考，不过如果是纯硬编码，Dapper也不推荐，但可以借鉴它的验证思路。
• 没有专门的“硬编码SQL防注入”类库，因为业界更推荐参数化，所以最好自己封装工具类。

2. 自定义防注入的核心要点 #

• 严格的白名单验证：
  • 对SKU/Store这类ID，先验证格式：比如用正则^[A-Z0-9-_]+$（根据你的实际ID规则调整），不符合的直接拒绝。
  • 如果ID是数据库中存在的值，先查询数据库验证每个ID是否存在，过滤掉不存在的ID（比如SELECT SKU FROM myTable WHERE SKU IN (...)，只保留返回的SKU）。
• 转义特殊字符：
  • 对SQL Server，把单引号'替换成双引号''，避免闭合字符串导致注入。但注意：转义只是辅助，不能替代白名单！
• 动态拼接时的逻辑安全：
  • 如果参数为空（比如SKU列表是空），不要拼接SKU IN ()（这会导致查询返回空），而是直接跳过该条件，和你原来的@skus IS NULL逻辑一致。
  • 拼接时用StringBuilder，避免字符串拼接的性能问题，同时每一步都做验证。
• 禁止拼接用户输入的原始字符串：
  • 绝对不能做sql += "SKU IN (" + userInput + ")"，必须把用户输入的每个ID单独验证后再拼接。

比如一个简单的安全拼接示例：

public static string BuildSafeInClause(string columnName, IEnumerable<string> values, Regex validationRegex)
{
    var validValues = values.Where(v => validationRegex.IsMatch(v)).Select(v => $"'{v.Replace("'", "''")}'");
    if (!validValues.Any())
    {
        return "1=1"; // 对应原来的@参数IS NULL逻辑，跳过该条件
    }
    return $"{columnName} IN ({string.Join(",", validValues)})";
}

使用时：

var skuClause = BuildSafeInClause("SKU", skuList, new Regex(@"^[A-Z0-9]+$"));
var storeClause = BuildSafeInClause("Store", storeList, new Regex(@"^\d+$"));
var sql = $"SELECT SKU, Store, ColumnA, ColumnB, ColumnC FROM myTable WHERE {skuClause} AND {storeClause}";

内容的提问来源于stack exchange，提问作者Gabriel Espinoza