Security on the Brain Security 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
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.
so for example, if username was “BobTheGreat” and the password was “AccessDenied!” then the resulting SQL would look something like:
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:
The result? The slimy dude just bypassed authentication!
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:
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:
For stored procedures:
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:
No SQL Injection right? Now, look at how the stored procedure is implemented:
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 happens when the application uses unsanitized data retrieved from the database. There are several scenarios for Second Order Injection attacks, but here is one:
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.
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.
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 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
Excellent article which permits to discover what you are calling the second order injection attacks that I was ignoring.
For that : simply thanks
@Papy, glad you found the article useful. You are not alone, second order injection are usually one of the most ignored aspects of SQLi.
Great article. I personally suffered from a security hole in one of the projects I was working on. The Database was hacked because the code was written in a way where SQL queries were formed by string concatenation. Great tips for everyone when writing code that does DB transactions.
I have a question though, is there a tool that can detect Second Order Injection attacks?
@Fady, glad you found it useful. Security-oriented Static Code Analysis tools usually do a very good job detecting these when the SQL statements are inline inside the source code, not so much when the concatenation is done inside stored procedures. I'd recommend starting with CAT.net. Static Analysis Tools Evaluation Criteria (SATEC) is another resource that could help you choose a tool fitted to your environment projects.webappsec.org/.../Static%20Analysis%20Tool%20Evaluation%20Criteria
Thanks Sherif for the valuable information!