Preventing SQL Injections With Parameterized Queries

In my previous post I wrote about a few steps that you can take to better secure your website. One of those steps, probably the most important on that list, was to use Parameterized Queries whenever you're mixing SQL and user input in order to avoid SQL injections. We all want to trust our websites visitors and in a perfect world we could save precious time by not worrying about such silly things. However, in our current world we have to spend this extra time. If there is a flaw somewhere, someone will eventually try to expose it and some may even attempt to abuse it.

Parameterized Queries are your defense against SQL injection attacks. SQL injections occur when you mix execution of your SQL statements with content entered by users without properly sanitizing the content entered by your users and thus you end up executing whatever commands a user wants. For example, let's take a forgot password page on a website. Internally, you have to look into your database and determine whether the email that was entered matched a valid user in your system. That could be done with something like the following:

SqlConnection connection = new SqlConnection(connectionstring);
SqlCommand command = new SqlCommand();
string sql = "SELECT COUNT(*)* FROM User WHERE Email = "'" + txtemail.Text + "'";
int result = 0;
connection.Open();
result = (int)command.ExecuteScalar();
connection.Close();

Simple enough and more developers than not will usually take this approach. A user enters their email into an input box, we take that input that append it to our SQL statement above and it works.

However, by doing so we open up our website to attacks. If a user wanted he could simply test out the security of your website by entering the following as input:

someemail@blah.com' or 'x' = 'x

This would create the following SQL statement:

SELECT *
FROM User
WHERE Email = 'someemail@blah.com' or 'x' = 'x'

And while not malicious in it of itself, running this command tells the user something very important about your website, and that is that it is ready for a SQL Injection. An attacker could spend some time with this guessing a few table names and a few column names and in surprisingly no time at all, could end up deleting your databases or assuming someone's identity and stealing private data. It happens all the time. And it's just a matter of time before someone went ahead and tried:

someemail@blah.com'; DROP TABLE User;

Parameterized queries are there just for that purpose. Instead of concatenating strings ourselves, we'll use placeholders in our SQL statements and then specify the parameters that will get injected into those placeholders. So let's go over some implementations of them using various ASP.NET technologies.

ASP.NET Web Forms

For basic ADO.NET SQL functions, .NET offers us the SqlParameter class, which represents, you guessed it, a Sql Parameter. They take a name and value pair which will correspond to the placeholder name that we specify in our SQL statement. For example:

    SELECT * FROM User WHERE Email = @Email;

The SqlParameter for the Email field would be defined as follows:

    SqlParameter param1 = new SqlParameter("Email", txtemail.Text);

The SqlCommand object contains a collection of SqlParameter objects that we can add to.

    SqlConnection connection = new SqlConnection();
    SqlCommand command = new SqlCommand();

    string sql = string.Format("SELECT COUNT(*) FROM User WHERE Email = @Email");
    SqlParameter param1 = new SqlParameter("Email", txtId.Text);

    connection.ConnectionString = GetConfig("defaultconnectionstring");
    command = new SqlCommand(sql, connection);
    command.Parameters.Add(param1);

    connection.Open();
    int result = (int)command.ExecuteScalar();
    connection.Close();

Though a bit more work, we've now plugged a very serious vulnerability in our system. We can add as many parameters as we need and just keep adding them to our command object's Parameters collection without a problem and without thinking twice about what kind of content is being added.

ASP.NET Web Pages 2.0

Web Pages 2.0 makes it a bit easier to work with parameterized queries by having it build into the WebMatrix namespace classes. You can specify parameters in your SQL statements with the '@' symbol and the order number of the parameter in the SQL statement as follows:

var query = "SELECT * FROM User WHERE Email = @0";
var db = Database.Open("databasename");
var result = db.QueryValue(query, txtemail.Text);

It's a much simpler process in Web Pages 2.0 as you can see above. But parameters pretty much get treated the same in both types of pages, except that in Web Forms the parameters must match the names given, such as @Email, whereas in Web Pages it's based on it's order in the list.

It's such a simple process, but one that all programmers have ignored at some point, and one that future programmers will continue to ignore. So hopefully this post helps prevent some websites out there from becoming victims at some point in its lifetime and will save someone the headaches of dealing with the aftermath. Happy coding!