如何让动态SQL搜索避免SQL注入？以searchEvents存储过程为例

嘿，你完全找对了问题所在——直接拼接用户输入到动态SQL语句里就是SQL注入的根源，REPLACE这类方法只能处理个别特殊情况，没法从根本上杜绝风险。真正的解决方案是利用sp_executesql的参数化能力，让SQL Server帮你安全处理所有用户可控的变量，完全避免字符串拼接带来的隐患。

问题分析 #

你原来的代码把@name直接拼进了@sqlCommand，如果用户输入类似' OR 1=1--这样的恶意内容，最终生成的SQL会变成：

SELECT ... WHERE Event.Name LIKE '%' OR 1=1--%'

这会直接返回所有事件数据，甚至可能被利用做更危险的操作。

修改后的安全存储过程 #

下面是修复后的代码，核心是所有用户参数都通过sp_executesql的参数列表传递，不再直接拼接进SQL语句：

CREATE PROCEDURE searchEvents 
    @name VARCHAR(50), 
    @location VARCHAR(20), 
    @postcode CHAR(4), 
    @address VARCHAR(40), 
    @startDate DATETIME, 
    @endDate DATETIME 
AS 
DECLARE 
    @sqlCommand NVARCHAR(MAX) = '
        SELECT 
            Event.Name, 
            Description, 
            Location.Name AS Location, 
            Postcode, 
            Address, 
            StartDate, 
            EndDate, 
            Website 
        FROM Event 
        JOIN Location ON Event.LocationID = Location.LocationID',
    @whereClauses NVARCHAR(MAX) = '',
    @parameters NVARCHAR(MAX) = '@p_name VARCHAR(50), @p_location VARCHAR(20), @p_postcode CHAR(4), @p_address VARCHAR(40), @p_startDate DATETIME, @p_endDate DATETIME'

BEGIN
    -- 构建WHERE子句，只添加参数占位符，不拼接实际用户输入
    IF @name IS NOT NULL
        SET @whereClauses += CASE WHEN @whereClauses <> '' THEN ' AND ' ELSE '' END + 'Event.Name LIKE ''%'' + @p_name + ''%'''

    IF @location IS NOT NULL
        SET @whereClauses += CASE WHEN @whereClauses <> '' THEN ' AND ' ELSE '' END + 'Location.Name = @p_location'

    IF @postcode IS NOT NULL
        SET @whereClauses += CASE WHEN @whereClauses <> '' THEN ' AND ' ELSE '' END + 'Location.Postcode = @p_postcode'

    IF @address IS NOT NULL
        SET @whereClauses += CASE WHEN @whereClauses <> '' THEN ' AND ' ELSE '' END + 'Location.Address LIKE ''%'' + @p_address + ''%'''

    IF @startDate IS NOT NULL
        SET @whereClauses += CASE WHEN @whereClauses <> '' THEN ' AND ' ELSE '' END + 'Event.StartDate >= @p_startDate'

    IF @endDate IS NOT NULL
        SET @whereClauses += CASE WHEN @whereClauses <> '' THEN ' AND ' ELSE '' END + 'Event.EndDate <= @p_endDate'

    -- 如果有WHERE条件，添加到主SQL语句
    IF @whereClauses <> ''
        SET @sqlCommand += ' WHERE ' + @whereClauses

    -- 执行参数化的动态SQL
    EXEC sp_executesql 
        @sqlCommand, 
        @parameters, 
        @p_name = @name, 
        @p_location = @location, 
        @p_postcode = @postcode, 
        @p_address = @address, 
        @p_startDate = @startDate, 
        @p_endDate = @endDate
END

关键改进点 #

• 彻底杜绝注入风险：所有用户参数都通过sp_executesql的参数传递，SQL Server会自动处理参数的转义，完全避免恶意输入篡改SQL逻辑。
• 安全处理模糊查询：把%通配符放在动态SQL的占位符周围（比如''%'' + @p_name + ''%''），既保留了模糊查询的功能，又不会引入注入风险。
• 代码更易维护：用@whereClauses变量统一管理查询条件，逻辑更清晰，后续添加新参数也更方便。

另一种可选实现方式 #

如果你更倾向于在参数层面处理通配符，也可以这样写：

-- 构建WHERE子句时直接使用纯占位符
IF @name IS NOT NULL
    SET @whereClauses += CASE WHEN @whereClauses <> '' THEN ' AND ' ELSE '' END + 'Event.Name LIKE @p_name'

-- 调用sp_executesql时传递带通配符的参数值
EXEC sp_executesql 
    @sqlCommand, 
    @parameters, 
    @p_name = '%' + @name + '%',  -- 在这里添加通配符
    @p_location = @location, 
    @p_postcode = @postcode, 
    @p_address = @address, 
    @p_startDate = @startDate, 
    @p_endDate = @endDate

这种方式同样安全，因为通配符是作为参数值的一部分传递，而不是直接拼接进SQL语句本身。

两种方式都能彻底解决SQL注入问题，你可以根据自己的编码习惯选择。

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