SQL Injections

Lecture Notes

This lecture will present an overview over SQL Injections with the multiple variants

Download here

Practical tasks

The objective of this lab is to explore Injection Attacks, in particular SQL Injection. This guide will be split into two parts. The first will make use of BWAPP, while the second part will focus on exploiting an actual system that suffers from these vulnerabilities.

If you require assistance remembering some of the SQL syntax, check the SQL Zoo

For many of this exercises it is relevant to use the OWASP Zed Attack Proxy. This tool will interact with your browser, either directly or through a HTTP Proxy, and allow you to capture, craft and send requests as required for each exercise.

bWAPP

bWAPP is a free, open-source and intentionally unreliable web application, or a web buggy program. It helps security enthusiasts, designers and students discover Web bugs and stop them from doing so. bWAPP plans for positive penetration tests and cyber ethics initiatives.

Download it from the bWAPP website, or run a docker container (we advise this approach)

docker run -d -p 80:80 raesene/bwapp

After this step, just point your browser to http://localhost/install.php, install bWAPP and follow the guide.

When the installation is completed, head to the website and create a user. Enter any data you wish.

Solutions for the bWAPP challenges are available at:

The SQL Injection Challenges

In order to be able to select a challenge, there is a registration requirement. However, users have the possibility to register with any data they wish. Afterwards, an SQL injection task can be selected from various ‘bugs’.

GET/SEARCH

For the selected (GET/SEARCH) SQL-Injection, information about available movies is displayed in a table when entering a movie title. Here you can search for one or more movies. As a result of the search, the movie details are displayed. If the input button is clicked without entering a search term, all movies are displayed. The set goal is to get a user password which seems to be possible as we have a table.

When a table is present, there is always the possibility of filling it with user specified data and obtain information through the said table. However first we must find how data is handled and what is vulnerable.

Detect the vulnerability

bWAPP is asking us to search the database for our favorite movie which contains the word ‘iron’ and the search field gives the result ‘iron man’. In this case there is a high probability that this query contains some columns.

The select-command is something like SELECT column1, column2, column3, column4 from table where movie LIKE '%". $userinput."%'

Task

  • Find if the field is vulnerable and what kind of SQL Injection is available

Find the number of columns

If data is to be obtain, the number of columns must be known to the attacker. Only after this step he can put the information in a format compatible by the existing code. We know that the table has 5 columns, but we do not know how many others are fetch from the database. This is a trial an error task, which can be done in several ways. One that works is using the ORDER BY column_number clause.

Tasks

  • Define a strategy to find the number of columns. How many columns we have?
  • How many columns we have?
  • What kind of SQL Injection was used?

Uniting queries

Until now we know the number of columns and we suspect what the query looks like:

SELECT column1, column2, column3, column4 from table where movie LIKE '%". $userinput."%'

We also know that we have a table filled with information from a database. What we want is to use it to exfiltrate information from another table. But first it is important to consider the UNION keyword. When used after a SELECT statement, the UNION statement allows to concatenate the results from another query into the first one.

That is, the first query is executed and it is united with another query. This second query will be controlled by the attacker. An important issue is that the number of columns must match. While we could do ... UNION SELECT 1,2,3. The second SELECT clause must have the first number of columns. Because we already know it, we can move forward.

Tasks

  • Add a second query with the adequate number of columns

Exfiltrate Data

Now that it is possible to concatenate information, it is trivial to extract data from other tables. Instead of issuing a SELECT 1,2,3 you can actually address existing tables.

Tasks

  • Extract information from users
  • Obtain all user information, including passwords
  • Obtain the list of databases
  • Obtain the list of tables
  • Load a file from the server

Other SQL Challenges

The remaining SQL Challenges are also very relevant to this lab, and the mostly explore the same attack, but present different interfaces, or require different SQL Injection types.

Tasks

  • Solve the SQL Injection - Blind - Boolean Based
  • Solve the SQL Injection - Blind - Time-Based
  • Solve the SQL Injection (GET/Select)
  • Solve the SQL Injection (POST/Search)
  • Solve the SQL Injection - Stored (User-Agent)

This tool also exploits a common Pitfall: Developers frequently forget about different interaction patterns, or consider that data may be mangled. One good example is the GET/Select exercise: the select field contains a limited number of values, but an attacker may introduce more and conduct an attack this way.

Solutions for these challenges can be found here. Use them to guide you through the challenges.

Juice Shop

OWASP juice shop login fields are vulnerable to SQL injection, which enables access to unauthorized access to the system. Let us inject SQL into the login field to bypass the login and login as the first user in the database.

First, create an error by giving ' as input to the email field, and any string to the password field.

Check the Response in the browser Network tab. You can see the SQL query used in the login.

"SELECT * FROM Users WHERE email = ''' AND password = '698d51a19d8a121ce581499d7b701668' AND deletedAt IS NULL"

Here we used ' in the email input field to cause an SQL error.

Now we know the SQL query related to logging in. We can send ' OR TRUE -- as email input and any string as a password.

"SELECT * FROM Users WHERE email = '' OR TRUE -- AND password = '698d51a19d8a121ce581499d7b701668' AND deletedAt IS NULL"
  • ': character close the email string.
  • OR: is a SQL query
  • TRUE: is a boolean value
  • --: will comment out the SQL query after the TRUE

