CODEWITHSUNDEEP
c#sqlsql-server
Mikyas Gami
Mikyas Gami

Reputation: 35

Can you only parameterize the where clause?

To prevent sql injection, I am trying to use parameterized queries. But it is not clear to me whether I should only paramterize the where clause or other parts of the query. For example, I am trying to improve the following query:

string strQ = @";WITH lstTable as (
                SELECT ROW_NUMBER() OVER(ORDER BY " + sort + @") AS RowNum, *
                FROM (
                SELECT *
                FROM SystemMessage
                WHERE Deleted = 0  ";`

This query is being used in grid and based on user's selection, it will sort by the column name. Do I needed paramaterize 'sort' in this scenario?

Upvotes: 1

Views: 505

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127603

You are correct that you can't use parameters directly for this use case. However the fact if you need to use parameters or not depends on how sort gets populated.

If it is a list of column names that are hard coded and the user just picks which indexes in the list are going to be chosen you don't need to worry about parameterizing, the user does not have direct input in the query so they can't inject code in to it.

If the user is providing the column names directly you must clean up the user input before passing it in, a way to do that is use the sql function QUOTENAME to clean up the input.

string strQ = @"
declare @query nvarchar(max)
set @query = ';WITH lstTable as (
                SELECT ROW_NUMBER() OVER(ORDER BY' + QUOTENAME(@sortColumn) + ') AS RowNum, *
                FROM (
                SELECT *
                FROM SystemMessage
                WHERE Deleted = 0 '
exec sp_executesql @query";

What that will do is whatever string you pass in to @sortColumn it will properly wrap [ ] around that string value. It then uses that excaped value in the dynamicly generated string and runs that with sp_executesql.

One important note, this example only works with a single column name currently, you would need a QUOTENAME and a new parameter per column you wanted to add in to the query. If you did try passing in FirstName, LastName it would become 

;WITH lstTable as (
   SELECT ROW_NUMBER() OVER(ORDER BY [FirstName, LastName]) AS RowNum, *
       FROM (
       SELECT *
       FROM SystemMessage
       WHERE Deleted = 0

when executed so it would attempt to find a column named "[FirstName, LastName]" and would fail.

Upvotes: 4

Related Questions