This is the first post in the Tools and Techniques category and I would like to start here with a small series on SQL injection technique. The first part of the series is about the basic principle of the attack. In the following parts, I will try to explain some special cases such as Blind SQL Injection and present a few mitigation options – i.e. preventing or mitigating attacks.
I have created a Git repository with a Docker-based training environment for SQL Injections. By downloading it you can follow the steps of this article step by step if you like. So if you have Docker and Git installed, you can use
git clone https://github.com/bugninja-de/SQL-injection-practice-course.git to clone the repository and get started.
What is SQL injection?
SQL injection is about injecting malicious SQL code into an application. Classically, the attacked application is a website, but potentially any kind of application that communicates with an SQL database and accepts any kind of input from the user is at risk.
The attack exploits insufficient filtering of user data on the one hand and poor or outdated methods of communication between the application and the database on the other.
Suppose we have a web application with a login page:
In the simplest case, user input would be passed unfiltered directly into an SQL query:
<?php $user = $_REQUEST['user']; $pass = $_REQUEST['pass']; $query = "SELECT id, username, password FROM users WHERE username = '$user' AND password = '$pass'"; $result = $mysql_db->query($query); ?>
If the user enters his username and password here, the SQL query would look something like this:
$query = "SELECT id, username, password FROM users WHERE username = 'testuser' AND password = 'testpassword'";
So if these inputs match the values stored in the database, its Id, username and password are stored in
$result. But is there no match to the user input,
$result remains empty.
But what happens if the user does not enter his username, but special characters like
' followed by SQL commands?
This is a very simple example of an SQL injection. With
' the “field” for the user name is closed and with
or 1 = 1 a SQL condition is inserted behind it, which is always true, thus for each database entry. With the
# – character a MySQL comment is introduced, which stops the program logic of the remaining mySQL query – everything following the
# – character is ignored by the server. So our SQL query looks like this:
$query = "SELECT id, username, password FROM users WHERE username = '' or 1 = 1 #' AND password = ''";
Remember: everything after the
# is ignored. This query gives us a result with id, username and password of all users stored in the
The docker based example only consider the first row of the result. Therefore we are directly logged in as admin:
In conclusion, the example is very simple, but I think that the principle becomes clear.
Depending on what kind of SQL server is used by the application, the syntax must be adapted. For example, there are different options for comments. Instead of the
# -character, the string
-- can introduce a comment in different SQL versions. Furthermore, the character that identifies a string within the SQL query can also be a
" character instead of the
' character, for example.
Of course, instead of the simple
or 1 = 1, more complex code can be injected, such as a
UNION query, which allows complex queries across different tables in a single vulnerable location. Furthermore, not only form fields are vulnerable. Sometimes user data is transmitted as GET parameters via the URL, sometimes visitor statistics are stored in a database and the user agent (which contains data about the browser and operating system used) is queried. In addition values stored in cookies can also be used in SQL queries. All this data could be modified by attackers and lead to the complete system being compromised.
In the next article of the SQL injection series, we will look at what an attack using
UNION ALL might look like.