So, now the SQL will check for email = '' or true which is always a TRUE statement.

What user did you login to?

This SQL injection will log us as the first user in the database. You can easily find the logged-in user details when you check the browser Network tab.

Now we are logged in as Admin!!!

SQL Injection Attack Lab

This exercise, provided by SEELabs (https://seedsecuritylabs.org/Labs_20.04/Web/Web_SQL_Injection/) contains a simple application and a database. You can use docker compose to setup a personal environment and correlate what you observe with the actual code.

In this lab, it was created a web application that is vulnerable to the SQL injection attack. The web application includes the common mistakes made by many web developers. Students’ goal is to find ways to exploit the SQL injection vulnerabilities, demonstrate the damage that can be achieved by the attack, and master the techniques that can help defend against such type of attacks.

To setup the lab, obtain this file, unzip it and start it with docker compose up --build -d.

To exploit the application from the webpage, try providing admin' # as the username. This should allow you to detect wether the page is vulnerable or not. You can also follow this approach from the command line, using curl, wget or even a python script using the requests package.

curl http://localhost/unsafe_home.php?username=Admin%27%20%23

You can be more creative and use the vulnerability to modify your own salary. Employees can only update their nicknames, emails, addresses, phone numbers, and passwords; they are not authorized to change their salaries. If you want to increase your own salary, exploiting the SQL injection vulnerability in the Edit-Profile page may allow it. The salaries are stored in a column called salary.

Use the statement in the NickName field: ', Salary=10000 where name = ‘Alice’ # and then update it.

You can also change others salary. Use statement in the profile editor: ', Salary=1 where name = ‘VICTIM’ #

Finally, you can change others passwords. The passwords are stored in SHA1 format. Therefore you can create a password to a user, and exploit the site using ', password=(SHA1 Password) where name = ‘VICTIM’ #.

The countermeasures for this attack are Prepared Statements. The code includes a safe version at safe_home.php. Use this home and check if the attacks are possible. In order to isolate the countermeasures, you can copy lines 70-80 from safe_home.php into lines 70-100 of the unsafe_home.php file. If you do this you will need to rebuild the docker images with docker compose up --build --force -d

Wordpress

Enumerate the network or use this host: http://10.110.2.176/

One Use tools such as WPScan, but that will not be required as careful observation will be enough. You can register a new user and comment, but do not change the configurations in the web site.

Consider the following software repositories.

Tasks

  • Enumerate the known SQL Injection vulnerabilities present
  • Explore the vulnerabilities with a custom payload
  • From the information present in the Internet, and the source code, write a small description of what motivated the vulnerability. Describe the exploitation path, why it happens, what is the impact, and the overall risk.
  • Develop a small proof of concept exploit code (usually a simple python script with the requests module is enough)

Analysis

An instance of Sonarcloud (a source based static analysis software) was setup with the code providing these applications. You can go through it to identify vulnerabilities directly related with the exercise, together with an explanation. For bWAPP check this url and correlate what happens with the code you see.

The Wordpress vulnerabilities are more subtle and require a careful analysis.

adRotate

Lets start with the adRotate plugin. The vulnerability is described here. It doesn’t provide much information, but a following link does provide.

From this we learn that the issue occurs when we call:

http://example.com/wp-admin/admin.php?page=adrotate-statistics&view=group&id=1+AND+SLEEP%2810%29

or

http://example.com/wp-admin/admin.php?page=adrotate-statistics&view=group&id=2+AND+1%3D%28SELECT+IF+%28+GREATEST%28+ORD%28MID%28%40%40version%2C+1%2C+1%29%29%2C+1%29+%3D+53%2C+1%2C+0%29%29

As described, the vulnerable field is the id field, which somehow is not sanitized properly. Because the error is more subtle, Sonarcloud failed to identify the issue.

The vulnerable code can be found starting at adrotate-statistics.php:L159 On that line we see that the arguments are compared in order to determine the action.

Later in that file, there are several example of correct access to the database, such as in line 189

However, the same developer made several mistakes in other lines. Can you find them? Compare that line with a vulnerable one at line 407. As you see, the first uses prepared statements, while the last uses a straight approach towards building a query.

wpDiscuz

The vulnerabilities in this plugin are even more subtle, as the plugin doesn’t construct SQL queries directly. However, the parsing mistakes are clear.

Start by analyzing line 686 of the class.WpdiscuzCore.php file. These lines start by obtaining arguments provided by the HTTP client to the server. In these lines you can see that postId is validated and forced to be an integer. If it doesn’t, 0 is returned. Follow the remaining lines and look for mistakes in parsing arguments (Hint: line 692 )

This value is then used in other internal functions, such as here , or here. In neither part we have an issue because no SQL is involved.

If you follow the code you will see that the arguments are being built and then passed to a function get_comments in line 876. This is an internal function of Wordpress, documented here which has some safeties.

However, sometimes the developer is using direct queries, such as in line 973, which is triggered with some configurations. When this happens, because the orderby is vulnerable and can contain an SQL expression, the attacker may exploit it.

External exercises

Relevant Software:

Resources:

Previous
Next

Last updated on 14 Sep 2022