SQL Injection is a term dreaded by web developers, database admins, and CISOs alike. Even if your regular job in IT doesn’t focus on cyber security or coding, chances are you’ve still heard of SQL injection and are familiar with the havoc that it has caused throughout the history of security breaches.
The SQL injection flaw – commonly referred to as SQLi – has been the bane of the web application space since first being identified in 1998 (and possibly earlier, depending on your source), when it was revealed in issue 54 of Phrack magazine by the cyber security researcher/hacker known as Rain Forest Puppy (a.k.a. rfp).
Wait, what?? Yes, you read that correctly… 1998. That article was published almost 20 years ago, yet he we are still dealing with SQLi on a regular basis today. Injection-type cyber attacks (of which SQLi is one) are still ranked as the highest-ranked threat by the Open Web Application Security Project (OWASP), whose Top 10 list of the most critical web application security risks is the de facto web application security standard for companies and regulating bodies around the world. Research findings from organizations like Verizon and Gartner also confirm that SQLi remains a very real and very problematic cyber attack vector. And to add fuel to the fire, this is also backed up by data we have collected here at Alert Logic. In the 18-month period of data-collection that our Cloud Security Report is built on, 55 percent of all observed cyber attacks against our customer base used SQLi. Yes, SQLi is still alive and kicking… like an angry mule.
But don’t give up on creating your web application just yet. There is good news. SQLi is a well-understood cyber attack precisely because it has been around so long. That means that there are great mitigation strategies already out there. It also means that it’s pretty easy to explain, which means more people can get educated about how SQLi works and the dangers it poses. So, let’s dig into SQLi.
Digging into SQLi
Let’s look at an application that is built using the ever-popular LAMP application stack. Traditionally, the “M” and “P” in LAMP respectively stand for MySQL (a relational database management system, i.e. the “database” or “SQL database”) and PHP (the programming language used to write the application). Because these are the two parts of the app most involved with SQLi, we’ll focus here. Let’s start with the database.
SQL stands for Structured Query Language, which is used to communicate with a SQL database like MySQL (other examples are MSSQL, PostgreSQL, and Oracle) to perform tasks such as retrieving data, adding rows to a table, deleting tables, or just generally updating the database. Anyone with direct access to the database can build queries using typical SQL commands like SELECT, INSERT, DELETE, etc. (a great list of some commonly used SQL commands can be found here). SQL is extremely powerful; hence it can be useful when used correctly and harmful when used maliciously or incorrectly.
If you’re running a business, it’s very likely that you want your customers to have access to the data in that database so they can buy your products or perform other kinds of business. But giving direct access to the database isn’t terribly useful (the typical user isn’t adept at writing SQL queries) or very secure (there is a ton of room for abuse if the outside world can get directly into your database). So, in comes the web application – in our case, written in PHP – to serve the dual functions of providing a convenient method for accessing the database and adding a layer between the user and the database.
There’s just one problem: because of insecure coding, many web applications are effectively allowing that direct access to the database that you are trying to avoid. Any kind of fields in the web application that don’t filter or otherwise sanitize user input can potentially allow injection of harmful commands that can get executed by the database.
Let’s look at an example of SQLi using the Damn Vulnerable Web App (DVWA) from Dewhurst Security. DVWA is an open source PHP/MySQL web application project, and it is … ahem… damn vulnerable. It is widely used for learning how to hack websites in a legal manner. I setup an instance of it on Amazon Web Services (AWS) to show some examples of SQLi. Quick security note: if you install DVWA on AWS, please be sure to lock down the security group of the EC2 instance so that only you can access it publicly. In my case, I locked the access down to my home and work source IP addresses to keep bad people from compromising the instance.
sqli1
One of the cool features of DVWA is that it lets you attack it with different levels of security so you can test your skills at varying difficulties. For convenience, I’ll be showing you SQLi against the low level of security.
On the homepage of DVWA, I click on the SQL Injection button. That presents me with an input field. We think this field is vulnerable to SQLi, but how do we know?
First of all, let’s see what the field is looking for as a user ID. If we put in a simple “1” and click submit, we get back the following:
That tells us that usernames are most likely stored as numbers. Just in case there is some doubt, we could go in a type in 2 and submit, then 3 and submit, and so on to get info on every user. But that is the hard way of doing it, and isn’t nearly as fun as typing in a cool SQL string to see if we can get all the data in the table (which will tell us if the field is injectable). So let’s input 0’=’0. That statement is always true, which means that we will get everything back from the table if the field is vulnerable to SQLi.
And the result from the database is:
So now we know every user name in the database, which means we can start really digging into the database to figure out how best to attack it. We can run strings to find out the database version, the database user context that is being used, password hashes that can be cracked over time, and more. Given time and access and a poorly-coded web application, the amount of reconnaissance and damage that be performed is virtually infinite.
So now that we know that the field is vulnerable (well, we already knew that, but it was fun proving it), let’s look at the code to see the problem. Here’s a screenshot of the PHP code.
sqli6
The vulnerable code that we took advantage of is $getid = "SELECT first_name, last_name FROM users WHERE user_id = '$id'";
When we entered 0’=’0 into the field, we were appending our commands directly into the SLELECT statement, and the application was conveniently passing that unfiltered query directly to the database for execution by the interpreter. With the way this code is written, any valid SQL query would get executed by the database. The query we created looks like this: SELECT first_name, last_name FROM users WHERE user_id = '0'='0';
So how do we fix it? In this case, we could simply do some scrubbing (sanitizing) of the data, which looks something like this:
Now when we try to enter that same 0’=’0, we get nothing back from the application (no error message is given). That’s because the new code above is making sure that only integers can be entered into the field. Any special characters will be ignored, and the user must try again.
To be sure, this is not the only way of going about fixing this issue. In fact, if you use DVWA to do your own testing, you can see that there are additional lines in the code that take care of some of the issues when you change the security levels to medium or high. The problem is that those levels are also vulnerable, which means you’ll need something more foolproof to fix the flaw.
To get familiar with SQL injection and some of the best ways to protect against it, I highly recommend checking out the OWASP SQL Injection Prevention Cheat Sheet. The number one suggestion from OWASP for preventing SQL is through prepared statements with parameterized queries, though there are other issues that could arise by using that method.
There are also issues like second order SQLi (these are more of an issue with the rise of the use of microservices architectures) and blind SQLi (these arise when the code is still vulnerable but the application only displays generic messages. For all of these, the essential point is that the developer must treat all data going into the application – no matter the source – as untrusted. When code is written from that perspective, many vulnerabilities can be avoided during development rather than fixed after they have been discovered through testing (best case scenario) or a breach (definitely worst-case scenario).
