TSQL -> Check string input for possible sql injection attempt

Suppose that we execute on the server side SQL string that came from the client (for example input from ASP page):

Read more about this subject (SQLserver + ASP):
Using SQL Server Stored Procedures To Prevent SQL Injection
SQL Injection Attacks: Are You Safe? - Injection attack examples

USE [colombo]
GO

IF EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[sp_myquery_exec]')
  AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_myquery_exec]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ==============================================================
  Author: [ amper ]
  Create date: [ 2007-02-25 ]
  Description: [run select statement composed on the client side]
================================================================ */

CREATE PROCEDURE dbo.sp_myquery_exec @strSQL nvarchar(250)
AS
BEGIN
  /* check recived string for possible sql injection */

  IF LOWER(@strSQL) LIKE LOWER(N'%EXEC%')
   OR LOWER(@strSQL) LIKE LOWER(N'%INSERT%')
   OR LOWER(@strSQL) LIKE LOWER(N'%UPDATE%')
   OR LOWER(@strSQL) LIKE LOWER(N'%DELETE%')
   OR LOWER(@strSQL) LIKE LOWER(N'%TRUNCATE%')
   OR LOWER(@strSQL) LIKE LOWER(N'%DROP%')
   OR LOWER(@strSQL) LIKE LOWER(N'%ALTER%')
   OR LOWER(@strSQL) LIKE LOWER(N'%GRANT%')
   OR LOWER(@strSQL) LIKE LOWER(N'%REVOKE%')
   OR LOWER(@strSQL) LIKE LOWER(N'%ADMIN%')
   OR LOWER(@strSQL) LIKE LOWER(N'%USERS%')
   OR LOWER(@strSQL) LIKE LOWER(N'%--%')
   OR LOWER(@strSQL) LIKE LOWER(N'%/*%*/%')
   OR LOWER(@strSQL) LIKE LOWER(N'%;%')
  BEGIN
   RAISERROR('command text is suspect for SQL Injection attempt',16,1);
   RETURN;
  END

  EXEC sp_executesql @strSQL;

END
GO

GRANT EXECUTE ON OBJECT::[dbo].[sp_myquery_exec] TO george;
GO


Execution of:
EXEC sp_myquery_exec 'select TOP 3 empno, fact, createdate FROM EmployeeFacts';

Will return results like this:
empnofactcreatedate
5maggi wants to go to vacation...18/04/99
6in April voich was late to work...24/05/99
10aiva was a best sales person...15/01/00

While Execution of:
EXEC sp_myquery_exec 'DELETE FROM EmployeeFacts';

Will return:
Msg 50000, Level 16, State 1, Procedure sp_myquery_exec, Line 22 command text is suspect for SQL Injection attempt


sqlexamples.info