Какие опасности динамических SQL , и можно ли их избежать?

Мы только что был дан следующий код в качестве решения для сложного поиска в новых приложений, предоставляемых с помощью оффшорных разработчиков. Я скептически использования динамических 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)

Найдено 4 ответа:

What are the dangers of dynamic SQL, and can they be avoided?

http://stackoverflow.com/questions/268182/what-are-the-dangers-of-dynamic-sql-and-can-they-be-avoided

Посмотреть решение →