Databases
StackOverflow2013
Postsdbo
POWhat are the dangers of dynamic SQL, and can they be avoided?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat are the dangers of dynamic SQL, and can they be avoided?
    primarykey
    data
    text
    <p>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!</p> <p>Any ideas on how to fix the injection attack?</p> <pre><code>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 &lt;&gt; 3 ' --PRINT('@sqlRowNum ='+@sqlRowNum) IF @searchStr &lt;&gt; '' 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 &lt;&gt; '' 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 &lt;&gt; '' AND @searchId &lt;&gt; 4 SET @sqlRowNumWhere = @sqlRowNumWhere + ' and ( Name like ''%' + @venueName + '%'' OR Address like ''%'+ @venueName+'%'' ) ' set @sqlRowNum = @sqlRowNum + ' ' + @sqlRowNumWhere --PRINT(@sqlRowNum) IF @selectedFeature &lt;&gt; '' BEGIN DECLARE @val1 varchar (255) Declare @SQLAttributes varchar(max) Set @SQLAttributes = '' Declare @tempAttribute varchar(max) Declare @AttrId int while (@selectedFeature &lt;&gt; '') 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 &lt;&gt; 1 set @sqlRowNum = @sqlRowNum + ' and dbo.GeocodeDistanceMiles(Latitude,Longitude,' + @latitude + ',' + @longitude + ') &lt;= ' + 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) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKevin Fairchild
    1. USdigiguru
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhat are the dangers of dynamic SQL, and can they be avoided?
      1. USdigiguru
      1. VTFavorite
    2. VO
      singulars
      1. POWhat are the dangers of dynamic SQL, and can they be avoided?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhat are the dangers of dynamic SQL, and can they be avoided?
      1. USsebthebert
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload