動态Pivot(2)

存儲過程sp_pivot的實作包含糟糕的程式設計習慣和安全隐患。就像我在本章的前面提到的，微軟強烈建議不要在使用者定義存儲過程的名稱中使用sp_字首。一方面，把存儲過程建立為特殊存儲過程的會帶來靈活性；但另一方面，你所依賴的行為得不到任何支援。是以最好放棄這種通過建立以sp_為字首的存儲過程擷取的靈活性，在使用者資料庫中使用其他字首建立使用者定義存儲過程。

EXEC Northwind.dbo.sp_pivot

@query    = N'dbo.Orders',

@on_rows  = N'1 AS dummy_col) DummyTable;

PRINT ''So easy to inject code here!

This could have been a DROP TABLE or xp_cmdshell command!'';

SELECT * FROM (select EmployeeID AS empid',

@on_cols  = N'MONTH(OrderDate)',

@agg_func = N'COUNT',

@agg_col  = N'*';

存儲過程生成的查詢字元串應該是這樣的：

SELECT *

FROM

( SELECT

1 AS dummy_col) DummyTable;

PRINT 'So easy to inject code here!

This could have been a DROP TABLE or xp_cmdshell command!';

SELECT * FROM (select EmployeeID AS empid,

MONTH(OrderDate) AS pivot_col,

1 AS agg_col

( SELECT * FROM dbo.Orders

) AS Query

) AS PivotInput

PIVOT

( COUNT(agg_col)

FOR pivot_col

IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])

) AS PivotOutput;

當執行這些代碼時，注入的PRINT語句可以順利執行。為了證明可以輕易地注入代碼，我使用了一個無害的PRINT語句，但是很明顯，這些惡意代碼可以是任何有效的T-SQL代碼。例如DROP TABLE語句、調用xp_cmdshell等。總之，在這些地方采取措施防範SQL注入是極其重要的。

該存儲過程不僅未防範SQL注入，而且根本就沒有執行任何輸入驗證。例如，應該驗證輸入的對象和列名稱的有效性。該存儲過程也沒有包含錯誤處理。我會在第10章讨論錯誤處理，是以沒有在修改後的解決方案中示範這一點。下面我将示範輸入驗證。

在呈現修改後的解決方案之前，先删除已經存在的sp_pivot：

USE master;

GO

IF OBJECT_ID('dbo.sp_pivot') IS NOT NULL

DROP PROC dbo.sp_pivot;

代碼清單7-9是該任務的修改後的解決方案

代碼清單7-9  建立usp_pivot存儲過程的腳本

USE Northwind;

IF OBJECT_ID('dbo.usp_pivot') IS NOT NULL

DROP PROC dbo.usp_pivot;

CREATE PROC dbo.usp_pivot

@schema_name AS sysname      = N'dbo',

-- 表/視圖的架構

@object_name AS sysname      = NULL, 

-- 表/視圖的名稱

@on_rows     AS sysname      = NULL, -- 分組列

@on_cols     AS sysname      = NULL, -- 旋轉列

@agg_func    AS NVARCHAR(12) = N'MAX',

-- 聚集函數

@agg_col     AS sysname      = NULL -- 統計列

AS

DECLARE

@object  AS NVARCHAR(600),

@sql     AS NVARCHAR(MAX),

@cols    AS NVARCHAR(MAX),

@newline AS NVARCHAR(2),

@msg     AS NVARCHAR(500);

SET @newline = NCHAR(13) + NCHAR(10);

SET @object  = QUOTENAME(@schema_name) + N'.' + QUOTENAME(@object_name);

-- 檢查是否缺少輸入

IF   @schema_name IS NULL

OR @object_name IS NULL

OR @on_rows     IS NULL

OR @on_cols     IS NULL

OR @agg_func    IS NULL

OR @agg_col     IS NULL

BEGIN

SET @msg = N'Missing input parameters: '

