SQL-injection is a technique where an attacker can execute (arbitrary) commands to a database. Such an attack is possible, if the software running on the server-side of a website does not properly filter the user input before sending it to a database. In such a case, an attacker can maliciously craft their message to change the query, and thereby gain control over the database.
Consider a web service that requires users to login.
Here, the user is asked to provide their
<email> address and their corresponding
Next, the website sends those credentials to the server, which then executes the following query:
SELECT * FROM `users` WHERE `email` = '<email>' AND `password` = '<password>';
This query returns the rows in the
users table of the database for which the
password values match.
Next, the server checks whether any rows are returned, and if so, allow the user to login and show the data of the particular user that was returned by the database (This is VERY BAD practice, passwords stored in a database should always be hashed and salted).
In this example, the
<password> values that the user provides are executed without any escaping or checks on the input.
Now suppose that the the user provides their email address
firstname.lastname@example.org and their password
Thijs'_s3cure_p@ssw0rd, which contains a
The server takes these credentials and tries to execute the following query:
SELECT * FROM `users` WHERE `email` = 'email@example.com' AND `password` = 'Thijs'_s3cure_p@ssw0rd';
Next, the server throws an error.
This is because the
' character in the password "closes" the passwords field, i.e. the SQL-statement evaluates whether the password equals 'Thijs'.
The next thing it sees (
_s3cure_p@ssw0rd'), is invalid SQL syntax, which the server cannot evaluate and will therefore throw an error.
When an attacker notices such a weakness, they can maliciously craft their input in such a way that the query executes arbitrary commands.
For example, if we want to see the data of other users in the database, we must craft input that ensures the
WHERE clause of the query always evaluates to
An example clause that is always
Furthermore, as an additional requirement, the SQL statement must also compile.
Fortunately, we can often employ tricks such as commenting out the remaining part of the query with the input
The login form below provides an interactive example that shows how a SQL query can be manipulated if the input is not filtered properly. You can play around with the email and password fields of the form and see how the SQL query changes depending on your input.
As you may have figured out, we can use the example query above to list all
grade values from the
users table if we make sure that the
WHERE clause always evaluates to
While, this information is interesting, other tables may contain even more interesting information.
Moreover, if an attacker really wants to do damage, he or she can wants to execute other arbitrary queries, such as removing a table or even an entire database.
In some cases, we can simply close the SQL-statement with a
; and start a different query that will be executed after the original one.
Given our previous example, if we provide the
incorrect'; <NEW QUERY>;--, we get the following output:
SELECT * FROM `users` WHERE `email` = 'firstname.lastname@example.org' AND `password` = 'incorrect'; -- Evaluates to False <NEW QUERY>; -- Executes <NEW QUERY>
Here, the database simply executes the
<NEW QUERY>, no questions asked.
However, some websites "defend" against these attacks by only executing the first query they receive.
In such cases, we can still perform attacks, specifically, we can perform a
UNION operator in a SQL query executes two queries together and returns the result of both queries to the user.
This operator is useful for programmers, but it is also a weakness that an attacker can exploit:
In this example, the query before the
SELECT * FROM `users` WHERE `email` = 'email@example.com' AND `password` = 'incorrect' AND 1=0 UNION SELECT * FROM `<other_table>`
UNIONstatement will not return anything because
AND 1=0will always be
False. However, we do retrieve all data from the
<other_table>. There is one catch with the
UNIONstatement, namely that the number of fields returned by both statements must be equal. Therefore, if the original statement returns two fields (e.g.,
grade), the second statement must also return two fields. If the
<other_table>contains two fields, we can simply use the
*operator. However, if the
<other_table>contains fewer or more fields, we will need to specify the field names that we want to return manually.
An attacker will not always know the different field names or even tables in a database.
Fortunately, databases keep tables containing information about the tables and columns stored in the database.
For example, in MySQL databases, the
information_schema.tables table contains the
table_name field with information about all tables stored in the database.
Therefore, in the previous example, we can gain the table information by executing the following query:
SELECT `name`, `grade` FROM `users` WHERE `email` = 'firstname.lastname@example.org' AND `password` = 'incorrect' AND 1=0 UNION SELECT `table_name`, `table_name` FROM `information_schema.tables`
Now that we know the other table names, we can use MySQL's
information_schema.columns with the
column_name field to get the fields (columns) of all tables.
Specifically, to get the columns of the table we would like to access, we can execute the command:
SELECT `table_name`, `column_name` FROM `information_schema.tables` WHERE `table_name` == `<selected_table>`
Of course, the best way to learn about SQL injection is with an introduction challenge.
Click button and paste the resulting data into the Schema SQL section below (no peeking!).
Next, you can use the interactive tool above to create queries.
Copy the queries you created into the Query SQL section below and click the
Run button to see how the queries are executed.
Your goal is to obtain the flag stored within the database.
You can recognise the flag as
Databases have internal tables that contain information about table names and columns stored in the database.
For example MySQL has the tables
information_schema.tables with the fields
table_name that list the tables accessible in the database.
information_schema.columns table contains information about the
column_name that lists the columns of each table.
In this example we don't have access to the internal MySQL tables.
However, we provide access to the tables