Pivot (Dynamic) vaildate SQL injection

USE [Northwind]
GO

/****** Object: StoredProcedure [dbo].[usp_pivot] Script Date: 07/07/2010 16:03:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[usp_pivot]
@schema_name AS sysname = N'dbo', -- schema of table/view
@object_name AS sysname = NULL, -- name of table/view
@on_rows AS sysname = NULL, -- group by column
@on_cols AS sysname = NULL, -- rotation column
@agg_func AS NVARCHAR(12) = N'MAX', -- aggregate function
@agg_col AS sysname = NULL -- aggregate column
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);
-- Check for missing input
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 THEN N'@schema_name;' ELSE N'' END
+ CASE WHEN @object_name IS NULL THEN N'@object_name;' ELSE N'' END
+ CASE WHEN @on_rows IS NULL THEN N'@on_rows;' ELSE N'' END
+ CASE WHEN @on_cols IS NULL THEN N'@on_cols;' ELSE N'' END
+ CASE WHEN @agg_func IS NULL THEN N'@agg_func;' ELSE N'' END
+ CASE WHEN @agg_col IS NULL THEN N'@agg_col;' ELSE N'' END
RAISERROR(@msg, 16, 1);
RETURN;
END
-- Allow only existing table or view name as input object
IF COALESCE(OBJECT_ID(@object, N'U'),
OBJECT_ID(@object, N'V')) IS NULL
BEGIN
SET @msg = N'%s is not an existing table or view in the database.';
RAISERROR(@msg, 16, 1, @object);
RETURN;
END
-- Verify that column names specified in @on_rows, @on_cols, @agg_col exist
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
BEGIN
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);
RETURN;
END
-- Verify that @agg_func is in a known list of functions
-- Add to list as needed and adjust @agg_func size accordingly
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')
BEGIN
SET @msg = N'%s is an unsupported aggregate function.';
RAISERROR(@msg, 16, 1, @agg_func);
RETURN;
END
-- Construct column list
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,
@params = N'@result AS NVARCHAR(MAX) OUTPUT',
@result = @cols OUTPUT;
-- Check @cols for possible SQL injection attempt
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%')
-- look for other possible strings used in SQL injection here
BEGIN
SET @msg = N'Possible SQL injection attempt.';
RAISERROR(@msg, 16, 1);
RETURN;
END
-- Create the PIVOT query
SET @sql =
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;
GO