+ CASE WHEN @schema_name IS NULL 

+ CASE WHEN @object_name IS NULL 

+ CASE WHEN @on_rows     IS NULL 

+ CASE WHEN @on_cols     IS NULL 

+ CASE WHEN @agg_func    IS NULL 

+ CASE WHEN @agg_col     IS NULL 

RAISERROR(@msg, 16, 1);

RETURN;

END 

-- 隻允許已存在的表或視圖作為輸入對象

IF COALESCE(OBJECT_ID(@object, N'U'),

OBJECT_ID(@object, N'V')) IS NULL

SET @msg = N'%s is not an existing table or view in the database.';

RAISERROR(@msg, 16, 1, @object);

END

-- 檢查 @on_rows, @on_cols, @agg_col 中的列名稱是否存在

IF   COLUMNPROPERTY(OBJECT_ID(@object), @on_rows, 'ColumnId') IS NULL

OR COLUMNPROPERTY(OBJECT_ID(@object), @on_cols, 'ColumnId') IS NULL

OR COLUMNPROPERTY(OBJECT_ID(@object), @agg_col, 'ColumnId') IS NULL

SET @msg = N'%s, %s and %s must'

+ N' be existing column names in %s.';

RAISERROR(@msg, 16, 1, @on_rows, @on_cols, @agg_col, @object);

-- 檢查@agg_func是否是已知的函數

-- 根據需要增加該清單并相應調整@agg_func的大小　

IF @agg_func NOT IN

(N'AVG', N'COUNT', N'COUNT_BIG', N'SUM', N'MIN', N'MAX',

N'STDEV', N'STDEVP', N'VAR', N'VARP')

SET @msg = N'%s is an unsupported aggregate function.';

RAISERROR(@msg, 16, 1, @agg_func);

-- 構造列清單

SET @sql =

N'SET @result = '                                    + @newline +

N'  STUFF('                                          + @newline +

N'    (SELECT N'','' + '

+ N'QUOTENAME(pivot_col) AS [text()]'       + @newline +

N'     FROM (SELECT DISTINCT('

+ QUOTENAME(@on_cols) + N') AS pivot_col'   + @newline +

N'           FROM ' + @object + N') AS DistinctCols' + @newline +

N'     ORDER BY pivot_col'                           + @newline +

N'     FOR XML PATH('''')),'                         + @newline +

N'    1, 1, N'''');'

EXEC sp_executesql

@stmt   = @sql,

@result = @cols OUTPUT;

-- 檢查 @cols 是否存在SQL 注入嘗試

IF   UPPER(@cols) LIKE UPPER(N'%0x%')

OR UPPER(@cols) LIKE UPPER(N'%;%')

OR UPPER(@cols) LIKE UPPER(N'%''%')

OR UPPER(@cols) LIKE UPPER(N'%--%')

OR UPPER(@cols) LIKE UPPER(N'%/*%*/%') 

OR UPPER(@cols) LIKE UPPER(N'%EXEC%')

OR UPPER(@cols) LIKE UPPER(N'%xp[_]%')

OR UPPER(@cols) LIKE UPPER(N'%sp[_]%')

OR UPPER(@cols) LIKE UPPER(N'%SELECT%')

OR UPPER(@cols) LIKE UPPER(N'%INSERT%')

OR UPPER(@cols) LIKE UPPER(N'%UPDATE%')

OR UPPER(@cols) LIKE UPPER(N'%DELETE%')

OR UPPER(@cols) LIKE UPPER(N'%TRUNCATE%')

OR UPPER(@cols) LIKE UPPER(N'%CREATE%')

OR UPPER(@cols) LIKE UPPER(N'%ALTER%')

OR UPPER(@cols) LIKE UPPER(N'%DROP%')

-- 其他一些可能用于SQL注入的字元串

SET @msg = N'Possible SQL injection attempt.';

-- 建立PIVOT查詢

N'SELECT *'                                          + @newline +

