SQL injections might sound like a thing from the past, but in actuality it is still one of the most widely used methods of attack donned by hackers around the world. As stated in the Akamai Media Under Assault report a staggering 69.7% of all web application attacks between January 2018 and June 2019 were SQL injections. That is a LOT considering that it was supposedly first discovered by a man by the name of Jeff “Rain Forrest Puppy” Forristal back in 1998. Yes… ‘98.
The Basics
SQL injection type attacks belong to the injection type attack class where inputs from untrusted data sources are used to (dynamically) construct and execute commands such as SQL, LDAP, shell, XML, XPATH and code. Modern web application frameworks are generally able to avoid SQL injection-type attacks, which often gives beginner web developers a false sense of security and causes them to create serious vulnerabilities in live critical systems.
In case of SQL injections, the backend that executes crafted data is a relational database. The attacker sends crafted data to the application which handles it as SQL commands on the server side. Forwarding data from an untrusted source to the SQL database may lead to:
- Data leakage;
- Unauthorized modifications of data and data loss;
- The attacker gaining full control of the target server;
- other availability, integrity or confidentiality issues.
This is caused when the data entered by an untrusted person is used inside SQL statements without sanitization, the SQL statements themselves are constructed dynamically, or the SQL data types are not checked or properly casted.
To explain in more detail, we need to go even further into the basics…
SQL - Structured Query Language
SQL is a declarative language where the needed result is described (declared) instead of describing an algorithm like in imperative languages such as JavaScript or PHP. The SQL language is used for processing data in Relational Database Management Systems (RDBMS). Many web applications which have dynamic HTML as the frontend use an SQL database for storing data. An RDBMS can usually handle several databases (or schemas).
Basic Commands:
# In case of MySQL you can find all the schema names using the following command:
show databases;
# To use a specific database use the following command:
use <databasename>;
# The table names can be found using the following command:
show tables;
# A list of all the columns in a table can be found using the command:
desc <tablename>;
Select commands:
# Information can be retrieved from tables using a select command:
select column,list,separated,by,commas from schemaname.tablename where selection_criteria;
# Example:
select first_name, last_name from dvwa.users where user_id=1;
SQL Tautology
A tautology is a statement which is always true. For example: A or 1=1 is always true regardless of the value of A. Please see the following SQL statement and mind the or 1=1 at the end of the line.
select first_name,last_name from dvwa.users where user_id=1 or 1=1;
This makes the where clause return true for each row and all the users from the table are selected. Sometimes hackers need queries that do not return any data, in that case a false will be seeded to the where clause:
select ... where user_id=1 and 1=2;
Union Select
In SQL injections the union keyword is widely abused to retrieve the content of a database. Union allows composing a result set from different queries. There is a strong restriction in the number of selected columns in each query. It must be the same for each query. So if you select columns from 2 different tables then you need to add or remove selected columns in either table to make the number of columns match.
# Selecting 2 columns from 2 different tables:
select columnA1, columnA2 from databaseA.tableA union select columnB1, columnB2 from databaseB.tableB;
Malicious actors try to extend queries using union select from the information_schema to reveal inside information that can be used again with the next union select.
Comments In SQL
Sometimes hackers manipulate SQL commands by using commenting characters like # and -- to finish the query before it is supposed to.
MySQL supports three comment styles:
-
#comments everything out until the end of the line -
--comments everything out until the end of the line -
/* block comment */can be used inside the SQL command to comment out specific chunks
Examples:
select TABLE_SCHEMA from information_schema.tables; # This is a comment
select TABLE_NAME from information_schema.tables; -- This is a comment
select TABLE_NAME,TABLE_SCHEMA /* this is a comment */ from
/*
this is a multi line comment
*/
information_schema.tables;
Single Quotes in SQL
In the SQL syntax the character ' (a single quote) indicates the beginning or ending of a data string. For example, the SQL select select 'sample text' as result; returns the following result:
+-------------+
| result |
+-------------+
| sample text |
+-------------+
1 row in set (0,00 sec)
Trying to insert a single quote into a query like select 'sample ' text' as result; results in the following error, which could indicate that an injection is possible (more on that later):
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near '' as result;
SQL Injections
SQL injections come in many shapes and forms depending on what the malicious actor wants to achieve. It varies from bypassing authentication forms to executing operating system commands.
SQLi types:
- Error-based SQL injection
- Bypassing authorization forms
- Union-based SQL injection
- Blind SQL injection
- Chained SQL commands
- SQL injections inside insert/update/delete
- Regular expressions in SQL injections
- Loading/writing files
- Executing OS commands
- Using other channels instead of HTTP methods for SQL injections
Error-based SQL injection
Error-based SQL injections are used for revealing information about the existence of SQL injection vulnerabilities and the type of the database engine. For example, like mentioned earlier, if entering the ' character into a website’s form field causes an error then the SQL injection error exist. In case of MySQL, the result should look like this:
...- You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near ...
From the error message we can see that MySQL is used as the database. Additionally, you can get information about the field and table names used in the query. Error-based SQL injection vulnerability exist when error messages generated by the database’s backend are returned to the end user who can then use that intel to go on to perform other types of SQL injection attacks.
SQL Injection Authentication Bypass
SQL injections can easily be used to bypass security and gain access to web applications. Let’s say the following web form asks you for a username and password:
<form action="?page=login" method="POST">
<input type="text" name="username"><br>
<input type="password" name="password"><br>
<input type="submit" id="login">
</form>
Then the following PHP script is executed to authorize you:
$query ="SELECT * FROM shop_users
WHERE username='$_POST['username']' and password='$_POST['password']'";
$result = mysqli_query($connection, $query)
$user=mysqli_fetch_assoc($result);
if (!empty($user)) {
return $user;
The username and password sent are directly inserted into the SQL query without any input sanitation. If you enter the username bob and password LetMePass then the following query is executed in the database’s backend:
SELECT * FROM shop_users WHERE username='bob' and password='LetMepass';
Now if you set admin as your username and use an SQL tautology (mentioned above) in the password field, accompanied by a single quote and comment character to finish the query half way then you can ingeniously bypass the password check entirely.
Example value in the form:
randompassword' or '1'='1
Please notice that the last ' is added by the php code and the full executed command will be:
SELECT * FROM shop_users WHERE username='admin' and password='randompassword' or '1'='1';
If the query is successful, then the first user in the dataset is fetched and the hacker is logged in. If that first user is an admin then the attacker is now successfully logged in with superior privileges. But if the first user returned is an unprivileged user then the attacker can enter a random username and use the false statement to exclude the first user while entering the following into the password field:
randompassword' or '1'='1' and id <> 1; -- #
Please notice that -- should be followed by at least one character (like a space) and if the table has an id field then the query returns the second user.
Union-based SQL injections
Union-based SQL injections are used to reveal database schema names, table names and column names. This info can be used to write other queries, for example, for dumping data. As stated earlier, union select is possible if every select has the exact same number of columns in their result set. When web forms allow to enter data, display the result (like a search form), and enable the use of SQL injections, then union select is probably possible. To find out the right number of columns in a table you can use a trial and error method with the order by keyword. When you get an error for order by 3, then there are two columns.
Examples:
a' order by 1; -- #
a' order by 2; -- #
a' order by 3; -- #
When you know the number of columns in a table then you can union select from that table. However, you need to know the schema, table and column names first. The information schema is known to you, therefore you can collect information about tables, schemas and columns from it. Terminate the query in the form field with ' and append a union select terminated with the comment characters # --.
Example in case the original query returns two columns:
' union select TABLE_NAME, TABLE_SCHEMA from information_schema.tables; # --
Example in case the original query returns 4 columns:
' union select 1,2,TABLE_NAME, COLUMN_NAME from information_schema.columns; # --
While using union select you may need to select more fields than the first table contains. In that case the concat() function can be used to combine two or more columns into one result column. The concat(arg1,...,argN) function concatenates all arguments to one result.
Examples:
# Normal select
select concat(version(),' ',user());
# Union select injection
' union select concat(version(),' ',user()); # --
' union select concat(TABLE_NAME,'-',TABLE_SCHEMA) from information_schema.tables; # --
' union select concat(TABLE_NAME,'-',COLUMN_NAME) from information_schema.columns; # --
SQL Injection Mitigation
There are several ways to protect your systems against SQL injections:
- Use Prepared Statements (with Parameterized Queries)
- Use Stored Procedures
- Whitelist Input Validation
- Escape All User Supplied Input
- Enforce Least Privilege
- Perform Whitelist Input Validation as a Secondary Defense (detect unauthorized input before it is passed to the SQL query)
- Harden your http server with security measures (mod_security for Apache, configuration directives in Nginx, etc.)
In Conclusion
SQL injections are fairly easy to use as they don’t require a higher degree in programming or cyber security. All you need is a basic understanding of SQL queries and how they are implemented in code. Oddly enough, it is still one of the biggest web application vulnerabilities in the world.
