Basics - Web - SQL-injection

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.

Example

Consider a web service that requires users to login. Here, the user is asked to provide their <email> address and their corresponding <password>. 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 email AND 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 <email> and <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 thijs@email.edu and their password Thijs'_s3cure_p@ssw0rd, which contains a ' character. The server takes these credentials and tries to execute the following query:

SELECT * FROM `users` WHERE `email` = 'thijs@email.edu' 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 True. An example clause that is always True is 1=1. 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 --.

Try it yourself

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.

Login

Database Query

CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT, `email` VARCHAR(45) NULL, `password` VARCHAR(45) NULL, `name` VARCHAR(45) NULL, `grade` VARCHAR(45) NULL, PRIMARY KEY (`id`)); INSERT INTO users (email,password,name,grade) VALUES ( 'thijs@email.com', 'P@ssw0rd', 'Thijs', 6); INSERT INTO users (email,password,name,grade) VALUES ('andrea@email.com', 'S3cr3t', 'Andrea', 9); INSERT INTO users (email,password,name,grade) VALUES ('herson@email.com', 'Costa Rica is world champion!!', 'Herson', 8); INSERT INTO users (email,password,name,grade) VALUES ( 'yoep@email.com', 'Crypt0_r0cks!', 'Yoep', 8.5); CREATE TABLE `secret` ( `id` INT NOT NULL AUTO_INCREMENT, `column1` VARCHAR(45) NULL, `column2` VARCHAR(45) NULL, `flag` VARCHAR(45) NULL, PRIMARY KEY (`id`)); INSERT INTO secret (column1,column2,flag) VALUES ('random', 'data', 'ctf{SQL_INJECTION_IS_EASY}'); CREATE TABLE `tables` ( `id` INT NOT NULL AUTO_INCREMENT, `table_schema` VARCHAR(45) NULL, `table_name` VARCHAR(45) NULL, PRIMARY KEY (`id`)); INSERT INTO tables (table_schema,table_name) VALUES ('tb_table', 'users'); INSERT INTO tables (table_schema,table_name) VALUES ('tb_table', 'secret'); INSERT INTO tables (table_schema,table_name) VALUES ('information_schema', 'tables'); INSERT INTO tables (table_schema,table_name) VALUES ('information_schema', 'columns'); CREATE TABLE `columns` ( `id` INT NOT NULL AUTO_INCREMENT, `table_name` VARCHAR(45) NULL, `column_name` VARCHAR(45) NULL, PRIMARY KEY (`id`)); INSERT INTO columns (table_name,column_name) VALUES ('users', 'email'); INSERT INTO columns (table_name,column_name) VALUES ('users', 'password'); INSERT INTO columns (table_name,column_name) VALUES ('users', 'name'); INSERT INTO columns (table_name,column_name) VALUES ('users', 'grade'); INSERT INTO columns (table_name,column_name) VALUES ('secret', 'column1'); INSERT INTO columns (table_name,column_name) VALUES ('secret', 'column2'); INSERT INTO columns (table_name,column_name) VALUES ('secret', 'flag'); INSERT INTO columns (table_name,column_name) VALUES ('tables', 'table_schema'); INSERT INTO columns (table_name,column_name) VALUES ('tables', 'table_name'); INSERT INTO columns (table_name,column_name) VALUES ('columns', 'table_name'); INSERT INTO columns (table_name,column_name) VALUES ('columns', 'column_name');

Advanced queries

As you may have figured out, we can use the example query above to list all name and grade values from the users table if we make sure that the WHERE clause always evaluates to True. 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 <password> input incorrect'; <NEW QUERY>;--, we get the following output:

SELECT * FROM `users` WHERE `email` = 'thijs@email.edu' 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 attack. The 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:

SELECT * FROM `users` WHERE `email` = 'thijs@email.edu' AND `password` = 'incorrect' AND 1=0 UNION
SELECT * FROM `<other_table>`

In this example, the query before the UNION statement will not return anything because AND 1=0 will always be False. However, we do retrieve all data from the <other_table>. There is one catch with the UNION statement, namely that the number of fields returned by both statements must be equal. Therefore, if the original statement returns two fields (e.g., name and 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` = 'thijs@email.edu' 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>`

Challenge

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 ctf{<flag_token>}.

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_schema and table_name that list the tables accessible in the database. Additionally, the information_schema.columns table contains information about the table_name and 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 information_schema.tables as tables and information_schema.columns as columns.