N'FROM'                                              + @newline +

N'  ( SELECT '                                       + @newline +

N'      ' + QUOTENAME(@on_rows) + N','               + @newline +

N'      ' + QUOTENAME(@on_cols) + N' AS pivot_col,'  + @newline +

N'      ' + QUOTENAME(@agg_col) + N' AS agg_col'     + @newline +

N'    FROM ' + @object                               + @newline +

N'  ) AS PivotInput'                                 + @newline +

N'  PIVOT'                                           + @newline +

N'    ( ' + @agg_func + N'(agg_col)'                 + @newline +

N'      FOR pivot_col'                               + @newline +

N'        IN(' + @cols + N')'                        + @newline +

N'    ) AS PivotOutput;';

EXEC sp_executesql @sql;

該存儲過程的實作遵循了良好的程式設計習慣并解決了前面提到的安全缺陷。但是要記住，當根據使用者輸入和存儲資料/中繼資料構造代碼時，要完全地防範SQL注入是非常困難的。

存儲過程usp_pivot是在Northwind資料庫中以usp_字首建立的使用者定義存儲過程。這意味着它隻能與Northwind中的表和視圖進行互動，從這個意義來講，它不如前面的實作那樣靈活。但是你可以在Northwind中建立用于查詢其他資料庫對象的視圖，并把該視圖作為輸入提供給這個存儲過程。

usp_pivot存儲過程的代碼提供了幾種防範SQL注入嘗試的措施：

 限制輸入參數的大小。

 存儲過程隻接收在資料庫中存在的有效的表或視圖名稱，不接收其他形式的查詢。同樣，存儲過程中的輸入參數@on_rows、@on_cols和@agg_co隻接收在輸入表/視圖中存在的有效的列名稱，不能是任意的T-SQL表達式。你可以使用任意的查詢建立視圖，然後把它作為該存儲過程的輸入。

 代碼在引用對象和列名稱的地方使用了QUOTENAME，并用方括号作為分隔辨別符。

 存儲過程的代碼檢查@cols變量是否存在注入的代碼字元串，它們可能通過存儲被串聯起來的旋轉列值注入。

代碼還對輸入執行檢查以確定提供了所有參數，這些表/視圖和列名稱存在，輸入的聚集函數包含在支援的函數清單中。關于錯誤處理，我将在第10章再讨論。

usp_pivot存儲過程看起來沒有sp_pivot靈活，但你可以建立視圖為usp_pivot提供資料。例如，考慮下面的代碼，在前面曾用它傳回按訂單年份旋轉的每個員工的訂單金額合計（數量*單價）：

@query    = N'

SELECT O.OrderID, EmployeeID, OrderDate, Quantity, UnitPrice

FROM dbo.Orders AS O

JOIN dbo.[Order Details] AS OD

ON OD.OrderID = O.OrderID',

@on_rows  = N'EmployeeID AS empid',

@on_cols  = N'YEAR(OrderDate)',

@agg_func = N'SUM',

@agg_col  = N'Quantity*UnitPrice';

通過建立一個包含所需資料的視圖，你就可以利用usp_pivot實作同樣的功能。

IF OBJECT_ID('dbo.ViewForPivot') IS NOT NULL

DROP VIEW dbo.ViewForPivot;

CREATE VIEW dbo.ViewForPivot

SELECT

O.OrderID       AS orderid,

EmployeeID      AS empid,

YEAR(OrderDate) AS order_year,

Quantity * UnitPrice AS val

ON OD.OrderID = O.OrderID;

然後調用usp_pivot，就像這樣：

EXEC dbo.usp_pivot

@object_name = N'ViewForPivot',

@on_rows  = N'empid',

@on_cols  = N'order_year',

@agg_col  = N'val';

你将得到前面表7-13所示的輸出。

相對于你的系統安全而言，這隻是很小的代價。

完成後，運作下面的代碼進行清理。