Hi Folks,

We all have at sometime worked on search and listing pages in our application. We often face situations where we need to search data by multiple values of same parameters (multi-value parameters). For example, we may need to search sales data by selecting multiple cities.
In such scenarios, we generally use dynamic SQL in backend to append Ids.

Example:
  1. DECLARE @SQL nvarchar(max)
  2. DECLARE @CityIds nvarchar(max)
  3. SET @SQL = 'SELECT CityId, (SalesAmount) '
  4. SET @SQL = @SQL + 'FROM Orders '
  5. SET @SQL = @SQL + 'WHERE CityId In (' + @CityIds + ') '
  6. SET @SQL = @SQL + 'GROUP BY CityId '
  7. EXEC (@SQL)

In such cases, if the parameter @CityIds is not handled properly, it may led to security concern like 'SQL INJECTION'.
To avoid this and similar issues, we can simple do the following:
  1. DECLARE @CityIds nvarchar(max)
  2. SET @CityIds = ',' + @CityIds + ','
  3. SELECT CityId, (SalesAmount)
  4. FROM Orders
  5. WHERE @CityIds = ',,' OR (@CityIds Like '%,' + Convert(nvarchar(10),CityId) + ',%')
  6. GROUP BY CityId
Both query will yield same results but the second method eliminates the security concern.

Attached is a example that you can execute and check in SQL.

Comments

Join the conversation! Your thoughts help the community grow.