UDF to Check for SQL Injection

-- ============================================================================
-- Check Statement for SQL Injection
--
-- This scalar UDF accepts a varchar(max) data type parameter. It returns a bit
-- value (1 or 0), based on whether the passed parameter contains suspicious
-- values (as defined by the function). So, if the input parameter contains a
-- call to a command such as "DELETE" or "SHUTDOWN", the flag is set to "1":
-- ============================================================================

if exists(select * from sysobjects where name = 'CheckForSQLInjection')
    begin
        print '- Dropping existing "dbo.CheckForSQLInjection" UDF.';
        drop function dbo.CheckForSQLInjection;
    end;
go

print '- Creating "dbo.CheckForSQLInjection" UDF.';
go
create function dbo.CheckForSQLInjection (
    @TSQLString varchar(max)
) returns bit
as
begin
    declare @IsSuspect bit;

    -- UDF assumes string will be left padded with a single space:
    set @TSQLString = ' ' + @TSQLString;

    if (
        PATINDEX('% xp_%',             @TSQLString) <> 0 or
        PATINDEX('% sp_%',             @TSQLString) <> 0 or
        PATINDEX('% DROP %',           @TSQLString) <> 0 or
        PATINDEX('% GO %',             @TSQLString) <> 0 or
        PATINDEX('% INSERT %',         @TSQLString) <> 0 or
        PATINDEX('% UPDATE %',         @TSQLString) <> 0 or
        PATINDEX('% DBCC %',           @TSQLString) <> 0 or
        PATINDEX('% SHUTDOWN %',       @TSQLString) <> 0 or
        PATINDEX('% ALTER %',          @TSQLString) <> 0 or
        PATINDEX('% CREATE %',         @TSQLString) <> 0 or
        PATINDEX('%;%',                @TSQLString) <> 0 or
        PATINDEX('% EXECUTE %',        @TSQLString) <> 0 or
        PATINDEX('% BREAK %',          @TSQLString) <> 0 or
        PATINDEX('% BEGIN %',          @TSQLString) <> 0 or
        PATINDEX('% CHECKPOINT %',     @TSQLString) <> 0 or
        PATINDEX('% BREAK %',          @TSQLString) <> 0 or
        PATINDEX('% COMMIT %',         @TSQLString) <> 0 or
        PATINDEX('% TRANSACTION %',    @TSQLString) <> 0 or
        PATINDEX('% CURSOR %',         @TSQLString) <> 0 or
        PATINDEX('% GRANT %',          @TSQLString) <> 0 or
        PATINDEX('% DENY %',           @TSQLString) <> 0 or
        PATINDEX('% ESCAPE %',         @TSQLString) <> 0 or
        PATINDEX('% WHILE %',          @TSQLString) <> 0 or
        PATINDEX('% OPENDATASOURCE %', @TSQLString) <> 0 or
        PATINDEX('% OPENQUERY %',      @TSQLString) <> 0 or
        PATINDEX('% OPENROWSET %',     @TSQLString) <> 0 or
        PATINDEX('% EXEC %',           @TSQLString) <> 0
    )
        begin
            select @IsSuspect = 1;
        end
    else
        begin
            select @IsSuspect = 0;
        end

    return (@IsSuspect);
end
go

-- ===========================================================================
-- Example Usage
-- ===========================================================================

-- Use AdventureWorks2012;
-- go

-- select dbo.CheckForSQLInjection ('SELECT * FROM HumanResources.Department'); --> 0
-- select dbo.CheckForSQLInjection (';SHUTDOWN'); --> "1", contains "SHUTDOWN"
-- select dbo.CheckForSQLInjection ('DROP HumanResources.Department'); --> "1", contains "DROP"