Hack-Proofing Your ASP.NET Applications

This article will cover SQL injection and parameter tampering, while the second part, which will appear in the January issue, will focus on cross-site scripting and cross-site request forgery.

SQL Injection

What Is It? SQL injection is an attack in which one or more commands are inserted into a query to form a new query that was never intended by the developer. This almost always occurs when dynamic SQL is being used; that is, when you’re concatenating strings in your code to form SQL statements. SQL injection can occur in your Microsoft .NET Framework code if you’re forming a query or procedure call, and it can occur in your server-side T-SQL code as well, such as in the case of dynamic SQL in stored procedures.

SQL injection is particularly dangerous because it can be used not only to query and edit data, but also to run database commands that are limited only by database user or database service account permissions. If your SQL Server is configured to run as an administrator account and your application user belongs to the sysadmin role, be especially concerned. Attacks using SQL injection can run system commands to perform the following:

• Install backdoors
• Transfer an entire database over port 80
• Install network sniffers to steal passwords and other sensitive data
• Crack passwords
• Enumerate your internal network, including scanning the ports of other machines
• Download files
• Run programs
• Delete files
• Become part of a botnet
• Query autocomplete passwords stored on the system
• Create new users
• Create, delete and edit data; create and drop tables

This is not a comprehensive list and the danger is constrained only by the permissions in place—and the creativity of the attacker. 

SQL injection has been around for so long that I’m often asked if it’s still a concern. The answer is absolutely and attackers use it very often. In fact, outside of Denial of Service (DoS) attacks, SQL injection is the most commonly used attack.

How Is It Exploited? SQL injection is generally exploited through direct entry on a Web page or through parameter tampering, which usually involves altering not just forms or URIs—cookies, headers and so forth are also subject to attack if the application uses these values in an insecure SQL statement (I’ll discuss this later in the article).

Let’s look at an example of SQL injection via form tampering. This is a scenario I’ve seen many times in production code. Your code may not be exactly the same, but this is a common way developers check login credentials.

Here’s the dynamically formed SQL statement for retrieving the user’s login:

