动态SQL有什么危险，可以避免吗？

本文介绍了动态SQL有什么危险，可以避免吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我们刚刚获得了以下代码，作为离岸开发人员提供的新应用程序中复杂搜索查询的解决方案。我对使用动态SQL表示怀疑，因为我可以使用’来关闭SQL语句。然后激怒将要在数据库上执行的操作！

We've just been given the following code as a solution for a complicated search query in a new application provided by offshore developers. I'm skeptical of the use of dynamic SQL because I could close the SQL statement using '; and then excute a nasty that will be performed on the database!

关于如何解决注入攻击的任何想法？

Any ideas on how to fix the injection attack?

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)

推荐答案

不会使用 EXEC ；宁可使用 sp_executesql 。有关原因和其他信息，请参见这篇精湛的文章动态SQL的诅咒和祝福。使用动态SQL。

As an aside, I would not use EXEC; rather I would use sp_executesql. See this superb article, The Curse and Blessings of Dynamic SQL, for the reason and other info on using dynamic sql.