SQL Injection, Part 4B Of 8: Defending Websites

Last time around, I spent some time on things not to do when attempting to secure a website against SQL injection.  Now I’m going to suggest a few things you can do to lock down a site effectively.

Parameterize Your Queries

The single most important thing you can do is parameterize your queries.  If you do absolutely nothing else, this will at least prevent malicious users from manipulating your website forms  and querystrings to perform unexpected and undesirable actions.

Let’s go back to our query code and figure out what we can do differently.

                //I'm too sexy for my data objects.
                using (SqlConnection conn = new SqlConnection("server=localhost;database=AdventureWorks;trusted_connection=yes"))
                {
                    string sql = String.Empty;
                    sql = "select Name, ProductSubcategoryID, ProductCategoryID from Production.ProductSubcategory where Name like '%" + Filter + "%' order by ProductSubcategoryID;";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.CommandTimeout = 30;
                        conn.Open();

                        //We're using a reader, so there's no way that anybody could do anything bad, right?
                        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        gvGrid.DataSource = dr;
                        gvGrid.DataBind();
                        gvGrid.Visible = true;
                    }

                    lblSearchString.Visible = gvGrid.Visible;
                    lblSearchString.Text = Filter;
                }

This code is not parameterized. Instead, the Filter parameter is translated to its underlying string and put directly into the code. This is what allows us to perform SQL injection attacks. So let’s convert this into code which does use parameters correctly.

                //I'm too sexy for my data objects.
                using (SqlConnection conn = new SqlConnection("server=localhost;database=AdventureWorks;trusted_connection=yes"))
                {
                    string sql = String.Empty;
                    sql = "select Name, ProductSubcategoryID, ProductCategoryID from Production.ProductSubcategory where Name like '%@Filter%' order by ProductSubcategoryID;";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        //create a parameter for @Filter
                        SqlParameter filter = new SqlParameter();
                        filter.ParameterName = "@Filter";
                        filter.Value = Filter;

                        //attach our parameter to the SqlCommand
                        cmd.Parameters.Add(filter);

                        cmd.CommandTimeout = 30;
                        conn.Open();

                        //We're using a reader, so there's no way that anybody could do anything bad, right?
                        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        gvGrid.DataSource = dr;
                        gvGrid.DataBind();
                        gvGrid.Visible = true;
                    }

I made two basic changes here. The first change is that the SQL query no longer stops and inserts the value of Filter; instead, I replaced it with @Filter (like you would see in a stored procedure). The second change is that I added a SqlParameter to the SqlCommand. This SqlParameter’s name is @Filter (to match the @Filter parameter in the SQL call), and its value is the Filter we receive. Then, we add the parameter to the SqlCommand, and run it the same way.

For the cost of a few extra lines of code, we have completely eliminated SQL injection.  The following images should help prove this.

The call to drop a table simply gets parameterized.

The call to drop a table simply gets parameterized.

In this first image, we can see that the call to drop the table is still considered part of the filter, instead of breaking off into another statement.  Thus, the “drop table test” part does not execute as it did earlier.

Our UNION standby does not work, either.

Our UNION standby does not work, either.

The next thing you might try, a UNION, also does not work.  This is for the same reason:  you can’t get “outside” the parameter.  As long as the form field text must be “within” a parameter, there is no way to do any harm.

A querystring-based attack is also thwarted.

A querystring-based attack is also thwarted.

Because this defense is in the data layer—right before we make the SQL call—it does not matter which mechanism is used to perform the operation.  In this case, we can see that the querystring is also protected.  If we had web services or WPF applications or Silverlight apps which called the same method, they would also be protected, too.

So how does this actually work?  It is instructive at this point to fire up Profiler and watch the trace as we see what these operations do. The following SQL code is what is sent to SQL Server by ASP.NET when running the querystring attack attempt:

