Example of Trusting Input Using Managed Code

Article
11/12/2009

The following code shows an example of trusting input using managed code.

using System.Data.SQLClient;
using System.Data.SQLTypes;public static SqlMoney FreightByCargo(string company) {
SqlCommand cmd = new SqlCommand();   
cmd.CommandText = "select sum(cost) as cost " + 
         "from orders "  +
         "where companyname = '" + company + "'"; return cmd.ExecuteScalar();
}

The following examples show why this code sample is wrong, and how attackers exploit it.

User input: An example of the valid input you expect

Name: Foo Corp
SELECT sum(cost) 
FROM orders 
WHERE companyname='Foo Corp'

Attacker input: What an attacker actually inputs

Name: Blah' or 1=1 --
SELECT sum(cost) 
FROM orders
WHERE companyname='Blah' or 1=1 -- '

In the above example, the bad input is 'Blah' or 1=1 -- '. This input is bad because the attacker can use this to test that SQL statements can be injected into the code using a plain user level privilege.

Very Bad Attacker Input

Name: b' drop table orders --
SELECT sum(cost) 
FROM orders
WHERE companyname= 'b' drop table orders -- '

In the above example, the bad input is b' drop table orders -- . The input is bad because it allows the attacker to inject a SQL statement into drop tables. To accomplish this, however, the attacker must have db_owner level privileges.

Very, Very Bad Attacker Input

Name: b' xp_cmdshell('fdisk.exe') --
SELECT sum(cost) 
FROM orders
WHERE companyname= 'b' xp_cmdshell('fdisk.exe') -- '

In the above example, the bad input is b' xp_cmdshell('fdisk.exe') – . This input is bad because the attacker can use it to execute system-level commands (shell to command line mode). To accomplish this, however, the attacker must be able to connect to your SQL Server databases using the sa level privilege.

A More Secure Managed Code Example

The following code sample shows how you can better prevent the above attacks from succeeding.

using System.Data.SqlServer;
using System.Data.SqlTypes; public static SqlMoney FreightByCargo(string company) {
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select sum(cost) as cost" +
"from orders " + 
"where companyname = @CompanyName"; 
SqlParameter param = cmd.Parameters.Add("@CompanyName", 
company); 
return cmd.ExecuteScalar( ); 
}