Updated October 2023
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 by hackers around the world. According to Statista, "SQL Injection is the main source of web application critical vulnerabilities found globally in 2022, with 33 percent, followed by 26.7 percent of internet facing critical vulnerabilities are due to cross site scripting (stored) attacks."
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.
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, and XPATH code. Modern web application frameworks are supposed to be impervious to SQL injection attacks, and this gives novice web developers a false sense of security that often leads them to create serious vulnerabilities in live systems.
In the 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:
This is caused when the data entered by an untrusted person is used inside SQL statements without sanitation, the SQL statements are constructed dynamically, or the data types are not checked or properly cast.
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 that have dynamic HTML as the frontend use a 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 ;
# 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 ;
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;
A tautology is a statement that 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;
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 on 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.
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 chunksExamples:
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;
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 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 injections are used for revealing information about the existence of SQL injection vulnerabilities and the type of the database engine. For example, as mentioned earlier, if entering the ' character into a website’s form field causes an error then the SQL injection error exists. In the 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 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:
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 halfway 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 fully 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 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; # --
There are several ways to protect your systems against SQL injections:
Conclusion
SQL injections are fairly easy to use as they don’t require a higher degree in programming or cybersecurity. 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.
Written by Roland Kaur, Content Development Lead