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, 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.
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!
7 out of 10 biggest all time data breaches were made possible using SQL Injections:
.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:
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.
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.
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.
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.
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, 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.
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.
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!
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
.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:
Piece-of-cake! Why Do We Still Have SQL Injection Then??
Inconsistency in using Parameterized SQL StatementsThe 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 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.
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.
According to 
Posts
