SQL Injections
WHAT IS SQLi?
In this article, we’ll be referring to SQL injections as SQLi.
SQLi is a web security vulnerability that allows an attacker to interfere with queries between an application and its database. You and I use a lot of applications and websites. Especially shopping websites like Jumia, Amazon, Temu and AliExpress. To make orders with these websites, you need to make an account with your email and password. You also input your credit/debit card details within the application as well. These details are saved in a Database that the developer owns. Alongside the products in your cart, products that are available and products that aren’t even released yet.
An attacker can access your credentials and most likely compromise other websites and applications you visit, since everyone mostly uses the same email and password for everything. The attacker can also read, modify or delete the data and even execute commands on the host. Sometimes, the attacker can even escalate an SQLi attack to compromise the underlying server and other backend infrastructure.
Within this article, we’ll be talking about the various ways to detect SQLi vulnerabilities within an application and how to prevent them. But before we dive in, it’s essential you understand SQL basics and commands.
In each database, there are tables. Each table contains rows and columns. For instance, the developer may decide to create a table called ‘Users’ to save user credential information. Within this table, there are 4 columns i.e username, email, password, phonenumber. To access the email within this table, the developer writes: SELECT email FROM Users
The above command means:
SELECT email – selects the email column.
FROM Users – specifies which table to pull it from.
This command will return all the emails saved within the Users table. It is important to note that there are many relational databases, i.e., MySQL, PostgreSQL, MSSQL, and Oracle. The syntax varies between them but not widely. Once you understand the above command, it’s easy to follow along and learn the variations across other databases. For the purpose of this article, we will stick to MySQL syntax
REASONS FOR SQLi ATTACKS
The reasons why someone will perform this attack can be narrowed down to 4
Retrieving hidden data within the database: Certain information exists within a DB that is only meant for the dev team and not for public access. Information such as your email, passwords, and payment details. Attackers can get those and also retrieve much more important information that wasn’t supposed to be public, like the release date for GTA VI.
Subverting application logic: Attackers can use SQLi to modify the application logic. They can use a query to completely mess with the application. An example of this is the attacker logging into the administrator’s account without using the password. Only the username. The attacker does this by simply typing administrator ‘-- as his username within the login field. What this does in the background is: SELECT * FROM users where username = ‘administrator’--’ AND password = ‘’ The username administrator is entered, the extra ' closes the username string early, then -- comments out the rest of the query, so the password check never runs.
Examine the database itself: Every database has its own version, table names and schemas (A database schema defines the formal structure, data organization, and relationships within a database). The attacker can perform reconnaissance, find the version and look for vulnerabilities within that version.
Mass exfiltration: UNION is a SQL keyword. In SQLi, it can be used to exfiltrate data from the database easily. The attacker will be able to not just access 1 table. They can access multiple tables and get data from all of them.
KINDS OF SQLi ATTACKS
Single quote attack: This is the simplest kind of attack. Submit a single quote within the URL and watch it break. For context, when you send data through an application to the database, it reads it as a string or integer (information). So, when you send something it doesn’t have within its DB, it just gives a Not found. If a single ‘ throws an error or the application starts acting weird, it means it is reading your input as a command. Meaning the attacker can send SQL commands as input to attack the database.
UNION attacks: UNION allows developers and database owners to combine the results of 2 SELECT statements into one. The attacker can use it to execute additional SELECT queries on the database through the application and append the results to the original query. For UNION to work, 2 key requirements must be met.
The individual queries MUST return the same number of columns.
The data types (string/int) must be compatible between the individual queries.
Before the attacker performs any sort of UNION attack, he checks how many columns a query is allowed to return and which columns can hold string data.
To determine the number of columns, the attacker uses the ORDER BY command. It determines how many columns the database can return with each query. The attacker types in ‘ORDER BY 1-- within the URL. This means “sort by the 1st column”. The attacker keeps incrementing till they hit a database error. The error means that the column number you’re trying to sort by does not exist. The last number that didn’t error is the column count.
Then the attacker inputs the following command within the database:
‘ UNION SELECT NULL,NULL FROM information_schema.tables --
NULL is used because it isn’t sensitive to any datatype i.e string and integer, meaning it won’t throw any errors. Test each NULL position by replacing it with a string (could be any string i.e ‘hello’, ‘column_name’) until one return without error. That lets you know which column position you can pull text out from.
Information_schema.tables – MySQL, PostgreSQL and MSSQL have something called information_schema.tables. It provides information about the database i.e the list of tables within a database.
Once the attacker gets the tables within the database, they move to get the columns within the table they are interested in using this command:
‘ UNION SELECT NULL,column_name FROM information_schema.columns WHERE table_name=’TABLE_NAME’
Once the attacker has the columns, they can easily get the data. Within the table, if the columns returned were username, password, email, they can use the command:
‘ UNION SELECT email,password FROM TABLE_NAME --
NT: In this example, it can only return 2 columns for an individual query at a time.
Blind SQLi attack: The steps mentioned above might not work for some applications. They are subject to SQLi attacks but the application never shows you the query results or database error. The attacker cannot simply read it from the application, so they infer it:
Content-based (Boolean) blind: The attacker injects a condition into an input i.e cookie, URL, form fields, and observes whether the response changes. They simply add a wrong logic within the input i.e Cookie=38y73298532’ AND ‘1’=’1 -> Application is fine Cookie=38y73298532’ AND ‘1’=’2 -> Applications logic changes If the application responds differently, they know that they have a vulnerable database. They use that hint to get data. The hint could be that as a returning user, when they use logic 2, the ‘Welcome Back’ message within the application disappears. Manual check takes time so they use tools to automate the process i.e Burp Intruder.
Error-based: Similar to Content-based, but this time the attacker is focused on an error. The attacker uses this over content based when the application isn’t returning any content back. The attacker uses a conditional response to trigger an error: ' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a → ERROR ' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)='a → NORMAL In this example, 1=1 and 1=2 are just to demonstrate the technique. A condition that is true triggers the error while the false one doesn’t. In a real world scenario, the attacker replaces the condition with a yes/no question about data they actually want i.e ‘is the first character of the admin password ‘a’?’. If the page errors out, the answer is yes. If it doesn’t, the answer is no. The possibilities depend on the configuration of the database and the types of errors.
Time-Delay: These are for the hardest cases. When the DB doesn’t return anything through the application, no content change and the errors are handled properly, the attacker has run out of visual signs. So, they turn to using time. They invent a signal. If the condition they type is true, the database pauses for the time they defined within the command meaning the application doesn’t return anything till that time has elapsed. ' || (SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END)-- || - is a logical OR operator (For SQL DB)
Out-of-Band (OAST) Technique: When network isn’t stable, Time-Delay becomes more of a guessing game than an actual way to get reliable data. The attacker finally uses the OAST technique. The attacker makes the database ping a server that they control. If they receive the ping, then that means the query you inputted ran which means the database is vulnerable. Furthermore, they can send stolen data from the database through the ping.
Second-Order SQLi: All the attacks above are first-order SQLi. First-order SQLi occurs when the application processes user input from a HTTP request and incorporates the input into a SQL query in an unsafe way. With Second-order, the application takes user input from a HTTP request and stores it for future use. The owner of the database thinks it’s fine and moves on. Meanwhile, the data is planted, the app reads from the database and builds a new query with it.
BYPASSING FILTERS
It is important to note some developers use keywords to prevent users from injecting SQL commands in the URL but they fail to address JSON/XML characters. They might decide to ban the key word SELECT. The attacker may decide to send S ELECT. The WAF does lazy keyword matching and allows the input not realising S is S in XML. SELECT passes and the XML parser decodes it back to SELECT server-side .
HOW TO PREVENT SQLi ATTACKS
Preventing SQLi is quite easy. It requires 2 main things:
Parameterized queries
Allow Lists.
Parameterized queries allow you to send the query structure and data to the database separately. This means the query template is defined first with a placeholder while the user input is passed in afterwards.
SELECT * FROM products WHERE id = ?
? being the placeholder. If the attacker decides to write any sort of SQL command, it reads the input as data and not a command, checks the table against it. The input is treated as a literal value to match against, so it simply matches no rows. The malicious SQL command becomes nonsense. This is because the database compiles the template before it sees the data, so the data cannot alter it.
There is a limitation to placeholders. It can only work for data values (strings, integers). They cannot be used for the structural parts of the query i.e table names, column names, ORDER BY keywords.
The attacker can still input these because they are still a structural part of the query. ORDER BY is still being used to order products and prices within the database, column names that exist within the table can be looked up as well. So, the developer implements allow lists.
Allow Lists cover for this limitation. The developer defines a fixed list of permitted column names and checks the users input against it. The attacker cannot type in SQL commands because it picks from only the approved list. So, any unknown input is rejected or falls back to a default.
Least-privilege database accounts and input validation also help improve the security posture of the database.
CONCLUSION
SQLi are one of many application attacks. It’s important to not only know about them and their existence, but test applications for these vulnerabilities to avoid giving sensitive data to a vulnerable database. For developers, it’s important to know these attacks to safely secure your infrastructure. Thank you.
