Preventing SQL Injection Attacks

Keep your code secure against intruders. In this article we provide examples of SQL injection attacks and how you can write code to prevent them. Stop people from getting information from your database.

In a previous article we provided some examples of how intruders will try to attack your site using Cross-site Scripting (XSS) attacks. In an XSS attack, the attacker tries to use client-side methods of injecting client-side script and then high-jacking a user's session. Now, we're going to provide some examples of a server-side attack where an intruder will try to obtain information from within your database. After the examples, we will go through methods of securing your code against these types of attacks.

SQL injection attacks take advantage of code that does not filter input that is being entered directly into a form. Susceptible applications are applications that take direct user input and then generate dynamic SQL that is executed via back-end code. For example say you have a logon form that accepts a user name and password. Once authenticated against the database, the application then sets a session value, or some other token for allowing the user to access the protected data.

Take a logon form for example, here you have two basic form elements, a textbox for accepting a user name, and a password box for the password.

Then in the code behind:

Dim SQL As String = "SELECT Count(*) FROM Users WHERE UserName = '" & _
username.text & "' AND Password = '" & password.text & "'"
Dim thisCommand As SQLCommand = New SQLCommand(SQL, Connection)
Dim thisCount As Integer = thisCommand.ExecuteScalar()

In the previous code block it executes the built SQL script directly, if count is greater than one, then you know the values entered in for the user name and password were the ones matching the database.

Now with that code in the previous example, suppose someone entered the following string into your username text box:

' or 0=0 --

The apostrophe will close the username value being sent to the SQL query, then pass another argument to the SQL query, after the last argument it then comments out the rest of the query using the "--". Since the second argument they entered into your texbox is an "or" statement, the first check on the user name doesn't matter, and since 0 is always going to equal 0 the script will execute successfully and return a positive logon. Guess what? Your intruder now has access to your application.

Ok so maybe they can logon into your application, but what else can they do? Let's take another example of SQL injection, as in the previous example of using the apostrophe to terminate the value, and proceed on to another argument, lets do this, but using something that can really ruin your application's data and day:

'; drop table users --

Definitely something that can ruin your day. Of course this type of an attack you'll probably notice pretty quick. Other SQL commands can then be entered to determine your database's structure, and return all user names and passwords from the database. You make it even easier for the attacker if you do not provide some ambiguous error message and provide the error message returned from .NET. This error message can provide critical information they need to determine what to enter in your form in order to obtain information.

SQL Injection Prevention

One method of preventing SQL injection is to avoid the use of dynamically generated SQL in your code. By using parameterized queries and stored procedures, you then make it impossible for SQL injection to occur against your application. For example, the previous SQL query could have been done in the following way in order to avoid the attack demonstrated in the example:

Dim thisCommand As SQLCommand = New SQLCommand("SELECT Count(*) " & _
"FROM Users WHERE UserName = @username AND Password = @password", Connection)
thisCommand.Parameters.Add ("@username", SqlDbType.VarChar).Value = username
thisCommand.Parameters.Add ("@password", SqlDbType.VarChar).Value = password
Dim thisCount As Integer = thisCommand.ExecuteScalar()

By passing parameters you avoid many types of SQL injection attacks, and even better method of securing your database access is to use stored procedures. Stored procedures can secure your database by restricting objects within the database to specific accounts, and permitting the accounts to just execute stored procedures. Your code then does all database access using this one account that only has access to execute stored procedures. You do not provide this account any other permissions, such as write, which would allow an attacker to enter in SQL statement to executed against your database. Any interaction to your database would have to be done using a stored procedure which you wrote and is in the database itself, which is usually inaccessible to a perimeter network or DMZ.

So if you wanted to do the authentication via a stored procedure, it may look like the following:

Dim thisCommand As SQLCommand = New SqlCommand ("proc_CheckLogon", Connection)
thisCommand.CommandType = CommandType.StoredProcedure
thisCommand.Parameters.Add ("@username", SqlDbType.VarChar).Value = username
thisCommand.Parameters.Add ("@password", SqlDbType.VarChar).Value = password
thisCommand.Parameters.Add ("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
Dim thisCount As Integer = thisCommand.ExecuteScalar()


Finally, ensure you provide very little information to the user when an error does occur. If there is database access failure, make sure you don't dump out the entire error message. Always try to provide the least amount of information possible to the users. Besides, do you want them to start helping you to debug your code? If not, why provide them with debugging information?

By following these tips for your database access you're on your way to preventing unwanted eyes from viewing your data.

By: Patrick Santry, Microsoft MVP (ASP/ASP.NET), developer of this site, author of books on Web technologies, and member of the DotNetNuke core development team. If you're interested in the services provided by Patrick, visit his company Website at Santry.com.