使用帶參數的 sp_executesql 如何防止 SQL 注入？

January 4, 2022

以下是使用sp_executesql的動態過濾方案

IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders;
GO
CREATE PROC dbo.GetOrders
@orderid AS INT = NULL,
@custid AS INT = NULL,
@empid AS INT = NULL,
@orderdate AS DATE = NULL
AS
DECLARE @sql AS NVARCHAR(1000);
SET @sql = 
N'SELECT orderid, custid, empid, orderdate, filler'
+ N' /* 27702431-107C-478C-8157-6DFCECC148DD */'
+ N' FROM dbo.Orders'
+ N' WHERE 1 = 1'
+ CASE WHEN @orderid IS NOT NULL THEN
N' AND orderid = @oid' ELSE N'' END
+ CASE WHEN @custid IS NOT NULL THEN
N' AND custid = @cid' ELSE N'' END
+ CASE WHEN @empid IS NOT NULL THEN
N' AND empid = @eid' ELSE N'' END
+ CASE WHEN @orderdate IS NOT NULL THEN
N' AND orderdate = @dt' ELSE N'' END;
EXEC sp_executesql
@stmt = @sql,
@params = N'@oid AS INT, @cid AS INT, @eid AS INT, @dt AS DATE',
@oid = @orderid,
@cid = @custid,
@eid = @empid,
@dt = @orderdate;
GO

在T-SQL Querying的第 541 頁上，它說

因為動態程式碼使用參數而不是將常量注入程式碼，所以它不會受到 SQL 注入攻擊。

sp_executesql 中參數的使用如何防止 SQL 注入？

謝謝

翻倍
要回答您的問題，您需要嘗試使用sp_executesqlwith 參數的替代方法：
使用EXEC（不帶sp_executesql）
使用sp_executesql（不帶參數）
在適當的情況下，這兩者都可能導致SQL 注入攻擊。
可能值得注意的是，即使完全沒有參數化，上面的程式碼風險也相對較低，因為傳遞的數據類型不是字元串類型，但它仍然是可能的。
字元串攜帶惡意負載的風險要高得多。
下面的程式碼範例來自我關於在不同上下文中使用動態 SQL 的展示文稿，但非常適用於您的問題。
字元串安全
您可以安全地使用這樣的程式碼，因為使用者輸入不是被執行的字元串的一部分：
DECLARE 
   @SQLString nvarchar(MAX) = N'',
   @TableName sysname = N'Votes';

IF @TableName = N'Votes'
BEGIN
   SET @SQLString += N'SELECT COUNT_BIG(*) AS records FROM dbo.Votes AS v;'
END

IF @TableName = N'Posts'
BEGIN
   SET @SQLString += N'SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p;'
END

EXEC(@SQLString);
GO 
不安全的字元串
在這個例子中，使用者輸入被連接到被執行的字元串中，並且沒有被參數化。這可能會導致問題：
DECLARE 
   @SQLString nvarchar(MAX) = N'',
   @Filter nvarchar(MAX) = N'',
   @Title nvarchar(250) = N''' 
 UNION ALL   
 SELECT 
     t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
 FROM sys.tables AS t --'; 
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
 SELECT TOP (5000) 
     p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
 FROM dbo.Posts AS p 
 WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
   SET @Filter = @Filter + N' 
 AND p.Title LIKE ''' + N'%' + @Title + N'%''';
END;

IF @Filter IS NOT NULL
BEGIN
   SET @SQLString += @Filter;
END;

SET @SQLString += N' 
 ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;

/* Check the results -- what's that at the end? */
EXEC (@SQLString);
最終結果是這樣執行的查詢，它在Title列中搜尋單個萬用字元，然後是列出數據庫中所有表的附加結果。
 SELECT TOP (5000) 
     p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
 FROM dbo.Posts AS p 
 WHERE p.OwnerUserId = 22656  
 AND p.Title LIKE '%' 
 UNION ALL   
 SELECT 
     t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
 FROM sys.tables AS t --%' 
 ORDER BY p.Score DESC;
雖然許多人會關注諸如刪除表之類的模因，但動態 SQL 的真正問題通常是數據被盜。那就是錢的所在。
仍然不安全
使用sp_executesql是一個很好的第一步，但它仍然需要與參數一起使用。像下面這樣的程式碼仍然以與上面相同的方式受到 SQL 注入的影響。
DECLARE 
   @SQLString nvarchar(MAX) = N'',
   @Filter nvarchar(MAX) = N'',
   @Title nvarchar(250) = N''' 
 UNION ALL   
 SELECT 
     t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
 FROM sys.tables AS t --';
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
 SELECT TOP (5000) 
     p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
 FROM dbo.Posts AS p 
 WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
   SET @Filter = @Filter + N' 
 AND p.Title LIKE ''' + N'%' + @Title + N'%''';