string loginSql = string.Format("select * from users where loginid= '{0}
 
  ' and password= '{1} '"", txtLoginId.Text, txtPassword.Text);

This forms the SQL statement:

select * from dbo.users where loginid='Administrator' and  
 
    password='12345'

By itself, this isn’t a problem. However, suppose the form field input looks like what’s shown in Figure 1.

Figure 1 Malicious Input Instead of a Valid Username

This input forms the SQL statement:

select * from dbo.users where loginid='anything' union select top 1 *
 
  from users --' and password='12345'

This example injects an unknown login ID of “anything,” which by itself would return no records. However, it then unions those results with the first record in the database, while the subsequent “--”comments out the entire rest of the query so it’s ignored. The attacker is not only able to log in, he can also return a valid user’s record to the calling code without actually having any idea of a valid username.

Your code may not replicate this exact scenario, obviously, but the important point when analyzing your applications is to think about where the values that are included in queries commonly come from, including:

• Form fields
• URL parameters
• Stored values in the database
• Cookies
• Headers
• Files
• Isolated Storage

Not all of these may be immediately obvious. For example, why are headers a potential issue? If your application stores user profile information in headers and those values are used in dynamic queries, you could be vulnerable. These can all be sources for attack if you use dynamic SQL.

Web pages that include search functionality can be exceptionally easy prey for attackers because they provide a direct method to attempt injections.

This can give an attacker almost query editor functionality in a vulnerable application.

People have become far more aware of security issues in recent years, so systems are generally more hardened by default. For instance, the system procedure xp_cmdshell is disabled in SQL Server 2005 and later (including SQL Express) instances. Don’t let this give you the idea that an attacker can’t run commands on your server, though. If the account your application uses for the database has a high enough permissions level, an attacker can simply inject the following command to re-enable the option:

EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'

How Do You Prevent SQL Injection? Let’s first discuss how not to fix this problem. A very common approach to fixing classic ASP applications was to simply replace dashes and quotes. Unfortunately, this is still widely used in .NET applications, often as the only means of protection:

string safeSql = "select * from users where loginId = " + userInput.Replace("—-", "");
 
safeSql = safeSql.Replace("'","''");
 
safeSql = safeSql.Replace("%","");

This approach assumes you have:

1. Protected every single query properly with these types of calls. It relies on the developer remembering to include these inline checks everywhere, instead of using a pattern that protects by default, even after coding all weekend and having run out of caffeine.
2. Typed-checked every single parameter. It’s usually only a matter of time before a developer forgets to check that a parameter from a Web page is, for example, really a number, and then uses this number in a query for something like a ProductId without doing any string checks on it because, after all, it’s a number. What if an attacker changes the ProductId and then it’s simply read from the query string as shown here:

URI: http://yoursite/product.aspx?productId=10

Yields

select * from products where productid=10

And then the attacker injects commands such as:

URI: http://yoursite/product.aspx?productId=10;select 1 col1 into #temp; drop table #temp;

Which yields

select * from products where productid=10;select 1 col1 into #temp; drop table #temp;

Oh no! You’ve just injected into an integer field that wasn’t filtered through a string function or type checked. This is called a direct injection attack because no quotes are required and the injected portion is directly used in the query without quoting. You may say “I always make sure all of my data is checked,” but that puts the responsibility of checking every single parameter manually on the developer and is very open to mistakes. Why not fix it the correct way by using a better pattern throughout your application?

So what’s the correct way to prevent SQL injection? It’s really fairly simple for most data access scenarios. The key is to use parameterized calls. You can actually have dynamic SQL that’s safe as long as you make those calls parameterized. Here are the basic rules:

1. Ensure you’re using only:
   • Stored procedures (without dynamic SQL)
   • Parameterized queries (see Figure 2)

Figure 2 Parameterized Query

using (SqlConnection connection = new SqlConnection(  ConfigurationManager.ConnectionStrings[1].ConnectionString))
 
{
 
  using (SqlDataAdapter adapter = new SqlDataAdapter())
 
  {
 
    // Note we use a dynamic 'like' clause
 
    string query = @"Select Name, Description, Keywords From Product
 
                   Where Name Like '%' + @ProductName + '%'
 
                   Order By Name Asc";
 
    using (SqlCommand command = new SqlCommand(query, connection))
 
    {
 
      command.Parameters.Add(new SqlParameter("@ProductName", searchText));
 
      // Get data
 
      DataSet dataSet = new DataSet();
 
      adapter.SelectCommand = command;
 
      adapter.Fill(dataSet, "ProductResults");
 
      // Populate the datagrid
 
      productResults.DataSource = dataSet.Tables[0];
 
      productResults.DataBind();
 
    }
 
  }
 
}

• Parameterized stored procedure calls (see Figure 3)

Figure 3 Parameterized Stored Procedure Call

//Example Parameterized Stored Procedure Call
 
string searchText = txtSearch.Text.Trim();
 
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString))
 
{
 
  using (SqlDataAdapter adapter = new SqlDataAdapter())
 
  {
 
    // Note: you do NOT use a query like: 
 
    // string query = "dbo.Proc_SearchProduct" + productName + ")";
 
    // Keep this parameterized and use CommandType.StoredProcedure!!
 
    string query = "dbo.Proc_SearchProduct";
 
    Trace.Write(string.Format("Query is: {0}", query));
 
    using (SqlCommand command = new SqlCommand(query, connection))
 
    {
 
      command.Parameters.Add(new SqlParameter("@ProductName", searchText));
 
      command.CommandType = CommandType.StoredProcedure;
 
      // Get the data.
 
      DataSet products = new DataSet();
 
      adapter.SelectCommand = command;
 
      adapter.Fill(products, "ProductResults");
 
      // Populate the datagrid.
 
      productResults.DataSource = products.Tables[0];
 
      productResults.DataBind();
 
    }
 
  }
 
}

