Using parameterized SQL queries

posted by Jeff
Discuss this article   Printer friendly

Save yourself from SQL injection attacks and other nasty problems by passing along data in parameters.

By now you're probably familiar with the risk of SQL injection attacks. Just to refresh your memory, this is when a naughty user of your site gets actual SQL statements to execute by way of a form on your page. If you concatenate strings to form SQL commands, you're at risk. Consider this spot of code:

string sql = "SELECT * FROM User WHERE Name = '" + NameTextBox.Text +
   "' AND Password = '" + PasswordTextBox.Text + "'";

Seems innocent enough, right? If someone knows that your code looks like that, you could be in a world of hurt. For example, if the user entered the following in the NameTextBox:

' OR 1=1 --

The actual SQL statement would be:

SELECT * FROM User WHERE Name = '' OR 1=1 --' AND Password = ''

The important part is the double dash, which comments out the rest of the statement. The 1=1 part adds a condition to the select that will include every row, because, well, 1 always equals 1. If you were using this to authenticate a user, they can enter anything and be logged in.

But it could be much worse. If your database connection string is using some privileged account (like, God forbid, the sa account in SQL Server), the naughty user could do more damage by putting a semi-colon (to make the command compound) followed by some DROP command to nuke your entire database, or execute a command line like "format d:" or something equally sinister. That wouldn't be much fun.

There is an easy way to make all of this scary stuff go away, and it might even help you score a boost in performance. If you've used stored procedures, you already know about parameters. You can use them in the straight SQL you're sending to the database as well, and they will make your life easier.

There are several overloads to SqlCommand.Parameters.Add(). At its most complex, you can pass in the parameter name, the SqlDbType, the length, and the actual value for the parameter. We'll take the easy route and show you how to pass in the parameter name and the value. The more complex overloads do perform slightly better, but in my experience the gains aren't significant enough to worry about it.

Let's rewrite the above code to something a little more safe. We'll create our connection and command objects as usual, but before we execute the command, we'll add some parameters to it.

SqlConnection objConnection = new SqlConnection(_ConnectionString);
SqlCommand objCommand = new SqlCommand(
   "SELECT * FROM User WHERE Name = @Name AND Password = @Password",
objCommand.Parameters.Add("@Name", NameTextBox.Text);
objCommand.Parameters.Add("@Password", PasswordTextBox.Text);
SqlDataReader objReader = objCommand.ExecuteReader();
if (objReader.Read())

Why is this safer? Just as with a stored procedure, SQL Server knows that the value of the parameter is not actual code to execute, but rather some variable bit of data to use for a SELECT criteria, or values in an INSERT or UPDATE.

There are some added performance benefits by doing things this way as well. First off, you're not concatenating strings, which generally speaking is a slow and expensive process. Second, you're passing the same SQL command to SQL Server over and over again, changing the parameters and not the actual command itself. When people try to sell you on stored procedures, a frequent benefit given is that the machine has compiled an execution plan and doesn't have to do it each time the sproc is called. It turns out that these "ad-hoc" queries get cached, so frequent use of them gives you the same benefit as with stored procedures. Don't believe me? Run a query similar to the one above over and over again, one as the above ad-hoc query, then again using a stored procedure. You'll get very nearly the same results.