Application security tips part 2 – SQL Injection

In part one of this series, I discussed the process of threat modeling an application. Once that step is done, you will have some concrete threats for which you need to prepare. Let’s take a look at some of the threats that you would likely identify as part of the threat modeling exercise and how to protect against them. We will be mainly focusing on web-based applications.

I will be doing this with the following format:

  1. Describe the vulnerability (with some examples)
  2. Describe what controls should be in place to prevent or mitigate the vulnerability.
  3. Investigate what specific .NET tools and libraries can be used to implement the controls.

First up, we’ll tackle SQL injection attacks. Without further ado, let’s get to the good stuff.

(source: xkcd)

What is SQL Injection?

SQL Injection is still the number one vulnerability on OWASP’s top ten list. It’s easy to believe this as we are seeing new examples of SQL injection attacks on a regular basis. Yahoo loses over a billion of their users’ records. 167 million LinkedIn accounts are exposed. These stories pop up all of the time. Most of the time, when you hear this kind of news story it is due to a SQL injection vulnerability in the site.

The point is, if you don’t want your company to be the next headline, make sure you understand SQL injection and how to defend against it. This is a skill necessary whether you just started programming or have been for years. Let’s take a look at the anatomy of a SQL injection attack.

The first thing to understand about a SQL injection attack (or any other injection attack, for that matter), is that it is possible due to the relationship inside a computer between data and code. Von Neumann architecture has given us computers that have data and instructions in the same memory. Unfortunately, this can be exploited. Now, usually we speak of direct memory-based attacks when talking about buffer and stack overflows, but injection attacks are also related to this.

Injection attacks come down to one simple violation: the application allows data to pass from the data context to the execution context. This means that the user can put code (SQL in this case) into an input field and instead  using the data as data, the application executes it as code. The attacker can now put arbitrary code into your application and it will happily do what the attackers ask. This usually means returning several million records with sensitive data as we’ve seen.

Injection attacks come down to one simple violation: the application allows data to pass from the data context to the execution context.

Using the xkcd comic from above as an example, let’s see how this works.

Bobby_Tables_Diagram

The offending code

Now let’s talk specifics. The number one reason why SQL injection happens is developers creating strings of SQL in their code and concatenating the input received from the client into the SQL statement for execution. If you see this code it should turn your stomach and never see its way past a code review. We’ll take a look at a basic product query using parameters.

image

How many problems can you see in this code?

  1. The productSubCategoryId is being set directly from the URL parameters. This is a good lesson. SQL injection can happen through URL parameters as well as inputs on a form (are you listening, WebAPI guys?).
  2. A SQL string is created and the productSubCategoryId is concatenated to the end of the string.

When looking at an application architecture, you should try to ascertain all of the trust boundaries. Trust boundaries are the areas where the untrusted parts of your application meet the trusted parts. The server represents a trust boundary in a web application. The client is the untrusted part (which will be discussed in more detail in part 4 of this series: The Client is Evil!), your business logic is the trusted part.

As a rule, nothing passes through a trust boundary without being validated. A trust boundary is like an aggressive national border; the data needs to be stopped, searched, and only let through if everything checks out.

In the above code, we see a trust boundary violation. Untrusted data coming from the client is directly used in a trusted query. SQL can be passed in via a URL parameter and will be executed by the database, giving the attacker whatever he wants. Never allow a situation where a user can change your code or execute his own!

Necessary Controls

The right security controls and secure programming practices are necessary to prevent SQL injection attacks.

Least Privilege

Lease privilege is a key security principal. It means only giving users the access necessary to do their job and nothing more than that. How does this help against SQL injection attacks? Set up whatever account you use to connect to the database (I suggest a service account separate from any human users) to only have the database access necessary to do its job. If we think back to young “Bobby Tables” at the beginning of this post, that DROP TABLE STUDENTS command doesn’t work if the account used to connect does not have rights to drop tables. Don’t use superuser or dbo rights to connect to your production databases or you are asking for trouble. It may also be prudent to lock down that account on a table by table basis. This will prevent it from even being able to read data it ought not to read.

Don’t use superuser or dbo rights to connect to your production databases or you are asking for trouble.

Validation/Sanitization of data

These are two different things, but I put them together since they can be used together to form a defense specifically against bad data inputs. Sanitization is scanning input for and removing malicious bits of code. Even though this could be used a part of a defense-in-depth strategy, I would strongly recommend against using only sanitization. This is because sanitization is a blacklisting feature. It can only remove what it knows to be bad. Once a fancy or crazy new way of obfuscating code as input appears, now your sanitization library doesn’t work anymore.

