Security Vulnerability of the Week(?) #1: SQL Injection

10:36 AM j. montgomery 5 Comments

I’ve been building a backlog of security vulnerabilities I’ve run across in ASP.NET over the past few years, whether from a web code assessment or something I’ve stumbled upon in our different source code repositories at contracting gigs. I plan to post variations of these vulnerabilities here on a semi-regular basis to help educate developers of these problems as well as how to work through to a good solution . The issues I run into are typically common OWASP top 10 type vulnerabilities, meaning they are well known, and are typically the top two – which are Cross Site Scripting (XSS) and Injection Vulnerabilities.
From observing the developers who typically create these vulnerabilities, I’ve become more and more convinced that there is a systematic problem with the software engineering field – the first person that turned me on to the idea that this was so wide-spread was David Rice, in his book Geekonomics: The Real Cost of Insecure Software. I don’t plan to rehash or attempt to make his case all over again, as I feel the book did an excellent job of not only describing the problem, but offering some potential solutions to the problem as well – so I’ll leave that for you to read – and I do think it’s well worth the read – both technical and non-technical individuals alike..
What has puzzled me is how often I run into pockets of developers who lack familarity of common software vulnerabilities (aka Unforgivable Vulnerabilities) and the corresponding techniques used to protect applications. Sometimes this issue is caused by the nature of how an organization may function - perhaps they have never have been exposed to this important information, or othertimes there is a mixed team where there's a disconnect in information sharing - some inability to communicate/win over their peers who are unaware of the issues. If a developer does have an awareness of the problems, it's common to implement methods that only take into account a subset of the issues.
To demonstrate what I mean, here’s a SQL Injection example:
Assume the string lastName below comes directly from a TextBox on an ASP.NET web page:
string sqlQuery = "select userId, fname, lname, address1, address2,
          city, state, zip from user where lname = '" + lastName + "'";
At runtime, under normal use, it will generate SQL like so:
select userId, fname, lname, address1, address2, city, state, zip
from user where lname = 'Monty' 
and return all the users who have the last name equal to Monty. By simply putting an extra single-quote in the TextBox, a situation is created that the developer didn’t account for which might cause a couple of different issues:.
1. A normal user attempts to find people with the last name of O’Brian which generates the following SQL at runtime:
select userId, fname, lname, address1, address2, city, state, zip
from user where lname='O'Brian'
To the uninitiated, this is a bug – plan and simple – the code above will error out and present the user with your error page, or even worse, the exact details of the error that occurred (which will give an attacker more information that helps them further exploit your site).

2. A malicious user decides to see if your site has a vulnerability and types the following into the TextBox on the web site:
‘ OR 1=1 --
Which at runtime, generates the following SQL:
select userId, fname, lname, address1, address2, city, state, zip
from user where lname = '' OR 1=1--'
For those not familiar with injection concepts, the SQL looks kind of odd and maybe like it has a syntax error, but for those familar with the issue, you immediately recognize the issue - the comment in SQL is two dashes. It excluded the leftover single-quote at the end of the line. The malicious user has been able to insert whatever that want at the end of your SQL Statement on the SQL Server. It’s worth pointing out that the above ‘exploit’ isn’t necessarily malicious in its current form…but it’s not destructive. What if we had appended a DELETE command after it, or executed a system stored procedure that may gain access to the host operating system, etc.?

