One way to prevent Injection Attach

Injection Attach

We ALL know (I hope) that certain formulations OF dynamic SQL expose the DBMS TO special security risks called "injection attacks". Now we will discuss it and give you a way to prevent injection attacks.

 

PLS do the test first

The First SP

The Second SP

The Third SP

The Fourth SP

Conclusion(how to Prevent)

 

 

 

PLS do the test first

Which OF the following 4 stored procedures are vulnerable TO an injection attack? 

 

The First SP:

 

            CREATE PROC dbo.Dynamic1(@TABLE sysname, @nameCol sysname, @nameVal varchar(35))

            AS

 

            SET NOCOUNT ON

            DECLARE @SQL nvarchar(4000)

 

            SET @SQL = N'SELECT * FROM ' + @TABLE + N' WHERE '

            + @nameCol + N' = ''' + CAST(@nameVal AS nvarchar(35)) + ''''

 

            EXEC (@SQL)

            GO

 

The Second SP:

 

            CREATE PROC dbo.Dynamic2(@TABLE sysname, @nameCol sysname, @nameVal varchar(35))

            AS

 

            SET NOCOUNT ON

            DECLARE @SQL nvarchar(4000)

 

            SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TABLE) + N' WHERE ' + QUOTENAME(@nameCol) + N' = '''  + CAST(@nameVal AS nvarchar(35)) + ''''

 

            EXEC (@SQL)

            GO

 

The Third SP:

 

            CREATE PROC dbo.Dynamic3(@TABLE sysname, @nameCol sysname, @nameVal varchar(35))

            AS

 

            SET NOCOUNT ON

            DECLARE @SQL nvarchar(4000)

 

            SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TABLE) + N' WHERE ' + QUOTENAME(@nameCol) + N' = ''' + CAST(@nameVal AS nvarchar(35)) + ''''

 

            EXEC sp_executeSQL @SQL

            GO

 

The Fourth SP:

 

            CREATE PROC dbo.Dynamic4(@TABLE sysname, @nameCol sysname, @nameVal varchar(35))

            AS

 

            SET NOCOUNT ON

            DECLARE @SQL nvarchar(4000)

 

            SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TABLE) + N' WHERE ' + QUOTENAME(@nameCol) + N' = @nameVal'

 

            EXEC sp_executeSQL @SQL, N'@nameval varchar(35)', @nameval

            GO

 

Now you have the following choice:

A:   Dynamic1 only

B:   Dynamic1 and Dynamic2 only

C:   Dynamic1, Dynamic2, and Dynamic3 only

D:   All 4 procs are vulnerable to injection attacks

 

Which one you will choice?

 

The correct answer is  C:  - Dynamic1, Dynamic2, and Dynamic3 only.

 

Conclusion(how to Prevent):

The methods to protect dynamically-generated SQL statements against injection attacks include:

 

1) using the QUOTENAME function to force table and column name parameters to always be evaluated as db objects

 

2) using a parameterized construct for resolving user inputs (usually part of the criteria expressed in a WHERE clause).

 

 Consider the following example calls, substituting the numbers 1-4 for . They are "safe" to run against the Northwind database if you'd like to view the results:

 

EXEC dbo.dynamic1 'sysobjects--Employees', 'test', 'test'

GO

 

 

 

EXEC dbo.dynamic3 'Employees', 'LastName', '''SELECT * FROM sysobjects--'