Security Code Review Techniques–SQL Injection Edition

  • Article

Security on the Brain

Power%20-%20LockSecurity is something we all know is important, but is it something that we always do? Most likely, not always. That’s partially because security is complex and takes time to implement. Many of you, these days, don’t have that time (it’s all about shortest time-to-market, right?) to think about security. You make sure that minimal security checks and balances are there, but that’s about it. Totally understand.

But security doesn’t have to be complex to implement once you know what you have already available to you in the frameworks and products that you use every day. Over the course of the next few weeks, check back often as we’ll be demystifying different aspects of application security, simple things you can do to protect your applications, how to use the tools and frameworks you’re already using as your lines of defense against hacking, and more.

Feel free to start or join discussions in the Canadian Developer Connection LinkedIn group to give and receive thoughts and feedback on these or any other topics from fellow Canadian developers and experts.

Guest post by Sherif Koussa

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:

  1. The application retrieves unsanitized data from the user.
  2. Parameterized SQL statements are used to insert the data into the database.
  3. 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.

Sherif Koussa
Sherif_High_Rez smallSherif is an independent Application Security Consultant and the founder of Software Secured - an application security firm. He has 14 years in the software development industry with the last 6 years solely focused on application security testing, security assessments and teaching developers how to write secure code. Sherif's main target is helping organizations assess their high-risk software applications using a source-code driven security methodology. He started his security career by working on the infamous OWASP security teaching tool WebGoat 5.0, he then helped SANS launch their GSSP-JAVA and GSSP-NET programs as well writing the blueprints of Dev-544 and Dev-541 courses. In addition to that, he authored courseware for SANS SEC-540: VOIP Security. In addition to leading OWASP Ottawa Chapter, Sherif is leading the Static Analysis Code Evaluation Criteria for WASC. Sherif performed security code reviews for 3 of the 5 largest banks in the United States. Before starting Software Secured, sherif worked on architecting, designing, implementing and leading large-scale software projects for Fortune 500 companies including United Technologies and other leading organizations including Nortel Networks, March Healthcare, Carrier, Otis Elevators and NEC Unified Communications. LinkedIn | Twitter