Syntax error in SQL Server while making Dynamic SQL

Question

Scenario:

I have a table with 100M+ rows of data so I have created an index with something as:

CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt  
    ON dbo.base_voter (name_voter,home_street_address_1, home_address_city)  
    WHERE deleted_at IS NULL ; 

Now when I execute the following code, it executed in less time:

SELECT id,
name_voter,
home_street_address_1,
home_address_city
FROM dbo.base_voter
WITH(NOLOCK)
WHERE deleted_at IS NULL
order by name_voter asc
OFFSET 0 ROWS 
FETCH NEXT 50 ROWS ONLY 

Now, I want to create this query as dynamic SQL as these parts name_voter asc, 0 , 50 need to be dynamic and will be sent through the backend.

But, when I try to execute

DECLARE @sql nvarchar(MAX);  
DECLARE @params nvarchar(1000);  

SET @sql =  
     N'SELECT id, name_voter, home_street_address_1,home_address_city  
       FROM dbo.base_voter 
       WITH(NOLOCK)
       ORDER BY @sort_col @sort_dir
       OFFSET @offset ROWS
       FETCH NEXT @limit ROWS ONLY';  
SET @params = N'@sort_col nvarchar(100),@sort_dir nvarchar(10), @offset int, @limit int';


EXECUTE sp_executesql @sql, @params,  
                      'name_voter','asc', 0, 50;

I receive error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@sort_dir'.
Msg 153, Level 15, State 2, Line 9
Invalid usage of the option NEXT in the FETCH statement.

I am not sure but is it because I am passing column name as a parameter? Or is there some other thing. I just want that query to be executed while preventing from SQL injection and with minimal execution time.

I will be here if you need any further information.

Answer 1

@marcello has the syntax right, but another thing, to prevent SQL injection you can check the values of @sort_col and @sort_dir (since I assume those are coming from the user). The direction is easy:

IF LOWER(@sort_dir) NOT IN (N'asc', N'desc')
BEGIN
  RAISERROR(N'Invalid direction', 16, 1);
  RETURN;
END

Column names are a little more involved. You can just use constants to limit to the output columns from the query:

IF LOWER(@sort_col) NOT IN 
(
  N'id', 
  N'name_voter', 
  N'home_street_address_1', 
  N'home_address_city'
)
BEGIN
  RAISERROR(N'Invalid column', 16, 1);
  RETURN;
END

Or you can make sure the column at least exists in the table, which would allow you to change the columns in the query without having to change the NOT IN list, and also to allow sorting by a column not present in the output of the query (even if that might not always make sense):

IF NOT EXISTS
( 
  SELECT 1 
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(N'dbo.base_voter')
      AND LOWER(name) = LOWER(@sort_col)
)
BEGIN
  RAISERROR('Invalid column', 16, 1);
  RETURN;
END

I wrote about this and other techniques in a two-part series:

• Protecting Yourself from SQL Injection in SQL Server - Part 1
• Protecting Yourself from SQL Injection in SQL Server - Part 2

Answer 2

from the comments of @sepupic and @Scott Hodgin, I put this together:

conditional order by

DECLARE @sql nvarchar(MAX);  
DECLARE @params nvarchar(100);  
declare @sort_col nvarchar(20) = 'name_voter'
declare @sort_dir nvarchar(20) = 'asc'

SET @sql =  
     N'SELECT id, name_voter, home_street_address_1,home_address_city  
       FROM dbo.base_voter 
       WITH(NOLOCK)' + NCHAR(10) +
      N'ORDER BY ' + @sort_col + SPACE(2) + @sort_dir + NCHAR(10) + 
      N'OFFSET  @offset  ROWS ' + NCHAR(10) + 
      N'FETCH NEXT  @limit  ROWS ONLY; '

SET @params = N' @offset int, @limit int';

--print @sql

EXECUTE sp_executesql @sql, @params, 0, 50;