动态 SQL 有哪些危险以及可以避免吗？

Question

我们刚刚收到以下代码，作为离岸开发商提供的新应用程序中复杂搜索查询的解决方案。 我对动态 SQL 的使用持怀疑态度，因为我可以使用 '; 关闭 SQL 语句。 然后执行一个将在数据库上执行的令人讨厌的操作！

关于如何修复注入攻击有什么想法吗？

ALTER procedure [dbo].[SearchVenues] --'','',10,1,1,''
@selectedFeature as varchar(MAX),
@searchStr as varchar(100),
@pageCount as int,
@startIndex as int,
@searchId as int,
@venueName as varchar(100),
@range int,
@latitude varchar(100),
@longitude varchar(100),
@showAll int,
@OrderBy varchar(50),
@SearchOrder varchar(10)

AS
DECLARE @sqlRowNum as varchar(max)
DECLARE @sqlRowNumWhere as varchar(max) 
DECLARE @withFunction as varchar(max)
DECLARE @withFunction1 as varchar(max)
DECLARE @endIndex as int
SET  @endIndex = @startIndex + @pageCount -1

SET @sqlRowNum = ' SELECT Row_Number() OVER (ORDER BY '

IF @OrderBy = 'Distance'
    SET @sqlRowNum =  @sqlRowNum  + 'dbo.GeocodeDistanceMiles(Latitude,Longitude,' + @latitude + ',' + @longitude + ') ' +@SearchOrder
ELSE
    SET @sqlRowNum =  @sqlRowNum + @OrderBy + ' '+ @SearchOrder

SET @sqlRowNum = @sqlRowNum + ' ) AS RowNumber,ID,RecordId,EliteStatus,Name,Description,
Address,TotalReviews,AverageFacilityRating,AverageServiceRating,Address1,Address2,Address3,Address4,Address5,Address6,PhoneNumber,
visitCount,referalCount,requestCount,imgUrl,Latitude,Longitude,
Convert(decimal(10,2),dbo.GeocodeDistanceMiles(Latitude,Longitude,' + @latitude + ',' + @longitude + ')) as distance
FROM VenueAllData '

SET @sqlRowNumWhere = 'where Enabled=1 and EliteStatus <> 3 ' 

--PRINT('@sqlRowNum ='+@sqlRowNum)
IF  @searchStr <> ''
BEGIN

    IF (@searchId = 1)    -- county search
    BEGIN
       SET @sqlRowNumWhere  = @sqlRowNumWhere +  ' and Address5 like ''' + @searchStr + '%'''
    END
    ELSE IF(@searchId = 2  ) -- Town search
    BEGIN
       SET @sqlRowNumWhere  = @sqlRowNumWhere +  ' and Address4 like ''' + @searchStr + '%'''
    END  
    ELSE IF(@searchId = 3  ) -- postcode search
    BEGIN
       SET @sqlRowNumWhere  = @sqlRowNumWhere +  ' and Address6 like ''' + @searchStr + '%'''
    END    

    IF (@searchId = 4)   -- Search By Name
    BEGIN
        IF @venueName <> ''
            SET @sqlRowNumWhere  = @sqlRowNumWhere +  ' and ( Name like ''%' + @venueName + '%'' OR Address like ''%'+ @venueName+'%'' ) '
        ELSE
            SET @sqlRowNumWhere  = @sqlRowNumWhere +  ' and  ( Name like ''%' + @searchStr + '%'' OR Address like ''%'+ @searchStr+'%'' ) '
    END
END


IF @venueName <> '' AND @searchId <> 4
    SET @sqlRowNumWhere  = @sqlRowNumWhere +  ' and ( Name like ''%' + @venueName + '%'' OR  Address like ''%'+ @venueName+'%'' ) '


set @sqlRowNum = @sqlRowNum +  ' '   + @sqlRowNumWhere 


--PRINT(@sqlRowNum)

IF @selectedFeature <> ''
    BEGIN
        DECLARE @val1 varchar (255)
        Declare @SQLAttributes varchar(max)
        Set @SQLAttributes = ''
        Declare @tempAttribute varchar(max)
        Declare @AttrId int
        while (@selectedFeature <> '')
            BEGIN
                SET @AttrId = CAST(SUBSTRING(@selectedFeature,1,CHARINDEX(',',@selectedFeature)-1) AS Int)
                Select @tempAttribute = ColumnName from Attribute where id = @AttrId
                SET @selectedFeature = SUBSTRING(@selectedFeature,len(@AttrId)+2,len(@selectedFeature))
                SET @SQLAttributes = @SQLAttributes + ' ' + @tempAttribute + ' = 1 And '
            END
        Set @SQLAttributes = SUBSTRING(@SQLAttributes,0,LEN(@SQLAttributes)-3)
        set @sqlRowNum = @sqlRowNum +  ' and ID in  (Select VenueId from '
        set @sqlRowNum = @sqlRowNum +  ' CachedVenueAttributes WHERE ' + @SQLAttributes + ')  '

    END

IF @showAll <> 1
    set @sqlRowNum = @sqlRowNum +  ' and  dbo.GeocodeDistanceMiles(Latitude,Longitude,' + @latitude + ',' + @longitude + ')   <=  ' +  convert(varchar,@range )


