Defend Your ASP.NET Application from SQL Injection Attacks

We were recently contacted by a company to resolve a security problem when their website began showing adverts and strange content that wasn’t theirs. Something or someone had de-faced their website with lots of spam adverts.

They use paid advertising (mainly Google Ads) to drive traffic to their website, so visitors to the website weren’t getting the information they expected and inbound sales queries dried up for a while.

The website is based on an aged ASP.NET 2.0 Web Forms application that has worked well since it was built 8 years ago by their in-house development team. It’s a database-driven website with details of 200 products (approx.) that are stored and served from a SQL Server database. There’s no ecommerce, transactions, forms or customer data – it’s a simple brochure-like website to showcase their products.

Every evening, the database of product details is updated by another automated service, so the problem resolved itself overnight. That is until it happened again a few days later, which is when we got the call to help identify and fix the issue.

Finding the Problem

The problem was quickly identified as a SQL Injection vulnerability. The product details in the database had been replaced with HTML and Javascript to show the spam adverts. All the expected product information had gone.

There’s an ASP.NET page called ProductDetail.aspx that shows information about each of their products. It’s uses a Query String parameter called “code” to determine which product details are shown. For example, here’s a link to show details for Product Q10001:

https://www.example.com/ProductDetail.aspx?code=Q10001

After checking the weblogs with the excellent LogParser.exe querying tool ^[1], the cause of the SQL Injection problem was found:

Here's a sample weblog entry for a valid request:
[DATE] [IP ADDR] GET /ProductDetail.aspx code=Q10001 443 ...

This is the SQL Injection Attack:
[DATE] [IP ADDR] GET /ProductDetail.aspx code=122+declare+%40s+varchar%284000%29+set+%40s%3Dcast ...

Notice how the Query String parameter is different from the expected format of code=Q10001.

The Query String value was also over 500 characters in length which is both too long and unusual for this website. This encoded Query String value includes a database routine that loops through all tables in the database and replaces any text fields with the spammy content. This StackOverflow post ^[2] has a great explanation.

Fixing the Problem

SQL Injections problems like this aren’t difficult to exploit. Most websites, applicaitons and APIs are being trawled by automated BOTs looking for common vulnerabilities like this all the time. It’s not hard to do.

Preventing this problem is also not hard to do. Any of these 5 simple measures could have avoided this issue:

1. VALIDATION

Never trust anything entered on the Internet and check all values as early as possible. For this website, the Product Codes always start with a Q and are 6 characters long. Adding some simple guard clauses to check it’s a valid product code before querying the Products table would have prevented this SQL Injection problem.

The IsProductCodeValid(..) function was introduced to check the Product Code and only proceeds with the database query if it's an expected format:

    
    protected void Page_Load(object sender, EventArgs e)
    {
        string productCode = Request.QueryString["code"];

        // VALIDATION to Check Product Code is expected format
        if (IsProductCodeValid(productCode) == false)
            Response.Redirect("~/?error=yes");  // VALIDATION fails - redirect to Home page

        // Load Product Details from the Database
        Product product = LoadProduct(productCode);

        // Set Form Values
        PopulateFormWithProductDetails(product);
    }
    

    
    /// VALIDATION - Guard clauses to check if a Product Code is an expected format
    private static bool IsProductCodeValid(string code)
    {
        // Product Code is compulsory
        if (String.IsNullOrEmpty(code))
            return false;

        // Product Codes are always 6 characters
        if (code.Length != 6)
            return false;

        // Product Codes should start with a Q
        if (!code.ToUpperInvariant().StartsWith("Q"))
            return false;

        // include any further checks e.g. must be alphanumeric etc. 

        // Product Code is an expected format
        return true;
    }
    

2. PARAMETERISED QUERIES

The SQL Injection problem succeeded because the database query was constructed by building the SQL statement dynamically which incorporated the problematic database commands passed through the Query String.

Here's the SQL statement that caused the SQL Injection problem:

    string sql = "SELECT * FROM [dbo].[Product] WHERE [Code] = '" +  productCode + "'";
                   

A simple application update to use a parameterised query like the following would have prevented this SQL Injection exploit:

    private static Product LoadProduct(string productCode)
    {
        Product product = new Product();

        string sql = "SELECT * FROM [dbo].[Product] WHERE [Code] = @ProductCode";

        using (SqlConnection conn = new SqlConnection(Options.DBConnString))
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@ProductCode", productCode);

            try
            {
                conn.Open();

                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        product.Code = (string)rdr["Code"];
                        product.Name = (string)rdr["Name"];
                        product.Summary = (string)rdr["Summary"];
                        product.Image = (string)rdr["Image"];
                        product.Details = (string)rdr["Details"];
                    }
                }

            }
            catch
            { }
            finally
            {  
                conn.Close();  
            }

        }

        return product;
    }
    
    

3. DATABASE PRIVILEGES

This website only needed read permission to query the Product table, but it was running with elevated [DBO Database Owner] privilege which it didn’t need. Had a Read-Only database login been used, the SQL Injection would have failed because the problematic database commands required elevated permissions to succeed.

4. WEB SERVER CONFIGURATION

Restricting the maximum length of allowed Query String values (via a simple web.config entry) could have prevented this problem. The problematic Query String was over 500 characters in length, whereas the expected Product Code is much shorter.

By default, IIS allow a maximum query string length of 2048 characters. Setting a lower limit (e.g. 100) could have mitigated this particular attack but would not protect against other SQL Injection attacks with shorter payloads.

5. WEB APPLICATION FIREWALLS

Services like Cloudflare ^[3] offer additional protection through their Web Application Firewall (WAF) that protects a website from the OWASP ^[4] top 10 vulnerabilities and would have prevented this problem.

There’s no need to change hosting when you use a service like CloudFlare WAF. You just update the DNS settings for the domain to direct all web traffic via their security checking service. Only traffic that passes all the WAF security checks is forwarded to your Web server. You'll also need to configure your webserver to only permit traffic that's arrived through CloudFlare.

Further Prevention Measures

Cloud providers like Azure and Amazon Web Services (AWS) also include Web Application Firewalls services that can help protect websites and applications against common threats like this. The company concerned is now considering moving their application from an on-premise server into the Cloud.

There are plenty of automated tools available that can analyse the code to check for potential security issues before they can be exploited. The checks should be run every time the code is changed.

Code reviews with an experienced colleague are also an invaluable tool in helping to mitigate problems like this.

Penetration testing should also be used to regularly check an application and the infrastructure for common security vulnerabilities. Make sure you have written permission before you run any pen. testing services.

Security == Defense in Layers

There have been a number of high-profile security problems because of SQL Injection. Barely a week goes by without a business hitting the headlines because of a security breach.

The code fixes and configuration changes to fix the problem were simple, but the main point is that secure applications and websites require a broader approach to implement better security. Development teams need to work closer with their Infrastructure and DBA teams - as well as partners, to put more of the necessary security layers, practices and procedures in place.

With the introduction of GDPR in May 2018, businesses are going to have to raise their game.

[1] Download LogParser form Microsoft:
https://www.microsoft.com/en-us/download/details.aspx?id=24659

[2] Explanation of the SQL Injection problem on StackOverflow:
https://stackoverflow.com/questions/8380233/can-someone-explain-this-sql-injection-attack-to-me

[3] Cloudflare's Web Application Firewall:
https://www.cloudflare.com

[4] OWASP Top 10 Vulnerabilities:
https://www.owasp.org