In March, 2011, two hackers, “Ne0h” and “TinKode” compromised MySQL.com and posted the site’s customer usernames and passwords. According to the pair, they used the site’s SQL Injection (SQLi) vulnerabilities to launch the attack.
Just over a year later, D33Ds Company, a hacker group posted passwords of 450,000 Yahoo users. Like the Oracle owned MySQL.com website attack, the D33Ds Company hacker group said it had taken advantage of Yahoo site’s SQL Injection vulnerability to attack.
And a little over two years ago, it was widely reported that over a million websites that use WordPress were at risk of a security compromise from a SQL Injection. The WP-Slimstat plugin critical security flaw was blamed for the increased risk of possible hijacking of websites using the WordPress content management system.
These are just few of the many SQLi vulnerability cases that have left web developers scratching their heads in search for quick and effective security solutions. These and other incidences gave people a taste of the magnitude of damage that SQL injection vulnerabilities can cause a company if they are not addressed.
While the discussions around SQL Injection have been ongoing for over a decade now, some developers still have little understanding of what it is all about and even fewer have seen the and understood the reality of the dangers it presents. Therefore, in this article, you will learn what SQLi is, why you should fix it and how to go about fixing it.
SQL Injection vulnerability: what is it?
SQL Injection is one of the top 10 OWASP vulnerabilities in the category “Injection.” This vulnerability is extremely easy to take advantage of. Additionally, it is quite common and its impact is awfully severe. A website developer is responsible for recognizing such a vulnerability and patch it.
Take a look at an example of an SQL Injection below:
The above line of code is all that an attacker requires to get complete access to a website’s entire database. Do you still doubt that? Let us examine the exact way the hacker would use this to own your entire database.
Because the hacker has gained full control of payload (e.g. say via params[:email]), he can go ahead and insert anything they choose into your website’s where query. Take a look at the example below:
As you can see above, the hacker sends a payload of ‘) or 1=1- –. This is how it works:
Sending the ‘) part of the payload will set the query so that it returns no results; email returns as blank: email=”.
In the second part 1=1 will always equal true, resulting in the returning of the first entry in the table of users.
Finally, — part is an SQL comment. Using this technique, the attacker cancels out any more modifications to the query that could have come from the server side. What this means is that the fine tuning is reduced, making a payload to work. On the most part, most hackers use this payload format in the majority of SQL Injection attacks:
- Closing the query
- Inserting the attack
- Preventing server modifications
While a glance at this might look like an inconsequential event, the hacker would now be in apposition to manipulate payloads as his way into even juicier website information. How? Take a look at this example below:
Using the ‘) or admin=’t’– payload, the hacker has found his way into the system enough to return an administrator user. This gives him information about a website administrator within the site’s database.
Fixing an SQL Injection Vulnerability
As you have seen in the above demonstration, it is extremely dangerous to fail to fix an SQL Injection vulnerability. The process of fixing SQL queries requires the use of parameterization. This is the most secure way of handling unsafe inputs by users.
Whichever ORM(Object-Relational Mapping) you choose, you should be able to use its facilities to parameterize queries.
Here are two common types of queries that are unsafe and how you can fix them. The examples are based on Active Record ORM.
A single parameter query
This is the most common for queries in Ruby.
As you can see above, line 8 and line 3 look similar. However, they are quite different in that unlike line 8, line 3 is using string formatting instead of using parameterization. String formatting is unsecure and will not provide adequate protection against possible SQL injection.
The other important lesson we can learn from taking a closer look at the Safe vs Unsafe examples above is that you know you are vulnerable to an SQL Injection if you find yourself having to add surrounding quotes to queries. This is a rule of thumb.
Compounding Queries
It is not always that you make a single parameter query, sometimes, there are a number of queries in a series using an AND statement as shown below:
To deal with this vulnerability, instead of AND, use OR statements as shown below:
As you can see, this is not the prettiest approach, but it makes sure the parameters pass in separately from each query. As a result, parameterization takes place, keeping your site safe from possible SQL injection.