SQL Injection And Parameterized Queries

If you post data access code to the forums as part of a question, and people reply with "Watch out for SQL Injection!", or "Use parameter queries!", you may wonder why they seem so concerned. Chances are that you are building your SQL statement dynamically, by concatenating values supplied by users, and then executing the result against the database. If you are doing this, you are opening your application up to SQL Injection attacks.

SQL Injection

SQL Injection is a technique that exploits security vulnerabilities in a database-backed application, by "injecting" code that alters the intended behaviour of the SQL command to be executed. The easiest way to become a victim of this is if you allow the user to provide part of the SQL command to be executed, by taking what they entered into TextBox controls or similar, and building your SQL statement around this input without checking it.

Consider the following example, which is typical of what could be found in a form called ProductDetails.aspx, detailing individual products from within the NorthWind sample database:

1.  using (SqlConnection conn = new SqlConnection(NorthwindConnectionString))  

2.  {  

3.    string query = "SELECT * FROM Products WHERE ProductID = " + Request.QueryString["Id"];  

4.   SqlCommand cmd = new SqlCommand(query, conn);  

5.   conn.Open();  

6.   using (SqlDataReader rdr = cmd.ExecuteReader())  

7.   {  

8.    DetailsView1.DataSource = rdr;  

    DetailsView1.DataBind();  

  }  

}

 

This example takes the value that is passed within the Id portion of the Querystring, which is a common way to pass the value of the selected product from one page to another. It concatenates that value with the hardcoded portion of the SQL string, resulting in a valid SQL statement ("SELECT * FROM Products WHERE ProductID = 3"). This is then executed against the database, and the results are returned and bound to a DetailsView. All being well, ProductDetails.aspx displays details of Aniseed Syrup. However, what if the user alters the querystring within their browser address bar so that it became this:

http://www.mysite.com/ProductDetails.aspx?Id=3 or 1 = 1

and then hits F5? What happens is that the user gets to see details of Chai Tea instead of Aniseed Syrup. They have successfully injected additional valid SQL characters so that when they are added to the hardcoded portion of the command text, the following gets executed against the database:

"SELECT * FROM Products WHERE ProductID = 3 or 1 = 1"

Now consider the following amended querystring:

http://www.mysite.com/ProductDetails.aspx?Id=3;UPDATE Products SET ProductName = 'You''ve been hacked!'--

When Request.QueryString["Id"] is concatenated to the hard-coded string, the SQL command text becomes this:

"SELECT * FROM Products WHERE ProductID = 3;UPDATE Products SET ProductName = 'You''ve been hacked!'--"

Unfortunately, as far as the database is concerned, that is a perfectly valid SQL command, and will result in all product names being updated to the new value of "You've been hacked!". Even worse could happen. The querystring could have ";DROP TABLE Products--" appended, which will result in the loss of the entire table.

Preventing SQL Injection - Use Parameters

Some people think that just escaping, or doubling single quotes will protect against SQL injection. However, this will only protect against certain forms of SQL injection. The first example contains no single quotes, so would still have been successful. Some people believe that creating a "Black List" of words and symbols to screen user input against is good enough protection. The black list approach involves filtering user input against a list of known SQL key words and syntax (such as DROP, EXECUTE, ;, -- etc), and rejecting the input if any are found. The problem with this approach is that you need different black lists for different user input- there are perfectly valid reasons why people may need to submit values that contain semi-colons, or the word "drop". You also have the job of maintaining the black lists to ensure that they are comprehensive, and kept up to date.

The best defence against attacks is to parameterize your queries. Parameters are placeholders for values. Altering the first example to make use of parameters gives us this:

1.  using (SqlConnection conn = new SqlConnection(NorthwindConnectionString))  

2.  {  

3.    string query = "SELECT * FROM Products WHERE ProductID = @Id";  

4.   SqlCommand cmd = new SqlCommand(query, conn);  

5.   cmd.Parameters.AddWithValue("@Id", Request.QueryString["Id"]);  

6.   conn.Open();  

7.   using (SqlDataReader rdr = cmd.ExecuteReader())  

8.   {  

9.      DetailsView1.DataSource = rdr;  

10.     DetailsView1.DataBind();  

11.  }  

12. }

 

The placeholder - @Id - has become part of the hardcoded SQL. At runtime, the value provided by the querystring is passed to the database along with the hardcoded SQL, and the database will check the ProductID field as it attempts to bind the parameter value to it. This ensures a level of strong typing. If the parameter value is not the right type for the database field (a string, or numeric that's out of range for the field type), the database will be unable to convert it to the right type and will reject it. If the target field datatype is a string (char, nvarchar etc), the parameter value will be "stringified" automatically, which includes escaping single quotes. It will not form part of the SQL statement to be executed.

Have a nice day... 'N happy Coding :)