Lab - SQL Injection | João Paulo Barraca

Environment setup

For this project you should use the virtual machine provided for the classes. We will be using software that is purposely vulnerable. Do not use your own laptop!. In alternative, you can get a compressed file with the docker-compose files to run the software. Get the files here

After the VM boots, go the Desktop/sqlinjection folder and execute:

sudo docker-compose up

This will start the entire environment and make a webserver available at http://127.0.0.1:8000 (inside the VM).

Check that you can start the containers and access the webpage with the Firefox browser.

In all exercises, adding the ?debug=true argument to the URL will print some debug information. If you are finding it difficulty to proceed, feel tree 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.

Injection Attacks

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.

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

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.

Download

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.

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)."')"

md5 is a hash function that is applied to the password to hide it.

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.

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? i 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.

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 behaviour 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., WERE 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:

sqlmap -u http://localhost:8000/blindsqli.php?user=bob

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

Authors

This content was developed by João Paulo Barraca and Vitor Cunha.

Lab - SQL Injection