SQL injection samples.

This is an example when a SQL injection occurs.

I pick this article off from

http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

it´s not mine but is very interesting.

-- An innocent looking SP

CREATE PROC [sp_demo_injection01]( @name sysname )

AS

-- ...with an obvious SQL injection-vulnerable sample

EXEC( 'SELECT * FROM sys.database_principals WHERE name = ''' + @name +'''' )

go

-- This is how it was intended to be used

declare @var sysname

SET @var = 'Some Name'

EXEC [sp_demo_injection01] @var

go

-- As you can see, I can easily abuse this module in the following manner

declare @var sysname

SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'

EXEC [sp_demo_injection01] @var

go

When the attacker runs this query the system will concatenate the input to the command we defined in the SP:

EXEC ( 'SELECT * FROM sys.database_principals WHERE name = ''' + 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!' +'''' )

The attacker is able to close the quote in the user name (notice the trailing quote in Some Name’) and converted the rest of what should have been a user name into a different SQL statement, causing the following command to be executed:

SELECT * FROM sys.database_principals WHERE name = 'Some Name'; GRANTCONTROL TO [Malicious User]; PRINT 'Game over! This system is no longer yours!'-- Malicious User now can control the database!!!'

As you can see the attacker was able to add extra SQL statements that were not intended by the author of the stored procedure, in this case granting CONTROL on the database to herself and printing a note.

Parameterization

In most of these scenarios there is an alternative to the example used above using parameterization. Using parameterization gives you the advantage that you can clearly specify the data type and avoid pitfalls as well as the final T-SQL statement generated will reference the parameters as variables and not directly use the user defined input to generate the statement.

If you are using T-SQL directly to generate dynamic SQL, you can take advantage of sp_ExecuteSql to execute parameterized queries, for example:

-- An improved version of [sp_demo_injection01]

CREATE PROC [sp_demo_injection02]( @name sysname )

AS

declare @cmd nvarchar(max)

declare @parameters nvarchar(max)

set @cmd = N'SELECT * FROM sys.database_principals WHERE name = @name'

set @parameters = '@name sysname'

EXEC sp_executesql @cmd, @parameters, @name = @name

go

-- This is how it was intended to be used

declare @var sysname

SET @var = 'Some Name'

EXEC [sp_demo_injection02] @var

go

-- The previous attack no longer has any effect!

declare @var sysname

SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'

EXEC [sp_demo_injection02] @var

go

CREATE PROC [sp_demo_injection03]( @Value nvarchar(100) )

AS

declare @cmd nvarchar(max)

declare @parameters nvarchar(max)

set @cmd = N'SELECT * FROM sys.database_principals WHERE principal_id = @Value'

set @parameters = '@Value int'

EXEC sp_executesql @cmd, @parameters, @value = @value

go

-- Should work

declare @var sysname

SET @var = '1'

EXEC [sp_demo_injection03] @var

go

-- Expect error 8114

-- Error converting data type nvarchar to int.

declare @var sysname

SET @var = '1; select * from sys.objects'

EXEC [sp_demo_injection03] @var

go

But be careful, using sp_executesql is not a guarantee that the SQL statement to be executed is not susceptible to SQL injection; the parameters should be used properly in order to really take advantage of this feature. The following example is a demonstration of a common mistake I have seen a few times: constructing the @cmd parameter using user-defined data instead of using it as a parameter.

-------------------------------------------------------------

-- Incorrect usage of sp_executeSql

CREATE PROC [sp_demo_injection04]( @name sysname )

AS

declare @cmd nvarchar(max)

declare @parameters nvarchar(max)

-- Looks famliar? yep, same injection as [sp_demo_injection01]

set @cmd = N'SELECT * FROM sys.database_principals WHERE name = ''' +@name + N''''

-- No parameters!!! This is typically a sign of misusing sp_execsql.

set @parameters = null

EXEC sp_executesql @cmd, @parameters

go

-- and now run the same attack we tried before...

declare @var sysname

SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'

EXEC [sp_demo_injection04] @var

-- ... and it is game over!

go

Be aware that sp_ExecuteSql doesn’t automatically protect against every SQL injection. It helps you to create the parameterized query, but it has to be used properly in order to work. I have seen a common misuse of this stored procedure: using the user-defined input (untrusted data) to generate the @statement parameter.

---------------------------------------------------------------------

-- Incorrect usage of sp_executeSql

CREATE PROC [sp_demo_injection04]( @name sysname )

AS

declare @cmd nvarchar(max)

declare @parameters nvarchar(max)

-- Looks famliar? yep, same injection as [sp_demo_injection01]

set @cmd = N'SELECT * FROM sys.database_principals WHERE name = ''' +@name + N''''

-- No parameters!!! This is typically a sign of misusing sp_execsql.

set @parameters = null

EXEC sp_executesql @cmd, @parameters

go

-- and now run the same attack we tried before...

declare @var sysname

SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'

EXEC [sp_demo_injection04] @var

-- ... and it is game over!

go

If you are using the .Net framework, you can use the SqlParameter class to create parameterized queries in a similar way, and the same warning still applies: Do not use user-defined input directly when constructing the parameterized statement. For further reference on this class, please refer tohttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlparameterclasstopic.asp