Lab - SQL Injection
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.
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
.
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
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 theadmin
user. Any field or user can be obtained. Try withbob
. -
' or 1 in (select username from users where id=1) -- //
: Actually, you can dump all usernames by referring them to theirid
. Other fields could be used. -
' or 1=CAST((select group_concat(name) from INFORMATION_SCHEMA.INNODB_TABLES) AS SIGNED) -- //
: lists the tables of theinformation_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?
Further Reading
- http://pentestmonkey.net/cheat-sheet/sql-injection/mysql-sql-injection-cheat-sheet
- https://cwe.mitre.org/data/definitions/89.html
- https://dev.mysql.com/doc/refman/8.0/en/information-schema.html
Authors
This content was developed by João Paulo Barraca and Vitor Cunha.