Using parameterized SQL queries
posted by Jeff
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
' 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",
SqlDataReader objReader = objCommand.ExecuteReader();
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
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.
©2014, POP World Media, LLC. All rights reserved
Legal, privacy, terms of service