動态SQL

執行動态SQL，可以使用EXEC(@sql)，也可以存儲過程sys.sp_executesql，由于參數是動态SQL暴露出來的，為了避免參數注入的風險，必須對參數做檢查。相比EXEC(@sql)，sys.sp_executesql是更安全的實作方式，因為後者會做參數的檢查。

簡單舉例，對于下面的動态SQL，由于沒有對參數@tablename做檢查，很容易導緻SQL注入。

假如使用者傳遞的參數值是：Users; DROP TABLE dbo.Users;

執行該動态SQL，會把删掉Users表。如何避免這種類型的SQL注入呢？解決方法有多種多樣，比較簡單的一種是使用QUOTENAME()，例如：

把要執行的SQL語句轉換成字元串，通過EXEC()函數來執行該語句，這是正常的方式，需要使用者對參數進行檢查：

根據我的經驗，在編寫動态SQL時，使用QUOTENAME()和REPLACE()更加安全和有效。當參數表示的表對象、視圖對象或者字段時，建議使用QUOTENAME()；當動态SQL的主題比較長時，可以設定占位符，比如##SQL_Block##，##Table_Name##，然後使用REPLACE()函數進行替換。

舉個例子，先寫好動态SQL的總體，把動态變化的參數用一個占位符來表示，最後用實際的參數來替換占位符：

該存儲過程實際上是對查詢進行參數化處理，使得執行查詢的主體不變，隻有參數發生變化。該存儲過程在執行動态SQL時會對參數和參數類型進行檢查，可以避免通過參數傳值引發的注入風險，該存儲過程有三個參數，前兩個參數是不變的，從第三個參數開始的參數，是動态變化的參數值：

參數注釋：

@stmt：動态SQL主體，是NVARCHAR(max)類型

@params：對@stmt中使用的參數進行聲明，是NVARCHAR(max)類型

@param1=value1, @param2=value2, ...：對參數傳值

在編寫動态SQL的主體時，查詢中用到的變量可以不需要聲明，在第二個參數中對@stmt中用到的變量進行聲明，這種方式實際上是強制把參數抽取出來，把整個查詢語句強制參數化處理，如下所示：

使用抽取動态SQL主題，好處是提高查詢性能，通過傳遞參數獲得動态的結果，缺點是不夠靈活，隻能對變量進行參數化處理。