SQL injection examples for practice

Welcome to this new episode of the OWASP Top 10 training series. In this blog post, you are going to practice your skills on some SQL injection examples. In the first SQL injection example, we will exploit an error-based use case. Then, we are going to exploit a blind use case in the second SQL injection example. Finally, you will learn how to use automated tools to find SQL injections vulnerabilities.

I will add other injection vulnerability types in the future, so make sure to subscribe to the newsletter at the bottom of the page. I send updates to all subscribers each Friday.

I’m assuming that you landed on this page because you are already familiar with the theory behind SQL injection. However, if you don’t, I highly recommend that you read this blog post where I explain SQL injection in theory with some theoretical SQL injection examples.

SQL injection tutorial prerequisites

In order to properly follow along this hands-on tutorial, you need OWASP WebGoat listening on port 8080. You also need either OWASP Zap or Burp Suite properly configured with your Web Browser.

SQL injection example 1: Error-based

Let’s start with WebGoat’s challenge 10 under the SQL injection menu (intro). It allows a user to see how many times a user has been logged in. The goal is to list all users data. If we input a single quote in the Login Count and the User_id fields, we notice an error getting back to us as shown below.

The error indicates two things. Firstly, this is an error based SQL injection. Secondly, the injection happens in the userid column. The Login_count is protected using a prepared statement.

Now, let’s craft a payload which will get us all users data. The idea is to build upon the error message which reveals the whole query, and inject a payload in the WHERE statement which always returns true. Such query could look like the following:

SELECT * From user_data WHERE Login_Count = 0 and userid= 1 or 1=1;-- 

Do you see what should be the value of our payload? Correct! it should be a number in the Login_count input and 1 or 1=1;– in the userid.

Since the fields in the SQL query are numerical, we don’t need to use any quotes. The following screenshot shows the exploitation results.

SQL injection example 2: Blind injection

We will solve challenge 5 of the SQL injection (advanced) menu. The goal is to exploit the Signup feature to gain access as user Tom.

If we input fields as shown below, we can see a weird error saying that the user already exists! This doesn’t make sense since we know for a fact that the user ‘ or 1=1;– doesn’t exist.

What if the SQL query responsible for this error is a SELECT statement which verifies if the user already exists? This would make sense right? If our assumption is correct, our malicious payload always returns true, which tells the back-end server that  the user already exists. To be sure, you can replace 1=1 with 1=2, and you will get your user created, as seen below.

Consequently, we can assume that the injection happens in a select statement.

The exercise that we did is called a black box testing. We can verify our assumption using a white box approach. This is explained in the Youtube video.

This is clearly a blind SQL injection. In fact, there is no direct error message, but a hint which we can use to infer data in the Database. The idea behind exploiting this SQL injection is to inject tom’ AND PAYLOAD;– . When PAYLOAD is true, we will get the first message saying that the user exists, otherwise, our user will be created.

We will use this hint to systematically extract tom’s password. Let’s analyze the following payload:

tom' AND password like 'a%';-- 

This will return true if tom’s password starts with a, meaning that we will get user already exists error message. Otherwise, our user will be created. We can also use SQL’s length function to first find tom’s password length, then use the function substring to get tom’s password one character at a time, but we will see this in action using sqlmap.

Exploiting SQL injection using sqlmap?

Introduction to Sqlmap

Sqlmap an open source penetration testing tool that automates the detection and exploitation of SQL injection. I use when I suspect an endpoint to be vulnerable, or to automate the process of dumping sensitive data from the database server. 

For beginners, you can use sqlmap –wizard option, then follow the instructions.

I personally prefer to play with sqlmap options. In fact, sometimes you have to tweak them in order to detect and exploit the SQL injection vulnerability.

The basic usage of Sqlmap is a GET request with GET parameters. The following command will test for an injection against param1 and param2 on the given URL:

sqlmap -u http://url.to/vulnerable/endpoint?param1&param2

Sometimes, you deal with a POST request, or a request with JSON data. Although you can use dedicated options to deal with each case, I personally prefer to use the -r option. Basically, I capture the whole HTTP request from OWASP Zap or BurpSuite, and I paste it in a file. Then, I run:

