Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  Deployment  »  Beyond Stored Procedures: Defense-in-Depth Against SQL Injection
 »  Home  »  Security  »  Beyond Stored Procedures: Defense-in-Depth Against SQL Injection
 »  Home  »  Web Development  »  Beyond Stored Procedures: Defense-in-Depth Against SQL Injection
 »  Home  »  Web Development  »  ASP.NET  »  Beyond Stored Procedures: Defense-in-Depth Against SQL Injection
 »  Home  »  Web Development  »  Mobile Devices  »  Beyond Stored Procedures: Defense-in-Depth Against SQL Injection
 »  Home  »  Web Development  »  Web Services  »  Beyond Stored Procedures: Defense-in-Depth Against SQL Injection
Beyond Stored Procedures: Defense-in-Depth Against SQL Injection
by Bryan Sullivan | Published  07/17/2006 | Deployment Security Web Development ASP.NET Mobile Devices Web Services | Rating:
Bryan Sullivan
Bryan Sullivan is a development manager at SPI Dynamics, a 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 12 years of experience in the information technology industry. Bryan is currently coauthoring a book with noted security expert Billy Hoffman on Ajax security, which will be published in summer 2007 by Addison-Wesley. 

View all articles by Bryan Sullivan...
Countering SQL Injection Attacks

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” (http://msdn2.microsoft.com/en-us/library/ms161953.aspx).

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();
selectUserCommand.CommandText = “SELECT * FROM Users WHERE UserID = ‘”  + userId.Value + “’ AND Password = ‘” + password.Value + “’”;
  selectUserCommand.Connection = conn;

  conn.Open();
  SqlDataReader reader = selectUserCommand.ExecuteReader();
  SqlContext.Pipe.Send(reader);
  reader.Close();
  conn.Close();
 }
}

Even if you are the one writing the stored procedures, you usually cannot be sure that someone else will not come behind you and change them after the application has been deployed. This is especially true regarding Web applications and is why a defense-in-depth strategy can help.

Clearly, the solution to the problem is to adopt a defense-in-depth strategy. You should continue to use stored procedures and parameterized queries whenever possible, but you should also take steps in establishing your defense-in-depth strategy to ensure that the parameters passed to those stored procedures and queries are validated. In our user authentication example above, “bobsmith” may be a valid user ID, but “SELECT * FROM tblCreditCards” is probably not. A good way to use your defense-in-depth strategy to validate user input is to apply regular expression rules to it. You can use the RegularExpressionValidator control found in the System.Web.UI.WebControls namespace to validate Web form data, and you can use the Regex class found in the System.Text.RegularExpressions namespace to validate any kind of text data. Here is an example of a Web form that validates the user input before passing it off to the database:

protected void Page_Load(object sender, EventArgs e)
{
 if (Page.IsPostBack)
 {
  // We allow only alphanumeric input
  Regex allowRegex = new Regex(“^[a-zA-Z0-9]*$”);
  if ((!allowRegex.IsMatch(textBoxUserId.Text)) || (!allowRegex.IsMatch(textBoxPassword.Text)))
  {
   labelErrorMessage.Text = “Invalid user ID or password.”;
   return;
  }
  else
  {
   // Call the login stored procedure
   …
  }
 }
}

An even more thorough defense-in-depth strategy is to use a combination of allowed-input patterns (also known as “whitelists”) and denied-input patterns (or “blacklists”). The user’s input must match the whitelist pattern (or at least one of the whitelist patterns, if there is more than one) and not match the blacklist pattern (or any of the blacklist patterns). You should definitely consider using blacklist patterns with your defense-in-depth strategy if you allow non-alphanumeric input such as apostrophes in your allowed-input list.

// We allow alpha characters, spaces, and apostrophes as input
Regex allowRegex = new Regex(@“^[a-zA-Z\s\’]*$”);
// But we disallow common SQL functions
Regex disallowRegex = new Regex(“(union|select|drop|delete)”);
if ((!allowRegex.IsMatch(textBoxLastName.Text)) || (disallowRegex.IsMatch(textBoxLastName.Text)))
{
  labelErrorMessage.Text = “Invalid name.”;
return;
}

Finally, we have to address the question of adding damage control to your defense-in-depth strategy. If a hacker did find a way to execute SQL commands against your database, what kind of damage could he do? If your application connects to the database as an administrative user, such as “sa” for Microsoft SQL Server, the damage could be severe indeed. Not only could he view the data in the tables, he could add new data of his own, or change the values of the existing data. Imagine an online shopping site where all items have had their price marked down to a penny. He could add new users or remove existing users. He could delete rows, tables, or even the entire database. You can alleviate this risk by applying the principle of least privilege to your defense-in-depth strategy: make your application connect to the database as a user who has just enough permissions to perform the actions required, and no more. If your application only reads data from a database, remove the insert, update, and delete permissions for the database user. If the application only needs access to a product catalog database (for example), make sure the user has no access to the order history database. Never specify “sa” or any administrative user as the database user.

By adopting a defense-in-depth strategy, you can avoid most or all of the damage that a SQL Injection attack can cause to your application. It is a great idea to use stored procedures for many reasons, including improved security, but do not rely on them to provide all of your security. Always validate user input, and apply the principle of least privilege to minimize the damage that a successful attack can cause.

About the Author
Bryan Sullivan is a development manager at SPI Dynamics, a 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. 

How would you rate the quality of this article?
1 2 3 4 5
Poor Excellent
Tell us why you rated this way (optional):

Article Rating
The average rating is: No-one else has rated this article yet.

Article rating:3.17391304347826 out of 5
 23 people have rated this page
Article Score18289
Sponsored Links