How to Detect & Prevent SQL Injections (SQLi)

Updated: Dec 3, 2021

SQL injection attacks are one of the biggest threats to web application security today, coming third in this year's Top 10 list from The Open Web Application Security Project (OWASP). As these attacks are so easy to carry out, any application using a database can be at risk.


Understanding what SQL injection attacks are, whether your application is vulnerable to them, and measures to prevent them altogether is key to building a safer, more secure online environment for your business and customers. It’s the difference between keeping valuable information protected, and leaving the door wide open for hackers to help themselves to your data — with disastrous results.


We'll cover the following:

  1. What is SQL and what is it used for?

  2. How do attackers access an SQL database?

  3. What are SQL injection vulnerabilities?

  4. What are the different SQL injection vulnerabilities?

  5. How and why are attackers trying to inject into SQL queries?

  6. How do you prevent an SQL injection attack?

  7. What’s the best defence against SQL Injection attacks?

  8. How do I secure my application against SQL injection attacks?

What is SQL and what is it used for?


SQL stands for Structured Query Language. It's the standard 'language' used to manage data in relational database management systems (RDBMS), whether that's storing it, changing it, or retrieving it. Examples of businesses that use them include Hive, Google and Amazon, so they're both popular and widespread. These SQL databases allow people (users) to access, change and delete data, and carry out a range of other functions.

How do attackers access an SQL database?


The first step is to find vulnerabilities. Most websites require a person to enter details into input fields, like a username and password, to access their account. When you enter this information, the website essentially runs a check (query) against the database to make sure these match and the person can be logged in. But by using a few simple tricks, an attacker could get the SQL database to run a different kind of query — effectively changing the way that database behaves — and unlock access and even administrator privileges, providing a real threat to your website’s security. If your application relies on a database and needs user input (such as entering a username and password) to access it, you could be at risk.

What are SQL injection vulnerabilities?


Attackers can use SQL injection vulnerabilities to bypass application security measures and change the way an application interacts with its own database, putting data at risk. An attacker can use SQL injection to add, change and delete the records in the database via the application. This could include accessing sensitive customer information (such as personal ID or bank details), intellectual property and more. With access to this information, the attacker could then perform actions like emptying a bank account, deleting customer records or simply accessing information they otherwise couldn't. Let's look at a technical example. An attacker can chain SQL statements together and append (insert) their own query to the one the application performs. This could be used to maliciously delete all existing users from the application.


An example payload:

a' or 1=1; drop table users;-- -

This would lead to the following query performed by the application:

select id, title, date, content from blog where title = 'a' or 1=1; drop table users;-- -'

Another example would be if the application expects that a user will search for an author of a book on the website. Once an attacker has identified that the application has an insecure handling of user input, they can append their own query, which allows them to pose as an admin user on the platform.


Here's a payload to show how that looks:

a' or 1=1; insert into users (username, password, is_admin) values ("wizard", "MySecretSuperPassword1!", 1);-- -

This would lead to the following query performed by the application:

select name, author from books where author = 'a' or 1=1; insert into users values ("wizard", "MySecretSuperPassword1!", 1);-- -'

Finally, here’s an example where the application assumes that a user will create an order for a particular article. The website passes the identifier for the article (article_id) back to the application and then forms an SQL statement that will create an order for that particular customer. Since the article_id is passed to the application from the user, they might interfere with the application logic and create a valid ‘insert into’ statement.

Following the valid statement, they can insert a select statement, which in turn will provide them with sensitive information about customers in the database (such as in an error message).


An example payload looks like this:

1, "2021-12-01"); select id, firstname, lastname, personal_id, bank_name, iban, bic from customers;-- -

This would lead to the following query performed by the application:

insert into orders (customer_id, article_id, date) values (100, 1, "2021-12-01"); select id, firstname, lastname, personal_id, bank_name, iban, bic from customers;-- -, "2021-12-01");

What are the different SQL injection vulnerabilities?


There are lots of different SQL injection vulnerabilities, and they can be grouped into four different types:

  • Error-based SQL injection: This type of vulnerability could help hackers retrieve information purely based on database error messages.

  • Boolean-based SQL injection: Without error messages, attackers can have a harder time getting into a website’s database. Enter the blind, or Boolean-based, SQL injection method. With this approach, there are other indicators an attacker can use to extract information.

  • Time-based SQL injection: If this method works, it suspends the system for a time period. The attacker can then determine whether the database is vulnerable enough.

  • Out-of-band SQL injection: These are one of the rarer types of SQL injection vulnerabilities, occurring when a hacker attacks and mines information through two different channels.

How and why are attackers trying to inject into SQL queries?


Attackers are looking for information that they can use or leverage for their own ends — whether it’s banking information to empty an account, personal information they can sell on, or sensitive company information they can exploit. SQL vulnerabilities can open up a whole world of possibilities. An attacker could run attack chains, compromise the underlying operational system and internal network, and even escalate privileges to control them, meaning files can be added, accessed, modified and deleted.

SQL injection attacks include retrieving hidden data, where hackers can modify their query for additional results; examining a database, which can reveal its version number and structure so attackers can exploit publicly known weaknesses or uncover information about the table set-up; subverting application logic, which allows the attacker to gain access through exploiting the way the system is set up; and blind SQL injection, where an attacker can ask a series of 'true or false' questions to reveal and understand error messages.