Here are some different reactions you may hear (and i have certainly experienced all of these first hand) from some misguided developers and perhaps managers:
  1. Response #1; Most developers will just move to solve this ‘bug’ and often implement the following fix, which seems reasonable to them - by simply escaping any single-quote they encounter with two – more on that later:
    string sqlQuery = "select userId, fname, lname, address1, address2, city,
                       state, zip from user where lname = '" +
                       lastName.Replace("'", "''") + "'";
    And this does in fact solve the obvious issue in view by generating the above fix renders the following SQL at runtime, when there's malicious input:
    select userId, fname, lname, address1, address2, city, state, zip
    from user where lname = ''' OR 1=1--'
    Which successfully escapes the single-quote so the entire input by the user is accepted in the where constraint, fixing the immediate apparent issue – both the injection vulnerability and the “O’Brian” issue.
  2. Response #2: The site is only used by people in our company, they aren’t malicious so I don’t have to worry about protecting against it. AND/or
  3. Response #3: The users at our company aren’t bright enough to do that – they’d have to know SQL AND know about injection.
  4. Response #4: Anger, defensiveness, arrogance, and/or stubbornness in the face of the facts.
  5. Response #5: So what, they can add more to the where constraint – big deal, it’s a search page anyway.
  6. Response #6: "This hasn’t ever happened before" or "We haven’t been hacked yet."
  7. Response #7: We don’t have anything that interesting that a hacker would want.

If you’ve heard any of these or anything I’ve missed please leave a comment about it.

The response we HOPE a developer will have is an open mind leading to the “Ah-HA” moment, followed by a bit of self-education to understand the scope of the issues followed by an understanding of the solutions to the problem.

This leaves me with a few questions:

What isolates the pockets of developers from the rest of the industry (i.e. guidance from Microsoft, OWASP, SANS Institute, and a hundred other web sites that talk about this, etc) who all have been working hard to educate them to prevent these kinds of problems in the future? Why do some developers go down the path to understand and resolve the issue and others don’t?

One thought is that there’s really no actual foundational knowledge required to be a software engineer – See Rice’s book Geekonomics for more information on solutions to that issue.

What’s the best reply to the responses above?

Response #1 – Quick fix

I can understand response one, because on the surface it appears there is a simple bug, and the quick and easy way to resolve the bug it is to escape any quotes that show up in the where constraint value. The problem with this response is that it doesn’t fully understand the issue – it’s not is issue of unregulated quotes appearing in user input, though that is part of the problem.

Lets expand the SQL Injection example further to demonstrate why this is a misguided fix. Let’s change the query as follows:
string sqlQuery = "SELECT userId, fname, lname, address1, city, state, zip
         FROM user WHERE userId = " + Request.QueryString["userId"];
At runtime, based on the following inputs to the URL, let’s watch what happens:
For the URL:

Renders the following SQL:
SELECT userId, fname, lname, address1, city, state, zip
FROM user WHERE userId = 1
And the URL:
SELECT userId, fname, lname, address1, city, state, zip
FROM user WHERE userId = 1 OR 1=1
It’s obvious that the previous fix escaping the single-quote won’t help us in this situation at all -
Request.QueryString["userId"].Replace("'", "''");
So the developer might go ahead and quickly fix this specific issue by casting the QueryString value to an int first and if no exception occurs then the value is good, which would be a reasonable approach, but at this point for any given query, the developer must foresee all potential inputs and write a specific fix for each type of query.

This is less then ideal.

Another additional factor to consider– MySQL allows a backslash (\) to be used to escape a single-quote so simply escaping a single-quote with two single-quotes won’t be sufficient in all situations as malicious input could take this into account and create another injection scenario, requiring an additional fix. This also brings another factor to light – based on the backend database, the rules change, so it would be better to find a way to fix the data that works universally from within ASP.NET.

Based on the above two scenarios, some developers would argue that in their situation, a particular custom fix they’ve invented is sufficient, and they may be correct for any given vulnerability. However, the industry as a whole needs to rely on a commonly accepted approaches agreed upon by a variety of different people who are smarter then the common masses of developers and who are also an authority on the topic (e.g. Microsoft’s own guidance on fixing SQL injection in ASP.NET, for instance). The reason is this, you’ve only just learned about the issue and even if you’ve been aware of these issues for a while, there may be particular situations you are unaware of – meaning any fix you come up with is likely to be short-sighted – you may miss a particular case that you are unaware of – and even if you are aware of all current possible issues, new approaches to bypass them are sometimes discovered. A couple others reason to use consistent secure coding practices – the “hit by a bus factor” – another developer may inherit your code. Will they get your approach to fix the vulnerability? If you’re not there to explain it, your fix may not be apparent. If the problem is solved using a standard approach – the next developer looking at your code has a much greater chance of understanding.

Response #2 and #3 – Our Employees are Nice / Unknowledgeable

There is debate over whether most attacks are done by insiders (employees) or people external to the company. Some studies claim high percentages from insiders – 60% and up however other studies put it at 18%. But it doesn’t really matter. If any percentage of incidents come from insiders, you will need to protect against it.

Also, the claim that your companies employees aren’t malicious is at best a guess and it doesn’t take much for an disgruntled employee to act out…and an assumption that they aren’t smart enough is mostly likely baseless as well. It’s not worth staking the security of your systems on that – especially if you have to explain to your shareholders why you didn’t do a better job fixing something the industry has know about for over 10 years.

Response #4 – Defensiveness

I can only partially understand this perspective – I suppose if I’m feeling defensive on a particular day, this could be my reaction at first – however, it's dangerous to maintain denial and can also hurt credibility and put the organization at risk. I’m not sure there’s a good response to this unless a manager or lead can force the issue.

Response #5 – So What’s the Big Deal?

This is not a necessarily a bad response if there’s a willingness to hear the answer. “What IS the big deal anyway? A user can add additional conditions onto the query, so what…” To properly expand on this topic, more information about what can be done in an attack is necessary.

Let’s expand the previous example and take into consideration MS SQL Server and Oracle…as there’s slightly different behavior between the them. For instance, the .NET Oracle Client limits multiple commands whereas in SQL Server, you can string together as many as you’d like just by delimiting them with a semi-colon.

Response #6 – It’s Never Happed Before – We Must Be Safe!

Really? It’s never happened? This answer is absurd when you think about it. If the knowhow to protect against a vulnerability is lacking, then it’s incredibly unlikely there was the knowledge and foresight to detect the vulnerability being exploited and report on it. My response is always – how you would know if it were? What mechanism in the system is used to report on an exploit of this vulnerability? This often leads to an “Oh Shit” moment when they realize that they may already have been hacked and have no way of knowing.

Response #7 – We’re Not Interesting

This may be true, but there’s plenty of hackers that don’t care. It’s true that a better target would be someone with something to steal, but if a hacker can exploit your network, they can launch further attacks on these more interesting targets from your locations. There’s nothing like being an unknown accomplice to someone else's crime.

The Solution

So to go through all this and not actually discuss the best way to protect against SQL Injection wouldn’t be cool. The best way to handle SQL Injection issues in ASP.NET is multi-faceted and depending on what your application is doing, many protections may need to be in place. My goal is not to provide an exhaustive fixes, but given the examples above, the following will go a long way to protect the site.


1. Filter/validate ALL untrusted input

Untrusted input is any data that comes from outside the code/application – for a web application, for instance, this would be form fields, headers, query string, cookie, config files, any serialized data - to name a few.

2. Use Prepared Statements / Command Parameters / LINQ to SQL for Database calls. The proper use of command parameters goes a long way to protect against SQL injection.

What Next?

If you’re new to this whole realm, or even if you need more comprehensive focused understanding of the issues as well as solid techniques to defend applications – it’s important to get educated. There are many resources available online that give very specific guidance on how to write secure applications. Even better, SANS Software Security Institute has just released a focused course called Secure Coding in .NET: Developing Defensible Applications that I've helped co-author. This course covers both the vulnerabilities as well as defensive techniques to protect your applications appropriately.

Additionally SANS has developed Secure Programming Assessments for .NET, Java, and C. This allows an organization to get a high-level view of the level their developers are at from a secure coding perspective.

Next time, I’ll focus on a different type of injection attack I ran across recently…stay tuned.

* UDPATE: I refactored some portions of this article due to my 'snarky' tone (came off negative) - it wasn't my intention to come off that way (thanks Jeff).


  1. My philosophy for server-code is that ALL clients are either stupid or malicious. Whether they're people on the other side of a browser or client-code that I wrote myself. From that perspective, the server MUST validate all input.

    For SQL injection, though, a simple solution is to not build dynamic SQL like that. Use tools and libraries and patterns that don't allow for such simple attacks.

  2. No only does using the proper libraries (e.g. SqlCommand with parameters) keep you safe from SQL injection but they also handle date and number formate locale differences :)


  3. Paul,
    I think the point of the article is that YES the solution is easy yet for the various reasons above it still doesn't get done right. Try dealing with #4 in particular, it's fan-effing-tastic.

  4. Use parameters with prepared statements/LINQ or use stored procs, never use inline SQL
    Use only permissions that are needed, certainly not DBO

  5. I couldn't believe it when I looked up my house on my county's appraisal web site, accidentally put a ' at the end... and watched it spew out an error showing an entire SQL script, table names, etc. Tax dollars at work, for sure!