set @withFunction = 'WITH LogEntries AS (' + @sqlRowNum +  ')

SELECT * FROM  LogEntries WHERE RowNumber between '+ Convert(varchar,@startIndex) + 
' and ' + Convert(varchar,@endIndex) + ' ORDER BY ' + @OrderBy + ' ' + @SearchOrder


print(@withFunction)
exec(@withFunction)

Answer 1

顺便说一句，我不会使用 EXEC； 相反，我会使用 sp_executesql。 请参阅这篇精彩的文章，动态 SQL 的诅咒和祝福，了解有关使用的原因和其他信息动态sql。

Answer 2

请参阅此答案。

另外，这些：

我对 SQL 注入免疫吗如果我使用存储过程？

使用参数使用 Like 运算符避免 SQL 查询中的 SQL 注入？

我可以通过转义单引号并用单引号包围用户输入来防止 SQL 注入吗？

Answer 3

这是上面查询的优化版本，不使用动态 SQL...

Declare @selectedFeature as varchar(MAX),
@searchStr as varchar(100),
@pageCount as int,
@startIndex as int,
@searchId as int,
@venueName as varchar(100),
@range int,
@latitude varchar(100),
@longitude varchar(100),
@showAll int,
@OrderBy varchar(50),
@SearchOrder varchar(10)

Set @startIndex = 1
Set @pageCount = 50



Set @searchStr = 'e'
Set @searchId = 4
Set @OrderBy = 'Address1'
Set @showAll = 1
--Select dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude)


DECLARE @endIndex int
SET  @endIndex = @startIndex + @pageCount -1
;

WITH LogEntries as (
SELECT 
    Row_Number() 
        OVER (ORDER BY 
            CASE @OrderBy
               WHEN 'Distance' THEN Cast(dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude) as varchar(10))
               WHEN 'Name' THEN Name
               WHEN 'Address1' THEN Address1
               WHEN 'RecordId' THEN Cast(RecordId as varchar(10))
               WHEN 'EliteStatus' THEN Cast(EliteStatus as varchar(10))
            END) AS RowNumber,
RecordId,EliteStatus,Name,Description,
Address,TotalReviews,AverageFacilityRating,AverageServiceRating,Address1,Address2,Address3,Address4,Address5,Address6,PhoneNumber,
visitCount,referalCount,requestCount,imgUrl,Latitude,Longitude,
Convert(decimal(10,2),dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude)) as distance
FROM VenueAllData 
where Enabled=1 and EliteStatus <> 3
And 
    (
        (Address5 like @searchStr + '%' And @searchId = 1) OR
        (Address4 like @searchStr + '%' And @searchId = 2) OR
        (Address6 like @searchStr + '%' And @searchId = 3) OR
        (
            (
                @searchId = 4 And 
                    (Name like '%' + @venueName + '%' OR Address like '%'+ @searchStr+'%')
            )
        )
    )
And
    ID in (
        Select VenueID 
        From CachedVenueAttributes 
        --Extra Where Clause for the processing of VenueAttributes using @selectedFeature
    )
And
    (   
        (@showAll = 1) Or
        (@showAll <> 1 and dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude) <= convert(varchar,@range )) 
    )
)

SELECT * FROM  LogEntries 
WHERE RowNumber between @startIndex and @endIndex 
ORDER BY CASE @OrderBy
               WHEN 'Distance' THEN Cast(Distance as varchar(10))
               WHEN 'Name' THEN Name
               WHEN 'Address1' THEN Address1
               WHEN 'RecordId' THEN Cast(RecordId as varchar(10))
               WHEN 'EliteStatus' THEN Cast(EliteStatus as varchar(10))
            END

我唯一没有修复的是 CachedVenueAttributes 中的选择，它似乎在循环中构建了一个 where 语句。 我想我可以将其放入表值函数中，并将其重构为与过程的其余部分隔离。

Answer 4

我喜欢动态 SQL 进行搜索。

在我过去使用它的地方，我使用了 .Net 准备好的语句，其中任何用户生成的字符串都作为参数传入，而不是作为 SQL 中的文本包含在内。

要使用现有解决方案运行，您可以采取多种措施来降低风险。

1. 白名单输入，验证输入，使其只能包含 a-zA-Z0-9\w （字母数字和空格）（如果需要支持 unicode 字符则不好）
2. 以受限用户身份执行任何动态 sql。 将存储过程的所有者设置为仅对相关表具有读取权限的用户。 拒绝写入所有表等。 此外，在调用此存储过程时，您可能需要对用户执行类似的操作限制，因为 MS-SQL 在存储过程中执行动态 sql 作为调用用户而不是存储过程的所有者。

Answer 5

我意识到这是一篇非常旧的帖子，但是当做这样的事情时：

    AND
    (   
        (@showAll = 1) 
        OR (@showAll <> 1 
            AND dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude) <= convert(varchar,@range)) 
    )

... OPTION(RECOMPILE) 通常会帮助选择一个更简洁的计划，只要它不会每秒执行一千次或任何东西。