Avoiding SQL Injection

One of the more serious threats you'll encounter when exposing a Web application on the Internet is the SQL injection attack. A hacker launches this type of attack by adding extra information to an input field. Your code then interprets this extra input as part of the SQL that it sends to the database.
For example, Figure 14-1 shows an input form in which a hacker is attempting an attack by inserting the following string: ', ''); drop table MyTable;--
Figure 14-1
The hacker is hoping that the code does something insecure, such as concatenate the input with the SQL statement to build the SQL string on the fly. In many situations, this is exactly what is happening in the code, and this section provides some advice about how to avoid it.
Important
With SQL injection, it isn't too hard for attackers to quickly extract data and then figure out your entire database schema. To see how they do it, visit your favorite search engine, starting with the keywords "SQL injection."
In Figure 14-1, the example has two buttons, Bad Add Shipper and Good Add Shipper. The hacker hopes the code like that shown in Listing 14-1 executes when he or she submits the page. The Bad Add Shipper button will run the code in Listing 14-1.
Listing 14-1: The wrong way to build an ad hoc SQL query
protected void btnBadAddShipper_Click(object sender, EventArgs e)
{
string connStr = "Server=(local);Database=Northwind;Integrated
Security=SSPI";
// this is *bad* because the user can
// enter anything they want
string cmdStr =
"insert into Shippers (CompanyName, Phone) values ('" +
txtCompanyName.Text + "', '" + txtPhone.Text + "')";
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
The code in Listing 14-1 is bad is because it concatenates the input with the rest of the SQL statement. This causes the resulting SQL command to be sent to the database as follows:
insert into Shippers (CompanyName, Phone) values ('', ''); drop table MyTable;--',
'')
The first part of this statement adds a new row with blank values to the Shippers table. The second part, after the first semicolon (;), removes the MyTable table from the database. The rest of the statement, after the second semicolon, is commented out to prevent errors.
As you can see, attackers can do a lot of damage to your system or view information they aren't supposed to see. Even worse, although it may not be totally obvious by this example, through SQL injection an attacker could potentially take over your entire system.
To fix this problem, you need to use parameters, as shown in Listing 14-2. When the user clicks the Good Add Shipper button, shown in Figure 14-1, it runs the btnGoodAddShipper method, shown in Listing 14-2.
Listing 14-2: The proper way to build an ad hoc SQL query
protected void btnGoodAddShipper_Click(object sender, EventArgs e)
{
string connStr = "Server=(local);Database=Northwind;Integrated
Security=SSPI";
// this is good because all input becomes a
// parameter and not part of the SQL statement
string cmdStr =
"insert into Shippers (CompanyName, Phone) values (" +
"@CompanyName, @Phone)";
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
{
// add parameters
cmd.Parameters.AddWithValue("@CompanyName", txtCompanyName.Text);
cmd.Parameters.AddWithValue("@Phone", txtPhone.Text);
conn.Open();
cmd.ExecuteNonQuery();
}
}
The code in Listing 14-2 is more secure because all input is treated as a parameter, rather than part of the SQL statement. Refer to the documentation on SqlParameter, or the corresponding type for whatever data provider you're using, for information on how to use parameters. An even more secure example would have instantiated a SqlParameter and explicitly set type, size, and other arguments to constrain the input.
ASP.NET v2.0 includes new datasource controls that enable you to add a bindable object to your Web form for populating data controls, such as GridView. These datasource controls accept parameters, meaning that they are also a safe way to get input from the user. For more information, see Chapter 8, "Extreme Data Binding."
Important
In addition to increasing performance, stored procedures are more secure because they require passing input as parameters.
To re-create this scenario, add a test table to your database, such as the one shown in Listing 14-3, and add input as shown in Figure 14-1. Clicking the Bad Add Shipper button deletes the table. However, clicking the Good Add Shipper button processes the input properly.
Listing 14-3: Example table for demonstrating a SQL injection attack
create table MyTable
(
TempColumn char(5)
)
Figure 14-2 shows the output in SQL Query Analyzer after running a couple of queries. Row 4 shows the results of running the insecure code from Listing 14-1 (clicking the Bad Add Shipper button). Row 5 shows the results of running the more secure code from Listing 14-2 (clicking the Good Add Shipper button). As you can see by looking at Row 5, processing input via parameters prevents interpretation of the input as part of the SQL statement and saves that input as column data.
Figure 14-2
If you are coding ad hoc SQL statements, using parameters is one of the best techniques you can use to increase the security of your entire application.