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:
- DECLARE @SQL nvarchar(max)
- DECLARE @CityIds nvarchar(max)
- SET @SQL = 'SELECT CityId, (SalesAmount) '
- SET @SQL = @SQL + 'FROM Orders '
- SET @SQL = @SQL + 'WHERE CityId In (' + @CityIds + ') '
- SET @SQL = @SQL + 'GROUP BY CityId '
- 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:
- DECLARE @CityIds nvarchar(max)
- SET @CityIds = ',' + @CityIds + ','
- SELECT CityId, (SalesAmount)
- FROM Orders
- WHERE @CityIds = ',,' OR (@CityIds Like '%,' + Convert(nvarchar(10),CityId) + ',%')
- 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.