Parameterized queries
Stored Procedures rule, because they facilitate manipulating the dataset you get from a database or the way you are inserting or deleting data in a database without changing your application. Or your business layer for that matter. But you can’t always use stored procedures for the actions you would like your application to take. Or maybe your database doesn’t support stored procedures. In that case it’s back to queries for you! And that’s what I would like to talk about… queries. And how to protect them.
Please note: I’m not posting actual code. Normally, I would check the existence of querystring values, check if the ID is actually an integer and so on, but this is just a sample to get my point across. And believe me; I’ve seen pages constructed like this, or worse.
Let’s assume we’re writing a very simple webpage where we want to show the details for a customer. We pass the customer ID in the querystring. That way we can show the customer’s data. An example of such a URL:
URL: bloggingabout.net/rick/custinfo.aspx?custid=12
In the page called custinfo.aspx will be logic to grab the customer ID from the querystring and query the database for the data for this customer. Here’s where the interesting part begins. Let’s assume the query for the customer data is constructed as follows:
SqlClient.SqlCommand cmd = new SqlClient.SqlCommand();
cmd.CommandText = “Select * from customers where customers.customerID = “ + Request.QueryString[“custid”];
This works like a charm, as long as you have other pages redirecting to this page using only valid customer ID’s. Now think about what will happen when the visitor of a site sees this URL, and decides to change it to this:
URL: bloggingabout.net/rick/custinfo.aspx?custid=12;Delete from customers
query: Select * from customers where customers.customerID = 12;Delete from customers
You can imagine the impact of this query: first all data for customer with id 12 will be fetched. Next, your customers table will be emptied. And by changing everything after the semicolon the visitor is able to manipulate the entire database! This should never be possible. One of the ways to protect your (web) application from SQL injection is using parameterized queries:
SqlClient.SqlCommand cmd = new SqlClient.SqlCommand();
cmd.CommandText = “Select * from customers where customers.customerID = @custid“;
cmd.Parameters.Add(“@custid”, int.Parse(Request.QueryString[“custid”]));
No matter what is inserted in the querystring, the parameterized query sees the entire value as the value that should be assigned to the parameter and makes sure it gets it all.
FYI: in this example there would be an error (FormatException) when the user enters the wrong value for the querystring, because int.Parse wouldn’t be able to parse the querystring to an integer. Unfortunately you can’t rely on this kind of errors to stop all SQL injection so parameterized queries would be the way to go. ;)