生成動態 SQL 時 SQL Server 中的語法錯誤

設想：

我有一個包含 100M+ 行數據的表，所以我創建了一個索引，其內容如下：

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

現在，當我執行以下程式碼時，它會在更短的時間內執行：

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 

現在，我想將此查詢創建為動態 SQL，因為這些部分name_voter asc, 0 , 50需要是動態的並且將通過後端發送。

但是，當我嘗試執行

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;

我收到錯誤：

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.

我不確定，但是否因為我將列名作為參數傳遞？或者還有別的什麼。我只希望執行該查詢，同時防止 SQL 注入並且執行時間最短。

如果您需要任何進一步的資訊，我會在這裡。

@marcello 的語法是正確的，但另一件事是，為了防止 SQL 注入，您可以檢查@sort_coland的值@sort_dir（因為我假設這些值來自使用者）。方向很簡單：

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

列名涉及更多。您可以只使用常量來限制查詢的輸出列：

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

或者您可以確保該列至少存在於表中，這將允許您更改查詢中的列而無需更改NOT IN列表，並且還允許按查詢輸出中不存在的列進行排序（甚至如果這可能並不總是有意義）：

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

我在一個由兩部分組成的系列中寫了有關此技術和其他技術的文章：

• 在 SQL Server 中保護自己免受 SQL 注入 - 第 1 部分
• 在 SQL Server 中保護自己免受 SQL 注入 - 第 2 部分

引用自：https://dba.stackexchange.com/questions/219736