CODEWITHSUNDEEP
.nett-sqlc#-4.0
Bengal
Bengal

Reputation: 329

Parametrized database query issue

I have created the query listed below. It works when I run it. 

string sortDir = (this.GridSortDirection == SortDirection.Descending ? " DESC" : " ASC");
int startIndex = 1;
int endIndex = this.gvData.PageSize;

SELECT RowNum, [ID], [Name], [Description], [DisplayIndex], [Status] 
FROM
(
    SELECT  [ID], [Name], [Description], [DisplayIndex], CASE WHEN Status = 1 THEN 'Active' ELSE 'Disabled' END AS [Status] ,
    ROW_NUMBER() OVER(ORDER BY [" + this.GridSortExpression + "]" + " " + sortDir + @")as 'RowNum'
    FROM  [MyDatabase].[dbo].[t_MyTable] s

) as Info
WHERE RowNum BETWEEN " + startIndex.ToString() + " AND " + endIndex.ToString()

I attempted to restructure it to parametrized query format as shown below, but am getting an error when it runs. The error states that there is a syntax error near sortDir.

string sql = @"SELECT RowNum, [ID], [Name], [Description], [DisplayIndex], [Status] 
FROM
(
    SELECT  [ID], [Name], [Description], [DisplayIndex], CASE WHEN Status = 1 THEN 'Active' ELSE 'Disabled' END AS [Status] ,
    ROW_NUMBER() OVER(ORDER BY @SortExpression @SortDir)as 'RowNum'
    FROM  [MyDatabase].[dbo].[t_MyTable] s

) as Info
WHERE RowNum BETWEEN @startIndex AND @endIndex";

cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@SortExpression", this.GridSortExpression);   
cmd.Parameters.AddWithValue("@SortDir", sortDir);                 
cmd.Parameters.AddWithValue("@startIndex", startIndex);
cmd.Parameters.AddWithValue("@endIndex", endIndex);
da = new SqlDataAdapter(cmd);
da.Fill(dt);

I also tried the following to no avail... same error message

cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@SortExpression", this.GridSortExpression + " " + sortDir);                    
cmd.Parameters.AddWithValue("@startIndex", startIndex);
cmd.Parameters.AddWithValue("@endIndex", endIndex);
da = new SqlDataAdapter(cmd);
da.Fill(dt);

Can anyone refer me to my error?

Many thanks in advance

Upvotes: 1

Views: 122

Answers (2)

mrmillsy
mrmillsy

Reputation: 495

It is possible to do conditional sorting using a CASE statement.

For example:

ORDER BY
     CASE
         WHEN @SortCol = 'a' THEN colA
         WHEN @SortCol = 'b' THEN colB
     END DESC

You can further extend this to sort by the direction.

ORDER BY
    CASE WHEN @SortCol = 'a' AND @SortDir = 'ASC' THEN ColA END ASC
    ,CASE WHEN @SortCol = 'a' AND @SortDir = 'DESC' THEN ColA END DESC
    ,CASE WHEN @SortCol = 'b' AND @SortDir = 'ASC' THEN ColB END ASC
    ,CASE WHEN @SortCol = 'b' AND @SortDir = 'DESC' THEN ColB END DESC

The last time I used this, I had a @KeyDataColumn which I wanted to sort on, group by, and return in the result set. Rather than repeat this logic 3 times, I found it much more convenient, readable and extendible to have the logic encapsulated in a sub query.

SELECT
    keyData.KeyDataColumn
    ,COUNT(base.*) AS KeyDataCount
FROM baseTable base
    CROSS APPLY (
        SELECT
            CASE
                WHEN @KeyDataColumn = 'NextEvolution' THEN base.NextEvolution
                WHEN @KeyDataColumn = 'TimesCaptured' THEN base.TimesCaptured
                WHEN @KeyDataColumn = 'BattlesWon' THEN base.BattlesWon
            END AS KeyDataColumn
    ) keyData
GROUP BY keyData.KeyDataColumn
ORDER BY
    CASE WHEN @SortDir = 'ASC' THEN keyData.KeyDataColumn END ASC
    ,CASE WHEN @SortDir = 'DESC' THEN keyData.KeyDataColumn END DESC

Upvotes: 1

Christian Hayter
Christian Hayter

Reputation: 31071

@SortExpression and @SortDir cannot be parameterized. They are bits of T-SQL syntax, not values. You will have to leave them in the command text string. By contrast @startIndex and @endIndex are values and so will work fine as parameters.

If you are worried about SQL injection, then I'm afraid that you will have to write your own code to validate the contents of this.GridSortExpression and sortDir. For example:

  • You could check that sortDir equals either "asc" or "desc" and reject anything else.
  • You could parse out the list of column names from this.GridSortExpression, then wrap each one in square brackets before putting the list back together.

Upvotes: 4

Related Questions