时间戳

首页 > 解决方案 > 存储过程 SQL 注入检查

sql-server - 存储过程 SQL 注入检查

问题描述

我编写了以下用于查询数据库的存储过程。有人能告诉我这个动态查询存储过程是否容易受到 SQL 注入攻击吗？

如果是，如何修改以下代码来防止SQL注入攻击？

第二个问题是OPTION (RECOMPILE)在WHERE原因的最后，每次执行都有必要吗？

CREATE PROCEDURE DataMapMainQuery
    (@DataMapID VARCHAR(MAX),
     @DataMapIDName VARCHAR(MAX),
     @StartIndex INT,
     @MaximumRows INT,
     @sortExpression VARCHAR(MAX))
AS
BEGIN   
    DECLARE @FilteredTotalRows AS INT
    DECLARE @SqlString NVARCHAR(MAX)
    DECLARE @WhereString1 NVARCHAR(MAX)
    DECLARE @WhereString2 NVARCHAR(MAX)

    IF (@DataMapID IS NULL)
        SET @WhereString1 = ' AND (DataMapID LIKE ' + '''%%''' + ' OR NULL IS NULL)'
    ELSE
        SET @WhereString1 = ' AND (DataMapID LIKE ' + '''%' + @DataMapID + '%''' + ' OR ''' + @DataMapID + ''' IS NULL)'

    IF (@DataMapIDName IS NULL)
        SET @WhereString2 = ' AND (DataMapIDName LIKE ' + '''%%''' + ' OR NULL IS NULL)'
    ELSE
        SET @WhereString2 = ' AND (DataMapIDName LIKE ' + '''%' + @DataMapIDName + '%''' + ' OR ''' + @DataMapIDName + ''' IS NULL)'

    IF (@sortExpression IS NULL)
        SET @sortExpression = 'DataMapID'

    SELECT
        @FilteredTotalRows = COUNT(*) 
    FROM
        DataMapMain
    WHERE
        1 = 1
        AND (DataMapID LIKE '%' + @DataMapID + '%' OR @DataMapID IS NULL)
        AND (DataMapIDName LIKE '%' + @DataMapIDName + '%' OR @DataMapIDName IS NULL)

    IF (@FilteredTotalRows < @StartIndex + 1)
    BEGIN
        SET @SqlString = '
                SELECT 
                    DataMapID, DataMapIDName,
                    DataMapGroup, DataMapGroupRemark,
                    CONVERT(BIGINT, TimeStamp) AS TimeStamp
                FROM 
                    (SELECT
                         ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowNumber,
                         DataMapID, DataMapIDName,
                         DataMapGroup, DataMapGroupRemark,
                         TimeStamp
                     FROM
                         DataMapMain
                     WHERE
                         1 = 1' 
                    + @WhereString1
                    + @WhereString2
                    + ') DataMapMain
                WHERE
                     RowNumber >= 1 
                     AND RowNumber < (1 + ' + CONVERT(NVARCHAR(10), @MaximumRows) + ') 
                OPTION (RECOMPILE)'
    END
    ELSE
    BEGIN
        SET @SqlString = '
                SELECT 
                    DataMapID
                    ,DataMapIDName
                    ,DataMapGroup
                    ,DataMapGroupRemark
                    ,CONVERT(bigint, TimeStamp) as TimeStamp
                FROM 
                     (
                     Select ROW_NUMBER() over (order by '  + @sortExpression + ') as RowNumber
                    ,DataMapID
                    ,DataMapIDName
                    ,DataMapGroup
                    ,DataMapGroupRemark
                    ,TimeStamp
                     From DataMapMain
                     WHERE
                        1 = 1'
                    + @WhereString1
                    + @WhereString2

                        + ') DataMapMain
                WHERE
                    RowNumber >= (' +  CONVERT(nvarchar(10),@StartIndex) + ' + 1) and RowNumber < (' + CONVERT(nvarchar(10),@StartIndex) + ' + 1 + ' + CONVERT(nvarchar(10),@MaximumRows) + ' )
                    OPTION (RECOMPILE)'
            END 
            PRINT @SqlString
            PRINT @FilteredTotalRows
        EXEC sp_executesql @SqlString
    END

标签： sql-servertsqlstored-proceduressql-injection

解决方案

只需sp_executesql与参数一起使用。构建您的动态 T-SQL 语句，而不是增值@parameter_name。然后像这样调用例程：

EXEC sp_executesql @sql
                  ,N'@parameter_name1 INT, @parameter_name2 VARCHAR(128), @parameter_name3 BIT'
                  ,@parameter_name1, @parameter_name2, @parameter_name3;

推荐阅读