Store Procedure 動態語法預防SQL Injection方式

2009-06-03

14880
0
MySQL
2010-03-17

最近在處理StoreProcedure撰寫動態語法SQL Injection的問題，整理後記錄下來，SQL Server主要是使用 sp_executesql 來處理，MySQL是使用 PREPARE 搭配 EXECUTE 來執行。

最近在處理StoreProcedure撰寫動態語法SQL Injection的問題，整理後記錄如下。

在此針對SQL Server及MySQL的處理方式做說明。

先以SQL Server示範一個錯誤的例子(以北風資料庫為例)。

一、動態SQL錯誤(不安全)範例(MSSQL)：


USE Northwind;
-- 宣告變數
DECLARE @strSQL NVarchar(100);
DECLARE @ContactTitle NVarchar(30);
DECLARE @City NVarchar(15);

-- 設定條件值
SET @ContactTitle = N'Sales Representative';
SET @City = N'London';

-- 動態組出SQL陳述式
SET @strSQL = N'SELECT * FROM Customers WHERE ContactTitle = ''' + @ContactTitle
     + ''' AND City = ''' + @City + '''';

-- 執行
exec (@strSQL)

以上方式因為是將條件值以組字串的方式執行，會有SQL Injection的問題，非常容易遭到駭客攻擊！
正確的寫法應使用 sp_executesql 來處理。

二、動態SQL正確範例：


USE Northwind;

-- 宣告變數
DECLARE @strSQL NVarchar(100);
DECLARE @ContactTitle NVarchar(30);
DECLARE @City NVarchar(15);
DECLARE @ParmDefinition NVarchar(500);

-- 設定條件值
SET @ContactTitle = N'Sales Representative';
SET @City = N'London';

-- 動態組出SQL陳述式(SQL語法跟 .NET組動態SQL寫法一樣)
SET @strSQL = N'SELECT * FROM Customers WHERE ContactTitle = @ContactTitle AND City = @City';

-- 定義傳入的參數型態 (與strSQL中的變數名稱對應)
SET @ParmDefinition  = N'@ContactTitle NVarchar(30), @City NVarchar(15)';

-- 透過sp_executesql執行
exec sp_executesql @strSQL, @ParmDefinition , @ContactTitle, @City;

sp_executesql參數說明如下：
    第1個參數：傳入要執行的SQL陳述式。
    第2個參數：定義SQL陳述式中的參數型態。
    第3個參數：要傳入的參數值，多個參數以半型號分隔。

StoreProcedure使用此方式撰寫時，需注意權限的設定，當使用exec直接執行SQL陳述式時，只要設定StoreProcedure的執行權限即可，但若透過sp_executesql執行，除了須設定StoreProcedure的執行權限外，也要設定Table相對應的使用權限才可正常執行，以上述程式碼為例，是進行select的動作，所以也要開啟可對Customers資料表做select的權限才可正常執行。

三、MySQL動態SQL範例：

在MySQL中，從5.0版本開始支援動態語法，使用 PREPARE 搭配 EXECUTE 執行。


-- 動態組出SQL陳述式(參數是使用問號(?)帶入)
SET @strSQL ='SELECT * FROM Customers WHERE ContactTitle = ?';
SET @strSQL = concat(@strSQL, ' AND City = ?');

-- 設定條件值
SET @ContactTitle = 'Sales Representative'; SET @City = 'London';

-- 執行
PREPARE preSQL FROM @strSQL;
EXECUTE preSQL USING @ContactTitle, @City;

preSQL是我們自己定義的名稱，USING傳入的變數是要帶入的參數，順序須與語法中問號(?)出現的順序相同。
MySQL還有一個釋放資源DEALLOCATE PREPARE的用法，在此例中未使用，當資料庫連線關閉時伺服器會自動釋放。

參考連結：

SQL Server：

MySQL：