exec sp_executesql N'select Name, ProductSubcategoryID, ProductCategoryID from Production.ProductSubcategory where Name like ''%@Filter%'' order by ProductSubcategoryID;',N'@Filter nvarchar(14)',@Filter=N'bike'' or 1=1--'

In comparison, here is what gets sent to SQL Server by ASP.NET when we run the non-parameterized code:

select Name, ProductSubcategoryID, ProductCategoryID from Production.ProductSubcategory where Name like '%bike' or 1=1--%' order by ProductSubcategoryID;

We can see the difference quite clearly: without a SqlParameter, the code sent to SQL Server is static SQL, but has been malformed in such a way as to have an unexpected and undesirable (from the perspective of the developer) result. On the other hand, adding a SqlParameter makes the SqlCommand executor turn the statement into dynamic SQL, passing along the parameter. In addition, the apostrophe after “bike” gets doubled up to make it no longer able to close a string.

This little difference goes a long way in protecting your websites from attackers. There are a few other things you can do, however, to protect yourself even further.

Use Maximum Field Sizes (And Size Your SqlParameters)

In AdventureWorks, the maximum field length for product subcategories is 50 characters.  Why, then, should your field length allow more than 50 characters?  If you limit the size of a textbox, you limit what bad guys can do.  Say that you only allow 10 characters.  In that case, bad guys could not drop tables, because the phrase “drop table” takes up 10 characters right there.

This advice does not help for free-form text fields or fields which necessarily must allow for plenty of room, but it does limit the potential for harm, as well as providing users a helpful indicator that they have reached the size limit for a field.

As an addendum, I also recommend using fixed parameter sizes.  The above example did not include a maximum field size, and so the dynamic SQL created had a size of nvarchar(14).  If somebody else puts in 15 characters, that will create another plan with a size of nvarchar(15).  This can result in you unnecessarily storing cached plans which are essentially duplicates in all but parameter size.  Instead, use the following code:

SqlParameter filter = new SqlParameter();
filter.ParameterName = "@Filter";
filter.Size = 50;
filter.Value = Filter;

By filling out the size property, every time the SqlCommand sends over this dynamic SQL, it will use the same parameter size, thus keeping you down to one plan in cache:

exec sp_executesql N'select Name, ProductSubcategoryID, ProductCategoryID from Production.ProductSubcategory where Name like ''%@Filter%'' order by ProductSubcategoryID;',N'@Filter nvarchar(50)',@Filter=N'bike'' or 1=1--'

Protect Against HTTP Parameter Pollution

I mentioned the possibility of using HTTP Parameter Pollution earlier. HTTP Parameter Pollution will not allow an attacker to bypass our defenses, as you can see below. This is a Profile trace of what gets translated from the SqlCommand to the SQL Server instance when I try to go to QueryDriven.aspx?search=bike’; drop/*&search=*/ table test;:

exec sp_executesql N'select Name, ProductSubcategoryID, ProductCategoryID from Production.ProductSubcategory where Name like ''%@Filter%'' order by ProductSubcategoryID;',N'@Filter nvarchar(50)',@Filter=N'bike''; drop/*,*/ table test;'

Despite this protection, it may still be a good idea to prevent HTTP Parameter Pollution attacks.  Unfortunately, the only way I know how to do this is with a Web Application Firewall.  On the server side, Request.QueryString already translates the querystring value to a comma-delimited string, so you cannot get it as a base list of objects to see how many records there are which match that value.  This limits your ability to perform defensive operations.

Conclusion (No Longer Tentative)

Parameterization is the key to securing your ASP.NET website against SQL injection.  Even if you have shoddy, injectable SQL code, performing this simple step means that at least you wouldn’t be able to get attacked through your website.  There is some performance cost, given the overhead of dynamic SQL, but it’s small, especially compared to the risk that you can run by using the non-parameterized methods available to you.

One thought on “SQL Injection, Part 4B Of 8: Defending Websites

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s