1.  2.   Dynamic SQL in stored procedures should be parameterized calls to sp_executesql. Avoid using exec—it doesn’t support parameterized calls. Avoid concatenating strings with user input. See Figure 4.

Figure 4 Parameterized Call to sp_executesql

/*

This is a demo of using dynamic sql, but using a safe parameterized query

*/
 
DECLARE @name varchar(20)
 
DECLARE @sql nvarchar(500)
 
DECLARE @parameter nvarchar(500)
 
/* Build the SQL string one time.*/
 
SET @sql= N'SELECT * FROM Customer  WHERE FirstName Like @Name Or LastName Like @Name +''%''';
 
SET @parameter= N'@Name varchar(20)';
 
/* Execute the string with the first parameter value. */
 
SET @name = 'm%'; --ex. mary, m%, etc. note: -- does nothing as we would hope!
 
EXECUTE sp_executesql @sql, @parameter,
 
                      @Name = @name;

Note because of the lack of parameter support you DO NOT use:  exec 'select .. ' + @sql

1.  3.   Don’t just replace dashes and quotes and think you’re safe. Choose consistent data access methods as already described that prevent SQL injection without manual developer intervention and stick to them. If you rely on an escaping routine and happen to forget to call it in one location, you could be hacked. Moreover, there could be a vulnerability in the way you implement the escaping routine, as can be the case with a SQL truncation attack.
2.  4.   Validate input (see the following Parameter Tampering section) via type checks and casting; use regular expressions to limit to, for example, alpha-numeric data only or pull important data from known sources; don’t trust data coming from the Web page.
3.  5.   Audit your database object permissions to limit application user scope, thus limiting the surface area for attack. Grant permissions such as update, delete and insert only if that user must be able to perform those operations. Each separate application should have its own login to the database with restricted permissions. My open source SQL Server Permissions Auditor can help with this; please check it out at sqlpermissionsaudit.codeplex.com.

It’s very important to audit your table permissions if you use parameterized queries. Parameterized queries require a user or a role to have permissions to access a table. Your application may be protected against SQL injection but what if another application that isn’t protected touches your database? An attacker could start querying away in your database, so you’ll want to make sure that each application has its own unique, restricted login. You should also audit permissions on database objects like views, procedures and tables. Stored procedures only require permissions on the procedure itself, not on the table, generally as long as there’s no dynamic SQL inside the stored procedure, so security is a bit easier to manage on them. Again, my SQL Server Permissions Auditor can help.

Note that the Entity Framework uses parameterized queries behind the scenes and thus is protected against SQL injection for normal usage scenarios. Some prefer to map their entities to stored procedures rather than opening up table permissions for the dynamic parameterized queries but there are valid arguments on both sides, and that’s a decision for you to make. Note that if you’re explicitly using Entity SQL, you’ll want to be aware of some additional security considerations for your queries. Please see the MSDN Library page, “Security Considerations (Entity Framework),” atmsdn.microsoft.com/library/cc716760.

Parameter Tampering

What Is It? Parameter tampering is an attack in which parameters are altered in order to change the application’s expected functionality. The parameters may be on a form, query string, cookies, database and so on. I’ll discuss attacks involving Web-based parameters.

How Is It Exploited? An attacker alters parameters to trick the application into performing an action it wasn’t intending. Suppose you save a user’s record by reading her user ID from the query string. Is this safe? No. An attacker can tamper with a URL in your application in a way similar to what’s shown in Figure 5.

Figure 5 An Altered URL

By doing so, the attacker could load a user account that you didn’t intend. And all too often, application code like the following blindly trusts this userId:

// Bad practice!
 
string userId = Request.QueryString["userId"];
 
// Load user based on this ID
 
var user = LoadUser(userId);

Is there a better way? Yes! You can read values from a more trusted source like a user’s session or from a membership or profile provider rather than trusting the form. 

Various tools make it quite easy to tamper with more than just the query string. I recommend you check out some of the Web browser developer toolbars available to see hidden elements on your pages. I think you’ll be surprised at what’s uncovered and how easy your data is to tamper with. Consider the “Edit User” page shown in Figure 6. If you reveal the hidden fields on the page, you can see a user ID embedded right in the form, ready for tampering (see Figure 7). This field is used as the primary key for this user’s record, and tampering with it alters which record is saved back to the database.