Union-Based SQL injection is also common. Because databases store information in tables and columns — like giant spreadsheets — there are ways to 'jump around' and unlock access to other areas or tables. Once an attacker has exploited SQL injection vulnerabilities to get into a database and has worked out how many columns there are and whereabouts they are in the column, they can use a Union-Based SQL injection to run more queries and dig even deeper, accessing other database tables to retrieve information. Here’s an example to show how it looks. Let's assume the vulnerable application queries a database for the a product identifier (productid), product name and price of a product. It does so by parsing the product identifier from the id parameter passed to the application via the URL.

http://example.com/Product?id=1

In this example, the application expects the database to only return one row back to the application.

/* create database connection */

$sql = "select id, name, price from 1ux9a7_products where product_id = '" . $_GET["id"] . "'";
$result = $conn->query($sql);

/* process result further, expecting only 1 row returned by the database */

Note that the table starts with a random pattern "1ux9a7_products", which can be described as 'security by obscurity'. This can be easily circumvented by simply asking the database for the tables it contains. Assuming that the attacker wants to dump the username and password of an admin user, they can ask the database to return tables with the name 'users' in it.


An example SQL query to do so can be found below:

select table_name from information_schema.tables where table_name like "%users%"

This results in the following payload being appended to the parameter:

http://example.com/Product?id=1' union select table_name, null, null from information_schema.tables where table_name like "%users%" limit 1,1--

After obtaining the answer from the database, containing the table name for the user (which in this case is 'xz712a_users'), they are able to create the following payload to extract the username and password for users that have an appearance of 'admin' within the username.


The following demonstrates an example of this:

http://example.com/Product?id=1' union select username, password, null from xz712a_users where username like "%admin%" limit 1,1--

The union operator will then combine the two select statements and additionally extract the username and password. The "limit 1,1" will ensure that the database only returns the first row back to the application.


The whole query processed by the application now looks as below:

/* create database connection */

$sql = "select id, name, price from 1ux9a7_products where product_id = '1' union select username, password, null from xz712a_users where username like \"%admin%\" limit 1,1--'";
$result = $conn->query($sql);

/* process result further, expecting only 1 row returned by the database */

We have now exploited a SQL injection vulnerability and are presented with the values stored in the username and password columns.


How do you prevent an SQL injection attack?


Thankfully, there are lots of ways to help make your SQL database secure and prevent attacks from happening in the first place. Here are a few things to implement or consider:

  • Prepared or parameterised statements: These are predefined actions for your SQL database to perform, with fewer places for a hacker to alter, so it's harder to 'trick' the system into granting access. With these, you aren't able to alter the query — only the parameter attached to it. This makes your database much more secure.

  • Testing for SQL injection vulnerabilities: Our team can scan your code to see where vulnerabilities lie and subject your systems to friendly SQL injection attacks. This can help determine how vulnerable you are to threats, and steps to take to ensure you’re better protected for the future.

  • Programming language: Whichever you use, it’s possible to create vulnerable applications in them all. Ensuring a developer is aware of SQL injection attacks and vulnerabilities, and using the latest versions of programming languages and libraries, can help build security into your application or website from the start

What’s the best defence against SQL Injection attacks?


While parameterised statements and pentesting should always be prioritised, it's good practice to implement additional defensive measures wherever possible. These include:

  • User input validation: This is where an algorithm essentially tests the data to make sure it’s valid — for instance, that the password contains eight characters or needs to include a special character. Thinking back to our SQL Injection example, had the system used user input validation, the algorithm would have recognised that there weren’t enough characters to be a real password and not granted access.

  • Allowlisting characters: These only allow certain approved characters to be used during the log-in process, making it harder for a would-be attacker to manipulate an SQL query. Allowlisting isn’t as user-friendly, however, and needs to be tweaked for each field (so, for example, only allowing numbers in an ID field, but allowing numbers and letters in a password field).

  • Denylisting characters: The opposite of allowlisting, this doesn’t allow certain characters to be used. However, there can be ways to bypass this method, and it’s generally not considered to be as secure.

  • Errors: Turning these off and ensuring they aren't relayed to a user during the log-in process can help prevent valuable information from being revealed to an attacker.

  • Server-side input length: This method can help secure input fields on web forms by limiting the allowed characters for each section. For example, a password or ID could be restricted to eight characters, while a name might be 15 or 20.

  • Web application firewalls: These offer an extra layer of defence as they essentially check SQL statements for injected code or special characters or keywords. Application firewalls can also gather data on attempted attacks, learn which rules might be needed to better protect your application (crucial for application security), and even make recommendations.

  • Network intrusion detection systems: logging and monitoring traffic using systems like these can flag and thwart attack attempts, keeping data safe.


How do I secure my application against SQL injection attacks?


SQL injection vulnerabilities can lead to catastrophic consequences, from hackers simply accessing data, to deleting it, modifying it and even extracting it. You might not realise your application is vulnerable either, which means you might be forced to be reactive, rather than proactive, about attacks - not to mention you could be putting your business and customer data at risk. Through penetration testing, ethical hacking, and other methods, we can spot vulnerabilities and help with application security to protect valuable data. Read about what we do, see how we could help your business or book a demo today, and stay one step ahead of hackers.