iT邦幫忙

DAY 30
13

T-SQL 應用之美系列 第 30

如何動態組出 T-SQL 指令(下)

上一篇分享是關於如何使用 EXECUTE 陳述式動態組出 SQL 陳述式,由於 EXECUTE 陳述式會讓駭客更容易進行 SQL 資料隱碼攻擊(SQL Injection),所以這次要分享使用系統預存程序 sp_executesql,透過指定參數的方法動態組出 T-SQL 指令,來避免 SQL 資料隱碼的攻擊。
sp_executesql 系統預存程序具備以參數來指定所要執行的 T-SQL,藉由這個特性,可以讓動態組出的 T-SQL 更加安全。

假設我們要動態組出 T-SQL,從 AdventureWorks 資料庫中的 Production.Product 資料表內,找出「顏色」是黑色的產品,可以使用下面的程式碼:

USE AdventureWorks
GO

-- 宣告變數
DECLARE @SQLCommand nvarchar(200)
DECLARE @columnList nvarchar(50)
DECLARE @parmColor varchar(10)

-- 定義變數
SET @columnList = N'ProductID 產品編號, Color 顏色'
SET @parmColor = 'Black'

-- 動態組出 T-SQL
SET @SQLCommand = 'SELECT ' + @columnList + ' FROM Production.Product WHERE Color = @color'

-- 執行動態組出的 T-SQL
EXECUTE sp_executesql @SQLCommand, N'@color varchar(10)', @color = @parmColor

執行的部分結果畫面:

從程式碼中,可以看到系統預存程序 sp_executesql 一共需要傳入 3 個參數,分別說明如下:

●第 1 個參數:
所要執行的 T-SQL 陳述式,其內容必須是 Unicode 字串,所以在宣告 @SQLCommand 時,必須指定為 n 開頭的資料型別,在這個範例我是使用 nvarchar 型別。
因為要把英文的欄位名稱改用中文來顯示,所以在定義變數 @columnList 時,要使用前置詞 N 來指定該變數的內容。

●第 2 個參數:
它會包含第 1 個參數中,所有內嵌的參數定義。以本程式碼為例,@color 就是內嵌的參數定義。同樣的,這個參數也必須使用前置詞 N 來指定該變數的內容,且每個參數的定義都是由參數名稱(在該程式碼內,就是 @color)和資料型別(在該程式碼中,就是 varchar(10))所組成。

●第 3 個參數:
指定第 2 個參數內的參數定義的值。以本程式碼為例,第二個參數指定為 @color varchar(10),所以要把先前設定的 @parmColor 的值指派給 @color,這樣才能讓 @parmColor 所代表的 Black 傳遞給 @color。

大家可以跟上一篇分享:<a href="">「如何動態組出 T-SQL 指令(上)」</a>,比較一下,於設定 @color 顏色變數的值時,上一篇文章在變數值的左右各用了 3 個單引號(')來把 Black 包起來,而在這裡只需要用 1 對單引號就可以了。

如果現在要再加上一個查詢的限制條件,就是「產品編號」要小於 325,就要多宣告並設定一個新變數 @parmPID,同時在系統預存程序 sp_executesql 也要隨之修改:

USE AdventureWorks
GO

-- 宣告變數
DECLARE @SQLCommand nvarchar(200)
DECLARE @columnList nvarchar(50)
DECLARE @parmColor varchar(10)
DECLARE @parmPID int

-- 定義變數
SET @columnList = N'ProductID 產品編號, Color 顏色'
SET @parmColor = 'Black'
SET @parmPID = 325

-- 動態組出 T-SQL
SET @SQLCommand = 'SELECT ' + @columnList + ' FROM Production.Product WHERE Color = @color AND ProductID < @pID'

-- 執行動態組出的 T-SQL
EXECUTE sp_executesql @SQLCommand, N'@color varchar(10), @pID int',
						@color = @parmColor, @pID = @parmPID

執行結果:

如果不想使用 EXECUTE 或 sp_executesql 來動態組出 T-SQL,可以改用下面的程式碼:

USE AdventureWorks
GO

-- 宣告變數
DECLARE @parmColor varchar(10)
DECLARE @parmPID int

-- 定義變數
SET @parmColor = 'Black'
SET @parmPID = 325

SELECT ProductID 產品編號, Color 顏色
FROM Production.Product WHERE Color = @parmColor AND ProductID < @parmPID

當然執行的結果會跟上一個程式碼一模一樣,這種殊途同歸的程式碼寫法,就是撰寫程式的樂趣之一!


上一篇
如何動態組出 T-SQL 指令(上)
系列文
T-SQL 應用之美30

尚未有邦友留言

立即登入留言