Figure 6 An Edit User Form

Figure 7 Revealing a Hidden Field on the Form

How Do You Prevent Parameter Tampering? Don’t trust user-supplied data, and validate the data you receive that decisions are based on. You generally don’t care if a user alters the middle name stored in his profile. However, you’d surely care if he tampered with the hidden form ID that represents his user record key. In cases like this, you can pull trusted data from a known source on the server rather than from the Web page. This information could be stored in the user’s session upon login or in the membership provider.

For example, a far better approach uses information from the membership provider rather than using the form data:

// Better practice
 
int userId = Membership.GetUser().ProviderUserKey.ToString();
 
// Load user based on this ID
 
var user = LoadUser(userId);

Now that you’ve seen how untrustworthy data from the browser can be, let’s take a look at some examples of validating this data to help clean things up a bit. Here are some typical Web Forms scenarios:

// 1. No check! Especially a problem because this productId is really numeric.
 
string productId = Request.QueryString["ProductId"];
 
// 2. Better check
 
int productId = int.Parse(Request.QueryString["ProductId"]);
 
// 3.Even better check
 
int productId = int.Parse(Request.QueryString["ProductId"]);
 
if (!IsValidProductId(productId))
 
{
 
    throw new InvalidProductIdException(productId);
 
}

Figure 8 shows a typical MVC scenario with model binding that does basic automatic type conversions without having to explicitly cast parameters.

Figure 8 Using MVC Model Binding

[HttpPost]
 
[ValidateAntiForgeryToken]
 
public ActionResult Edit([Bind(Exclude="UserId")] Order order)
 
{
 
   ...
 
   // All properties on the order object have been automatically populated and 
 
   // typecast by the MVC model binder from the form to the model.
 
   Trace.Write(order.AddressId);
 
   Trace.Write(order.TotalAmount);
 
   // We don’t want to trust the customer ID from a page
 
   // in case it’s tampered with.
 
   // Get it from the profile provider or membership object
 
   order.UserId = Profile.UserId;
 
   // Or grab it from this location
 
   order.UserId = Membership.GetUser().ProviderUserKey.ToString();
 
   ...
 
   order.Save();}
 
   ...
 
   // etc.
 
}

Model binding is a great feature of Model-View-Controller (MVC) that helps with parameter checks as the properties on the Order object will automatically be populated and converted to their defined types based on the form information. You can define Data Annotations on your model as well to include many different validations. Just be careful to limit what properties you allow to be populated and, again, don’t trust the page data for important items. A good rule of thumb is to have one ViewModel for each View, so you’d completely exclude a UserId from the model in this Edit example.

Note that I use the [Bind(Exclude)] attrib­ute here to limit what MVC is binding into my Model in order to control what I do or don’t trust. This ensures the UserId won’t come from the form data and thus can’t be tampered with. Model binding and Data Annotations are beyond the scope of this article. I took just a brief look here to show how parameter typing can work in both Web Forms and MVC.

If you must include an ID field on the Web page that you “trust,” please visit the MVC Security Extensions link (mvcsecurity.codeplex.com) for an attribute to assist with this.

Wrapping Up

In this article, I’ve presented two of the most common ways applications are hacked. As you can see, however, the attacks can be prevented or at least limited by making just a few changes in your applications. Of course, there are variations on these attacks and other ways your applications can be exploited. I’ll cover two more types of attacks, cross-site scripting and cross-site request forgery, in the next issue.   

---

Adam Tuliper is a software architect with Cegedim and has been developing software for more than 20 years. He’s a national INETA Community Speaker, and regularly speaks at conferences and .NET user groups. Check him out on Twitter at twitter.com/AdamTuliper, his blog at completedevelopment.blogspot.com or secure-coding.com.

Thanks to the following technical expert for reviewing this article: Barry Dorrans