sqlmap -r request_file

If you need an in-depth sqlmap tutorial other than what you found on the net, drop me a comment with the things you need to learn about it. You can also watch the video above where I demonstrate sqlmap step-by-step.

Sqlmap in action

To retrieve Tom’s password, we will tell sqlmap that our hint is the string try to register with a different username, the technique is boolean and the vulnerable parameter is username_reg. We are also telling it to use verbosity level 3 to display the payloads which are being tried. Notice how they are similar to what we discussed above.

sqlmap -r request_file -p username_reg --technique=B --string "try to register with a different username" -v 3

After running the command above, we would get a positive result as seen below.

From here, we will extract the current-db.

sqlmap -r request_file -p username_reg --technique=B --string "try to register with a different username" --current-db

The database name PUBLIC as you can see below.

From here, we will extract the list of tables .

sqlmap -r request_file -p username_reg --technique=B --string "try to register with a different username" -D PUBLIC --tables 

It seems that the CHALLENGE_USERS is the table to hold the users’ data. We will list the columns of that table. Notice how sqlmap will try to automatically brute force the column names.

sqlmap -r request_file -p username_reg --technique=B --string "try to register with a different username" -D PUBLIC -T CHALLENGE_USERS --columns

Finally, we will get Tom’s password by dumping the columns needed using sqlmap:

sqlmap -r request_file -p username_reg --technique=B --string "try to register with a different username" -D PUBLIC -T CHALLENGE_USERS -C email,password --dump

As you can see, sqlmap is powerful when it comes to discovering and exploiting SQL injection. You can write your own script to retrieve Tom’s password, but it would take you a bit more time. I encourage you to do it first, it is a great exercise.

How to configure OWASP Zap to detect SQL injection?

We’ve covered how to install and configure OWASP Zap. If you haven’t configured it yet, go ahead and do it before continuing.

In this SQL injection example, we are going to configure Zaproxy to detect onlu SQL injections using the Active scan. This is a great opportunity for you to discover some Zaproxy options. Please follow the instructions as follows.

1. Go to your target application and capture a vulnerable request. In my case, I am using OWASP WebGoat. Specifically, I’m targeting the challenge 11 of the SQL injection (intro) menu, under the Injection menu. This is the form I’m going to submit.

2. Make sure you capture the request using OWASP Zap. You should see it under the History tab .

3. Right click on the highlighted POST request and choose attack, then Active Scan.
4. Under Scope tab, make sure the option Show Advanced Options is checked.

5. Go to the Policy tab and turn off all attack vectors.

6. Now, go to the Injection menu on the left.
7. Scroll down to the SQL injection test and choose a default Threshold .

8. Click “Start Scan”. A new tab named Active Scan will appear next to the History tab.
9. When finished, Click the plus button next to the Active Scan tab and choose Alerts to show the scan results.
10. Notice that the SQL injection has been detected.

How to configure Burp Suite pro to detect SQL injection?

We are going to exploit the same previous SQL example, this time using Burp Pro.

Burp Suite Community Edition doesn’t ship with Burp Scanner, but Burp Pro does. If you have a Burp Pro License, please follow the instructions below.

1. Verify that you have properly setup Burp Suite.
2. Capture the same request as we did above with Zaproxy.

3. Right click on the request and choose Scan in the contextual menu.
4. In the Scan configuration menu, click on New.

5. Give your configuration a name, for example SQL injection, and choose only SQL injection in the Issues Reported panel.

6. Click on Save, you should see your newly created configuration, click Ok.
7. Go to the Dashboard tab. You should see a new task running. When it finishes, you can see that Burp has found two SQL injection issues.

8. Click on the View Details button on the right to see more details on the vulnerable parameters and the requests being sent.

That’s it. I hope you learned how to exploit SQL injection from the examples above. In the next episodes, we are going to discover more OWASP Top 10 vulnerabilities on WebGoat and Juice Shop. Stay tuned with the Friday newsletter.