SQL Injection Attacks: When using ADO.NET Command objects is not sufficient
About 2 months ago, I stumbled upon a slightly disconcerting SQL injection attack vector.
Most guidance that deals with avoiding SQL injection attacks includes using ADO.NET Command objects and their Parameters property. When you combine the use of ADO.NET Command/Parameter objects with SQL stored procedures, you should be protected well against SQL injection attacks. (At least, that's what I understand from the guidance available from Microsoft at [1].
Now, this seems to work effectively: bad "characters" are somehow filtered out. I am not sure where this happens, in the Command object or by the stored procedure (I've heard some conflicting statements about that from Microsofties).
However, there is one scenario in which this doesn't work at all, and that's when your stored procedures create a dynamic SQL statement. So, if your stored procedure looks like this:
CREATE PROCEDURE dbo.InjectTest
(
@Input varchar(20)
)
AS
BEGIN
DECLARE @Sql varchar(60);
SET @Sql = 'SELECT * FROM Entries WHERE ID = ''' + @Input + '''';
EXEC (@Sql);
END
GO
And then you call that stored procedure as follows:
Dim Conn As New SqlClient.SqlConnection("server=(local);database=Tests;integrated security=sspi;")Dim Command As New SqlClient.SqlCommand("InjectTest", conn)
Command.CommandType = CommandType.StoredProcedure
Command.Parameters.Add(
New SqlClient.SqlParameter("@Input", SqlDbType.VarChar, 20))Command.Parameters("@Input").Value = "' OR 1 = 1 --"
You'd actually select all rows from the table, or in other words, a SQL injection attack is possible. And while you should always validate user input as early as possible in your application, a string like that might be considered valid in many scenarios.
Note: This is with .NET 1.1 and SQL Server 2000. I have not tested this on other platforms. I am especially eager to find out what would happen in .NET 2.0 and SQL Server 2005, but I have not had a chance yet to install SQL Server 2005.
[1] http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch12.asp