Dynamic query and sql injects

Question

I have a stored procedure that receives a string parameter "OrderByColumn" and builds dynamic query accordingly.
This is the part of my stored procedure code:

ROW_NUMBER() OVER (ORDER BY 
  CASE WHEN @OrderByColumn='Date' AND @OrderDirection=0 THEN tbl_Docs.Date END ASC,
  CASE WHEN @OrderByColumn='Count' AND @OrderDirection=0 THEN tbl_Docs.Count END ASC,

And in my code behind function that calls the stores procedure I have:

cmd.Parameters.Add("@OrderByColumn", SqlDbType.NVarChar).Value = orderByColumn;
cmd.Parameters.Add("@OrderDirection", SqlDbType.Int).Value = orderDirection;

The user sets the OrderByColumn parameter by clicking on the gridviews column header, so there is no direct user input, so as I see there is no option to inject any thing...

In the book they also validate the orderByColumn string, I don't understand why it's needed because as I've noted the user can't input direct expression.

My question is:

is it safe?

I've also read in some book that ORDER BY clause doesn't support the use of parameters.
What does it mean?

Answer 1

Validation is not just confined to SqlInjection attacks, there could many other reason why one should validate input.

Parameters are treated similarly as in OVER clause, as constant and is ignored. so It is not basically the matter of support for parameters, but the results you get, which are unaffected by the use

Answer 2

This seems safe enough to use.

Im not completely following this section

I've also read in some book that ORDER BY clause doesn't support the use of parameters.

Do you mean the ORDER (ASC/DESC) or column?

If you are refering to the column, you can achieve this. Something like

DECLARE @Table TABLE(
        ID INT,
        Val INT
)

INSERT INTO @Table SELECT 1, 3
INSERT INTO @Table SELECT 2, 2
INSERT INTO @Table SELECT 3, 1

DECLARE @FieldNumber INT

SELECT @FieldNumber = 1

SELECT *
FROM    @Table
ORdER BY 
            CASE @FieldNumber 
                WHEN 1 THEN ID
                WHEN 2 THEN Val
            END

SELECT @FieldNumber = 2

SELECT *
FROM    @Table
ORdER BY 
            CASE @FieldNumber 
                WHEN 1 THEN ID
                WHEN 2 THEN Val
            END

Answer 3

I reckon it's safe as well... The contents of the parameter are not being directly included in the query being issued by the stored procedure - you are merely testing the param to decide what to put in the query yourself.

I think you can have a clear conscience...

Answer 4

is it safe?
Yes, that seems fine. The value is not being concatenated into an SQL string to then be executed, plus you are actually validating to explicitly checking for certain, valid values.

I've also read in some book that ORDER BY clause doesn't support the use of parameters. What does it mean?
That means you can't do (e.g.) "ORDER BY @SomeVariable" where @SomeVariable is the column to order by. Hence you need to use a CASE approach like you're doing.