A Guide to Preventing SQL Injection Vulnerabilities

SQL injection is one of the oldest and most prevalent threats that has been responsible for thousands of data breaches. It’s no surprise, therefore, that it is listed as the number one web application security risk on the OWASP Top 10 document.

In this post, we take a deeper dive into the threat exploring what it is, its implications, and what you can do to protect applications against it.

What is an SQL Injection Vulnerability

Definition: It’s a security flaw on a web application that allows hackers to alter how an SQL query is executed and consequently gain unauthorized access into the application’s database.

How it works: SQL injection takes advantage of poorly written SQL statements where user input is processed as part of the SQL statement. Take this PHP query for instance.

$query = "SELECT * FROM users WHERE user = '$username' and password = '$password'";
$result = mysql_query($query);

Notice how the user-provided data, username and password are directly connected with the query? Now, imagine if someone used (admin’; –) as the input in the username field. Then the SQL statement to be executed would look like this;

$query = "SELECT * FROM users WHERE user = 'admin'; --' and password = '$password'";

The semicolon that comes after admin is to indicate that the current statement has ended and the double hyphen instructs the SQL compiler not to execute the rest of the statement because it’s a comment.

That’s how easy it is for attackers to login into a database as an admin using SQL injection.

And, while the WHERE clause in a SELECT query is the most prone to SQL injection vulnerabilities, it’s not the only area where they can occur. Other vulnerable areas include:

  • The table and column names in SELECT statements.

  • The ORDER BY clause in a SELECT query

  • UPDATE statements within the updated values or the WHERE clause

  • INSERT statements within the inserted values

Types of SQL Injection Vulnerabilities

SQL injection vulnerabilities fall into 3 major categories depending on how they access backend data.

  1. In-band SQLi In this type, attackers use the same device to carry out SQL injection and also to receive results. There are two variants of In-band SQLi.

    • Union-based SQL injection – This is a type of vulnerability that allows attackers to combine more than one SELECT statement to access data that would otherwise be inaccessible.

    • Error-based SQL injection – Here the attacker sends a query that causes the database to produce an error. The attacker can then gather information such as table names and content from the error message and use it to fashion an SQLi attack.

  2. Blind SQL injection vulnerability In this type of vulnerability, no data is returned from the database to the attacker. Instead, the hacker performs actions on the database and then studies its behavior to inform their attack plan. Blind SQLi also has two variations.

    • Boolean-based – Here, the attacker sends a query to the database that prompts for results. If the query has a false condition and yet the application loads normally then that’s an indicator it’s vulnerable to SQL injection.

    • Time-based – In this type of attack the hacker sends a query to the database requiring that the application wait a while before returning results. If the database waits then that means that the database is vulnerable.

  3. Out-of-band This is a type of attack where the channel used to carry out the attack and the channel that receives the results are different.

We are now going to look at how to prevent SQL injection vulnerabilities but before that, let’s explore why these vulnerabilities are such a big threat.

Implications of an SQL injection attack

  • Data exfiltration - A successfully executed SQL injection gives the hacker access into the database where they can proceed to steal important data including usernames and passwords, financial information, intellectual company property, and other sensitive information.

  • Database manipulation - SQL injection attacks can also be utilized to modify the database by adding or deleting tables. The hacker can also change how the database responds to other queries.

  • Escalated attacks - Depending on the attacker’s skills it’s also possible to attack back-end systems by exploiting SQL injection vulnerabilities. Case in point, one of the most recent high-profile cyber-attack, the Accellion attack, started with SQL injection.

  • Damaged brand reputation - An SQL attack especially one that ends with a massive data breach will cause customers to lose trust in the organization’s ability to protect them.

  • Regulatory fines - In some cases, companies may have to pay fines for failing to adhere to set security regulations.

How to Prevent SQL Injection Vulnerabilities

  • Use prepared statements

So, we have established that the reason attackers can execute SQL injections is that the user inputs in dynamic queries are also interpreted as commands right?

Prepared statements help overcome that flaw by requiring that the developer write the SQL command and the user input separately. Now, regardless of the input provided by the user, it won’t change how the query is executed in the database.

Remember the example query we used at the beginning of the article?

$query = "SELECT * FROM users WHERE user = '$username' and password = '$password'";
$result = mysql_query($query);

Here is a safe way to approach it using prepared statements.

$stmt = $mysqli->prepare("SELECT * FROM users WHERE user = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();

In this approach, the developer uses the symbol ‘?’ as the placeholder for username and password. Now, the query will first be compiled with the placeholders and then the user-provided data will be added later.

So, if the attacker was to use admin as the username and a’ or ‘1’=’1 as the password, the query would not be altered. Instead, it will look for a user named admin with the password a’ or ‘1’=’1.

This is the most effective way of protecting applications against SQL injection vulnerabilities.

You can read more about using prepared statements to prevent SQL injection in this post.

The only downside is that this method won’t work to protect your application from untrusted input in some parts of the query such as the table and column name. The same is true for the ORDER BY clause.

This is why you will need to employ other security measures to effectively protect the web application from SQL injection attacks. These methods include:

  • Stored procedures - This is more of an alternative to prepared procedures. It uses the same principle as prepared statements only that the SQL code is stored in the database and called from the application while prepared statements are “prepared” every time you want to execute it.

  • Allow list - This method is perfect for applications that have a finite number of options for the user to choose from. In that case, you set up the query in such a way that the user can only choose from a list of options and not add their own. Still, this method is prone to errors from the developer and it is recommended that you couple it with prepared or stored statements.

  • Input sanitization - This is also a great way to guard against SQLi but needs to be coupled with prepared statements. It involves removing unwanted characters such as, ‘ / “ {} from user-provided data.

  • Web application firewall - Blocking out malicious user input manually can be quite a hassle. It may also lead to false positives which is not a good user experience. A premium web application firewall can take this burden away from you as well as protect your web application against other threats. A great WAF will have a database of all the latest signatures that enable it to identify and block malicious SQL queries in real-time.

Additional tips to protect applications against SQLi

  • Turn off the visibility of database errors on your production site to counter error-based SQL injections that we discussed.

  • Use the latest development technologies since they will have the latest security provisions. For instance, if you are using PHP, use PDO instead of MySQLi.

  • Scan your application regularly for vulnerabilities

  • Database connection access limit. Control who can access the database and what they can do. It’s also recommended that you have a different database for different functions.