What is SQL injection
In short, it’s the result of a not checking (validating) user input before using it to build a dynamic SQL query. An attacker can take advantage of this to achieve a range of possible outcomes. From expanding a query beyond it’s intended scope, to fully taking control of a server.
Let’s start with an example
The typical example given when demonstrating SQL injection is a vulnerable login page, which is vulnerable to error based SQL injection, where the user is presented with a prompt for their username and password which are then checked against records in a database.
The vulnerable SQL query in this case is just a string of text that then has the user’s values placed into it. For example:
select * from users where username = “$username” and password = “$password”;
To display this a little more visually:
We pass in these values into the login form:
- username = root
- password = myPassword
Which creates the following string to be passed to the SQL database server:
select * from users where username = “root” and password = “myPassword“;
Which then returns the row containing matching the root user, providing their password was “myPassword”.
Obviously this example assume the passwords are stored as cleartext in the database, but we’ll cover those risks in a separate article.
This demonstrates that everything is working as we expect it too, so let’s try and break it.
Passing in these values instead:
Which becomes the string:
select * from users where username = “”” and password = “”;
The extra double quote breaks the query by making it syntactically incorrect (the best kind of incorrect). When this string is sent to the database; instead of receiving a record back, we receive an error, this would usually manifest as a failure message on the page. During a penetration test, it’s critical to look out for these kinds of messages.
So far all we’ve achieved is failing to log into a web form, not particularly impressive. Let’s try to change the structure of the query.
We pass in these values:
- $username = root”;#
- $password = doesntmatter
Which then creates to:
select * from users where username = “root”;#” and password = “doesntmatter”;
These characters terminate the query, everything after the # is commented out and ignored.
When this query is sent to the database, the password check will be ignored. We’ve now totally bypassed authentication.
At this point, the attacker can potentially run any arbitrary statement they like. For example, they can start running select queries to extract all other information within the access databases or update statements to ‘change’ fields.
Also, just because no error is displayed don’t assume you are not vulnerable to SQL Injection. You could still fall victim a blind SQL injection attack.
As you can probably expect, something this easy to exploit isn’t going to work on the Facebook login page. SQL injection errors are usually subtler, and seldom directly in the login form. That doesn’t mean that there aren’t SQL injection vulnerabilities in the wild. Some recently exploited high-profile company and names you may recognise include:
All of those compromises were SQL injection attacks, resulting in customer data being exposed.
It gets worse
SQL injection isn’t just limited to having attackers download your entire customer database, (I understand that’s a pretty broad definition of “limited”) But under the right conditions an attacker can gain full remote code execution on the server.
This varies from server to server, for example:
- Microsoft SQL Server: The “xp_cmdshell” command is available by default in many installations
- MySQL: The “into outfile” command can be used to place a webshell
- Postgreql: Remotely creating a crafted user-defined-function (UDF)
- Oracle: Remotely creating a crafted Java library
These all have various limitations, effect only certain versions or require specific configurations, but if successful they potentially allow the attacker to totally gain control of a server.
Now that you’re sufficiently terrified or excited, let’s make sure this doesn’t happen to our apps.
If you’re building a new application, or have the budget to overhaul your existing ones; remove every trace of dynamic queries. Remember how we were building strings that happened to be queries and sending to the database earlier? Those are dynamic queries. There is a better, more secure way to interact with SQL databases called either Parameterised Queries or Prepared Statements.
In PHP we would use:
$statement = $conn->prepare(select * from users where username = ? and password = ?);
If you absolutely have to use dynamic queries, be very picky about what data you allow through. Only permit the expected input (validate) at the application layer via server side code. Always taking a ‘whitelist’ based approach and always escape the dangerous characters that you can’t do without.
To see this in action we’ve made a short video showing a step by step breakdown of each stage. Starting by detailing how to find potential injection points, moving through query control and exfiltration and finally demonstrating how to turn SQL injection to full remote code execution.