Preventing SQL Injection Attacks

Introduction

In this article, I will show how to create protection against injection attacks.

Background

An injection attack is one in which the user enters some cleverly written text in an otherwise innocuous input field. This user-entered text can sometimes trick your application into doing unexpected things and could potentially wreak havoc on your system. In this section, we will take a look at a few examples and how to prevent them.

The most common type of injection attack is a SQL injection. Imagine that you've created a login form for use with Forms Authentication. Your code compares a user entered username and password to values in a database, and returns true or false via the following VB.NET code:

dim strSQL as string = "SELECT 1 FROM Users WHERE UserName = '" & _
              txtUserName.Text & "' AND Password = '" & txtPassword.Text & "'"

This statement by itself seems harmless. Now imagine that the user enters the following text into the txtUsername textbox:

'; DELETE FROM Users --

Your original code would then construct the following SQL statements:

SELECT 1 FROM Users WHERE UserName = ''; DELETE FROM Users --' AND Password = ''

MS SQL Server interprets this as two separate statements, and suddenly all of your user data is deleted.

There are a few ways around this issue. First, if you can, always used parameterized Stored Procedures instead of dynamically constructed SQL statements. For example:

dim cmdAuthentificate as new SqlCommand(queryUserAuthentificate)
cmdAuthentificate.CommandType = CommandType.StoredProcedure
cmdAuthentificate.Parameters(new SqlParameter("@Username",txtUsername.Text)
cmdAuthentificate.Parameters(new SqlParameter("@Password",txtPassword.Text)
cmdAuthentificate.Parameters(new SqlParameter("@Result",DBNull.Value)
cmdAuthentificate.Parameters("Result").Direction = ParameterDirection.Output
cmdAuthentificate.ExecuteNonQuery()

In this case, no matter what the user enters in the text boxes, the data is interpreted as literal content and no SQL statement can be injected.

If you can't use a Stored Procedure, you can still use a parameterized dynamic SQL statement:

dim strSQL as string = "SELECT  @Result = 1 FROM Users WHERE UserName = @UserName " &_
           "AND Password = @Password"
dim cmdAuthentificate as new SqlCommand(strSQL)
cmdAuthentificate.Parameters.Add(new SqlParameter("@Username", txtUsername.text))
........
cmdAuthentificate.ExecuteNonQuery()

A third method is to parse out any potentially damaging user-entered data. This can be rather daunting, but often a simple check of apostrophes does the trick:

dim strSQL as string = "SELECT 1 FROM Users WHERE UserName = '" & _
    txtUsername.Text.Replace("'", "''") & "' AND Password = '" & _
    txtPassword.Text.Replace("'", "''") & "'"