当 t-sql 动态代码作为筛选器使用时，保护 t-sql 动态代码的最佳方法是什么sp

问：

我正在制作一个接受动态过滤的存储过程，我遇到的问题是我需要尽可能保持它的灵活性。

ALTER PROCEDURE astp_test
    @WhereClause NVARCHAR(max) = NULL
AS

DECLARE @FilteredResults AS TABLE (testId int, testfield datetime2)
DECLARE @sql AS NVARCHAR(MAX) = N'SELECT testId ,                               testfield 
                                        FROM aviw_test
                                        WHERE IsOpen = 1 AND IsLatesInsert = 1
                                            AND testStepNo = 7
                                            AND test2 IS NULL
                                            AND (testfielddate IS NULL OR testfielddate2 < GETUTCDATE()) 
                                            AND Domain IN (SELECT Domain FROM project WITH (NOLOCK) WHERE Status = ''Active'')' + 
    CASE WHEN @WhereClause IS NOT NULL 
        THEN  N' AND ' + @WhereClause ELSE N''
    END

INSERT INTO @FilteredResults
    EXEC sys.sp_executesql @stmt = @sql;

我想保护输入，但以这种方式，因为有一些复选框会发送如下内容： .那么最好的方法是什么呢？@WhereClause"AND testDatePick = '2019-10-10' AND testStage = 'InProgress' AND testArea = 'London' "

sql-server t-sql 注入动态 sql

最好的方法是不使用动态 sql。包含什么？为什么不使用 ORM 来生成一个好的 SQL 查询呢？事实上，为什么不将其转换为视图并在其上编写一个简单的查询呢？PS 不会使慢速查询运行得更快，它会在获取额外锁的同时读取脏数据。若要使此子查询运行得更快，请添加索引，并可能使用 SNAPSHOT 隔离@WhereClause@sqlWITH (NOLOCK)Status, Domain

0赞 Thom A 10/15/2019

我不同意这一点，@PanagiotisKanavos.然而，最好的方法是安全地注入对象名称（使用）并参数化语句。不过，注射是完全错误的想法，因为你永远无法确保安全。QUOTENAMEWHERE

2赞 Jeroen Mostert 10/15/2019

如果你要接受原始 SQL，那么没有很好的方法可以防止任何事情;必须信任客户端，因为在 T-SQL 中分析 T-SQL 是不可取的。在这种情况下，最好将查询逻辑完全集中在客户端中，这样你就可以在一个地方验证它，并为查询中不会更改的部分声明一个视图。我还建议阅读本文，了解有关进行动态搜索的不同、更安全方法（以及与每种方法相关的陷阱）的大量背景信息。

1赞 Thom A 10/15/2019

我不同意笼统的说法“最好的方法是不使用动态sql。 @PanagiotisKanavos。该评论已被编辑以扩展，但肯定说只是说“动态SQL不好”是错误的。写得不好的动态 SQL 很糟糕，但写得好、安全的动态 SQL 就可以了;当然，它的用途也有的时候。

1赞 Thom A 10/15/2019

要“修复”（保护）它，@pixe您需要删除该参数，并将其替换为需要比较的每列 1 个参数。这将需要对数据库和应用程序进行更改。这是一项要求。如果不这样做，就无法确保它的安全。如果你不能这样做，我强烈建议你删除该功能。@WhereClause

答：

-1赞 Madhu 10/15/2019 #1

请尝试以下操作

    ALTER PROCEDURE astp_test
        @WhereClause NVARCHAR(max) = NULL
    AS

    DECLARE @FilteredResults AS TABLE (testId int, testfield datetime2)
    DECLARE @sql AS NVARCHAR(MAX) = N'SELECT testId ,                               testfield 
                                            FROM aviw_test
                                            WHERE IsOpen = 1 AND IsLatesInsert = 1
                                                AND testStepNo = 7
                                                AND test2 IS NULL
                                                AND (testfielddate IS NULL OR testfielddate2 < GETUTCDATE()) 
                                                AND Domain IN (SELECT Domain FROM project WITH (NOLOCK) WHERE Status = ''Active'')' 

if @WhereClause is not null
set @sql=@sql + 'AND ' + @WhereClause

    INSERT INTO @FilteredResults
        EXEC sys.sp_executesql @stmt = @sql;

如果您有任何问题，请告诉我

谢谢

上一个：保护动态 SQL 免受 SQL 注入的影响

下一个：SP_EXECUTESQL中的 SQL 注入

当 t-sql 动态代码作为筛选器使用时，保护 t-sql 动态代码的最佳方法是什么sp_executesql

What is best way to secure t-sql dynamic code when it comes as filters using sp_executesql

评论