END;

IF @Filter IS NOT NULL
BEGIN
   SET @SQLString += @Filter;
END;

SET @SQLString += N' 
 ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;
/* Check the results -- what's that at the end? */
EXEC sys.sp_executesql 
   @SQLString;
將執行與上述相同的查詢。
回到安全
使用更符合您的範例的程式碼，我們可以通過將值分配給參數而不是將其直接連接到字元串中來避免 SQL 注入。
DECLARE 
   @SQLString nvarchar(MAX) = N'',
   @Filter nvarchar(MAX) = N'',
   @Title nvarchar(250) = N''' 
 UNION ALL 
 SELECT 
     t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
 FROM sys.tables AS t --'; 
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
 SELECT TOP (5000) 
     p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
 FROM dbo.Posts AS p 
 WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
   SET @Filter = @Filter + N' 
 AND p.Title LIKE N''%'' + @Title + N''%'' ';
END;

IF @Filter IS NOT NULL
BEGIN
   SET @SQLString += @Filter;
END;

SET @SQLString += N' 
 ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;

/* Check the results -- what's that at the end now? */
EXEC sys.sp_executesql 
   @SQLString, 
 N'@Title NVARCHAR(250)', 
   @Title;
這次我們建弦的結果不同了。現在看起來像這樣：
 SELECT TOP (5000) 
     p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
 FROM dbo.Posts AS p 
 WHERE p.OwnerUserId = 22656  
 AND p.Title LIKE N'%' + @Title + N'%'  
 ORDER BY p.Score DESC;
我們沒有返回包含來自的詳細資訊的結果，而是返回sys.tables零行，因為參數設置為搜尋字元串，並且沒有文章標題與之匹配。
下一個下一個
希望這能讓您更好地了解參數化動態 SQL 如何幫助您避免 SQL 注入攻擊。
還有其他很好的理由使用它，比如更好的計劃記憶體，但這超出了這個問題的範圍。
有關更多資訊，請在此處查看我的文章：
邁向更安全的動態 SQL
不要依賴方括號來保護您免受 SQL 注入

SQL 注入是獲取用於創建查詢的參數數據並將它們注入到查詢中的行為。換句話說，不是使用參數，而是orderid = @oid使用orderid = 'abc'. 這使您容易受到惡意或意外注入不正確或無意的查詢的影響。例如，您可能在參數中有一個撇號，或者它可能無法正確翻譯日期。或者更危險的是，黑客會嘗試類似 Robert'); DROP TABLE Students;--.
上述查詢更安全的原因是它本質上是靜態的。（這個特定的查詢稱為Kitchen Sink Query，並具有其他性能優勢。）它僅響應一組有限的條件而改變：是否提供參數。它不會將實際數據注入到查詢中。
SET @sql = 
N'SELECT orderid, custid, empid, orderdate, filler'
+ N' /* 27702431-107C-478C-8157-6DFCECC148DD */'
+ N' FROM dbo.Orders'
+ N' WHERE 1 = 1'
這部分是靜態的，始終是查詢的一部分
+ CASE WHEN @orderid IS NOT NULL THEN
N' AND orderid = @oid' ELSE N'' END
這部分是有條件添加的。然而，重要的是，實際數據不是查詢的一部分，只有是否提供數據才是改變的部分。
這更容易防範，因為現在唯一的風險是你會預先知道的輕微語法錯誤（你顯然沒有得到 Intellisense）。不同查詢的可能範圍要小得多：您只有 16 種不同的可能性，而且它們都遵循相同的路線，因此您可以更好地推理結果，並且可以輕鬆測試所有可能性是否有效。
其結果是您將查詢和數據分離。查詢被傳遞給編譯器，並且數據單獨綁定到參數，這些參數充當佔位符。編譯器不可能將數據誤解為實際程式碼，它始終是數據。

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

使用帶參數的 sp_executesql 如何防止 SQL 注入？

翻倍

字元串安全

不安全的字元串

仍然不安全

回到安全

下一個下一個

相關問答

允許創建表（動態名稱）並插入但僅此而已

exec(sql) 的安全替代方案

為什麼在儲存過程中的這個查詢上不會發生 SQL 注入？

動態 SQL 查詢 - 如何將 int 添加到程式碼中？

INSERT INTO… 在 LinkedServer 上小批量選擇

動態 SQL 中斷語句