This article appeared originally on Microsoft Canadian Developer Connection

 

What is SQL Injection

SQL Injection is a programming weakness where the application dynamically constructs sql queries using string concatenation of unsantized data. Imagine the following scenario where the application is retrieving two parameters from the request: “username” and “password”. The application then uses these two parameters to construct the SQL statement used to verify whether the credentials are correct.

clip_image002

so for example, if username was “BobTheGreat” and the password was “AccessDenied!” then the resulting SQL would look something like:


clip_image004

But imagine if a slimy little attacker who enters their username as: “slimy’ or 1=1–” Now the resulting SQL statement would look something like this:

clip_image006

The result? The slimy dude just bypassed authentication!

Why is it a big deal?

Simple, the attacker just got access to your data, possibly all your data, and potentially your server as a bonus. The attacker essentially “0wned” your database in hackers’ terms.

7 out of 10 biggest all time data breaches were made possible using SQL Injections:

  • Heartland Payment Systems: 130 Million records lost – Jan 20, 2009
  • TJX Companies: 94 Million records lost – Jan 17, 2007
  • TRW: 90 Million records lost – June 1, 1984
  • Sony Corporation: 77 Million records lost – April 26, 2011
  • Card Systems: 40 Million records lost – June 19, 2005
  • RockYou: 32 Million record lost – Dec 14, 2009
  • Sony Corporation: 25 Million records lost – May 2, 2011

Effective Controls in .NET:

.NET provides very effective ways to protect against SQL Injection attacks. So in case of inline SQL statements, the framework offers a simple way to construct parameterized SQL statements as follows:

clip_image008

For stored procedures:


clip_image010

Piece-of-cake! Why Do We Still Have SQL Injection Then??

Inconsistency in using Parameterized SQL Statements

The most common mistake developers make is not applying parameterized SQL statements consistently. Developers sometimes want to check a complicated query and debug why is not running properly, so they comment the parameterized version of the query and run the dynamically constructed version and forget to revert back to the parameterized version.

Another example, a junior developer just the team and didn’t get around to learn what SQL injection is all about might change queries and use a non-parameterized SQL statements.

Finally, and this is not uncommon at all, when the development team decides that using parameterized SQL statements is just not for them and vouch for using filtering known bad characters such as single quote (‘) instead. Of corse, this is a very dangerous strategy because 100% of the applications I reviewed, which followed this path; had a few places where the developers missed escaping data input, and if it didn’t take me long to find those places, it is not going to take the attacker long either.

Using Parameterized Stored Procedures Correctly Yet Still Vulnerable

Although it is not as common as it is used to be, using parameterized stored procedure in a wrong way will lead to SQL Injection flaws. Consider the following example:

clip_image012

No SQL Injection right? Now, look at how the stored procedure is implemented:


clip_image014

Although the stored procedure is parameterized and the data was passed correctly to the stored procedure, the stored procedure didn’t use the parameter properly, i.e. used string concatenation to construct the SQL statement opening up the application back to SQL injection.

So the lesson here, is to make sure to peak inside your stored procedures and make sure that there are no string concatenation in there.

Second Order Injection Attacks

Second order injection happens when the application uses unsanitized data retrieved from the database. There are several scenarios for Second Order Injection attacks, but here is one:

  • The application retrieves unsanitized data from the user.
  • Parameterized SQL statements are used to insert the data into the database.
  • The application later on, retrieves this data and uses it to construct SQL statements using string concatenation.

So the SQL injection does not actually happen on the first time the data is inserted into the database because it is obvious here that we would need to use parameterized SQL statements. But the SQL Injection happens in the second time when the data is used without parameterized SQL statements. We see this scenario a lot actually in the field, where developers think that data retrieved from the database is safe to use and don’t bother using parameterized SQL statements there.

Another common scenario when the application uses data retrieved from the database, this data has been entered through another application. Assuming that the data is safe and using string concatenation to construct SQL statements will again lead to SQL Injection vulnerabilities.

Finally, Failure to Use The Principal of Least Privilege

How many of you guys have used administrative accounts to connect to the database? Oh, wow, all of you? You guys break my heart!!

No, seriously, we’ve all done it. It is easier, convenient and no permissions mess.

However, using an administrative account means that the attacker would also have administrative privileges on your database if they were able to break in. So all what the attacker needs to do is to find a tiny SQL Injection here or there and bam, they 0wn your database.

Using an account with the least amount of privileges absolutely necessary for the application to perform its function is essential to provide depth into your defences.

Summary

I help my clients uncover and remediate all the vulnerabilities in their applications. However, if I had a magic wand to erase just one vulnerability from existence it would definitely be SQL Injection. Attackers can steal your customer’s data in a flash, inject malware into your database and infect all your users or compromise your server and maybe your whole network using SQL Injection techniques.

Making sure proper input validation is done, using parameterized SQL statements and/or parameterized stored procedures properly and consistently in addition to using accounts with the least amount of privileges are all techniques that should help you mitigate the risk of SQL Injection attacks.

White Paper - Proving Adherence to Software Security Best Practices

White Paper - Proving Adherence to Software Security Best Practices

Industry standards and the best practices for developing secure software. Please provide your email and name to receive your copy.

Success! Your copy is on the way.