While sanitization is a good option, validation is a must. Also, this validation must happen on the server, not on the client. Validation is a whitelisting feature. You only allow what you want or need. For instance, if your Id parameter can only be an integer, check on the server that what was provided was actually an integer. Throw an exception if anything else is passed. You can then log the exception to help recognize when someone might be probing your site for vulnerabilities. In general, whitelisting is always more effective than blacklisting.

While sanitization is a good option, validation is a must.

Parameterized SQL queries/Stored procedures

This is the granddaddy of them all. If you do nothing else (not recommended), then parameterize your SQL queries. This means setting up the queries such that values are passed in as parameters instead of concatenated to the end of strings. While it’s possible to do this manually with your own SQL, I don’t recommend it.

Instead, if you don’t use an ORM like Entity Framework (more on that later), then I totally endorse the use of stored procedures. Stored procedures are pre-built queries that live inside the database and are called from the application. This means that they are parameterized by default and you won’t be writing SQL inside the application code. You instead write the SQL in the database as a stored procedure. You call the stored procedure with the parameters it needs. Let’s take a look at an example:

SQL_Injection_SP

This is a simple example but it shows how easily you can incorporate stored procedures into your data access code. You may be wondering how this helps with SQL injection. Good question. Remember the discussion earlier about passing between the data context and execution context? When you concatenate values into a SQL string, you allow data to be interpreted as code. Parameterizing your queries prevents this from happening. Using parameters ensures that the data is treated only as data and never as code. If you try to add code such as “2’ OR 1=1–” for id hoping to return all records, the database will look for a record with the id of “2’ OR 1=1–” and won’t find it, returning zero rows.

One thing to remember, however. Stored procedures can have SQL injection vulnerabilities themselves if written incorrectly. Don’t concatenate parameter values to a SQL string in a stored procedure. This will lead to the same vulnerability as was there in the server code.

Using parameters ensures that the data is treated only as data and never as code.

What .NET provides for you

The best way to prevent SQL injection in the .NET world, in my humble opinion, is to use Entity Framework as an ORM to connect to your database. There are other ORMs available, such as NHibernate, but I am most familiar with Entity Framework and rather enjoy using it. Most ORMs will protect well against SQL injection and I am not here to debate the different ORMs available. So I’ll move forward with showing how Entity Framework protects your application from SQL injection.

The principle of least privilege is implemented at the database level. Entity Framework does help with the validation piece. When you create Entity POCOs (Plain Old CLR Objects) you an add validation rules using annotations found in the System.ComponentModel.DataAnnotations namespace.

You have a host of annotations available. You can restrict the length of a string, make sure it matches a certain regex, and mark fields as required. This all will be added to the database schema and will be checked at runtime to ensure that only the data you want is allowed through. It’s great that .NET allows you to declare in one place logic that will be used everywhere you use the POCO. Check out an example here.

Another great part of Entity Framework is that it automatically parameterizes your SQL queries. Just use LINQ to build queries against your data and it will all be translated into parameterized queries that are not vulnerable to SQL injection.

Protect your site from SQL injection!

So now you should know enough about SQL injection to be able to protect yourself and your code. Let’s review what we’ve discussed.

  1. SQL injection happens when user-supplied data is interpreted as code inside the database.
  2. The principle of least privilege, validation, and parameterizing SQL queries will eliminate SQL injection vulnerabilities.
  3. Entity Framework (and other ORMs) offer easy and effective ways of preventing SQL injection attacks.

I hope you’ve found this information useful. I’d hate for your company to be the next headline. Following the guidelines here will help to prevent that.

Stay safe. Code safe.

References

As always, I stand on the shoulders of giants. Here are the sources of information that helped me put this post together.

  1. OWASP Top 10 Web Application Security Risks for ASP.NET by Troy Hunt. A must-watch Pluralsight course for any ASP.NET developer.
  2. The 10 Biggest Data Breaches of 2016

Also, these resources will help you to learn more about this vulnerability.

  1. OWASP Top 10 Project
  2. Ethical Hacking: SQL Injection. Another kick-butt course from Troy Hunt
Advertisements

2 thoughts on “Application security tips part 2 – SQL Injection

  1. Pingback: Application Security Tips for New Developers–Series Introduction – Green Machine

  2. Pingback: Breaking Down the OWASP Top 10 2017 RC Part 1: Numbers One Through Five – Green Machine Security

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s