A few years ago, mentioning the phrase “SQL Injection” to developers or asking to adopt a “defense-in-depth” strategy would probably get you a blank stare for a reply. These days, more people have heard of SQL Injection attacks and are aware of the potential danger these attacks present, but most developers’ knowledge of how to prevent SQL Injection is still inadequate, and when asked how to defend their applications against SQL Injection, they usually reply, “That’s easy, just use stored procedures.” As we will see, using stored procedures is a great first step for your defense strategy, but is not sufficient as the only step. You need to adopt a defense-in-depth strategy.

If you are not familiar with SQL Injection attacks and their potential for danger to your applications, please see the MSDN article SQL Injection.

The problem with exclusively relying on stored procedures and not implementing a defense-in-depth strategy is that you are really just counting on the developer of the stored procedures to provide your security for you. Stored procedures, similar to the following SQL Server code used to authenticate a user, are fairly common:

ALTER PROCEDURE LoginUser
(
@UserID [nvarchar](12),
@Password [nvarchar](12)
)
AS
SELECT * FROM Users WHERE UserID = @UserID AND Password = @Password
RETURN

That stored procedure looks pretty secure, but consider this one:

ALTER PROCEDURE LoginUser
(
@UserID [nvarchar](12),
@Password [nvarchar](12)
)
AS
EXECUTE (’SELECT * FROM Users WHERE UserID = ”’ + @UserID + ”’ AND Password = ”’ + @Password + ””)
RETURN

By creating an ad-hoc SQL statement and passing it to the EXECUTE function in the code of stored procedures, we can actually create SQL-injectable stored procedures. This is even easier to do when you use managed code to write stored procedures, as is newly supported in Microsoft SQL Server 2005:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void LoginUser(SqlString userId, SqlString password)
{
using (SqlConnection conn = new SqlConnection(”context connection=true”))
{
SqlCommand selectUserCommand = new SqlCommand();
 Web application security products company. Bryan manages the DevInspect and QAInspect Web security products, which help programmers maintain application security throughout the development and testing process. He has a bachelor’s degree in mathematics from Georgia Tech and 11 years of experience in the information technology industry. He also contributed to the AVDL specification, which has become a standard in the application security industry.

[tags]sql injection attack,bryan sullivan,devinspect,qainspect,stored procedure[/tags]