SQL injection Explained with Example

SQL database is one of the most used and the most popular databases worldwide. Many websites today use this to store user information like email, username and password.

SQL itself stands for Structured Query Language which can be used for creating, storing, manipulating or deleting the data in the database. The database that uses SQL is considered as a relational database, which means that the data stored may have a related structure or be related to one another.

Unlike non-relational databases (NoSQL), this type of database stores the data inside a table containing rows and columns. This structure makes it easier for the developer to navigate as well as more familiar for humans as we usually categorize data into an organized table.

For this reason, many databases adopt this language, such as MySQL, PostgreSQL, Microsoft SQL Server, and MariaDB. Because so many websites use SQL, many attacks usually aimed at this type of database by taking advantage of website vulnerabilities.

What is SQL injection?

SQL injection is the type of cyber-attacks that uses website vulnerability by injecting SQL syntax in order to accomplish unauthorized data manipulation in the database. The concept is quite similar to XSS (Cross-Site Scripting) attack. While XSS mostly targets the frontend, it targets the backend of the website.

In XSS attacks, the script is usually based on JavaScript while SQL injection is based on SQL. The purpose itself can be the same but done in different methods. For example, in XSS attacks, the malicious code may be injected into the website in order to redirect the actual user into a “fake” page, so the attacker can get user credentials like username and password.

Using SQL injection, the attacker can gather user information directly from the website’s database without having to “lure” the user in the first place. This kind of attack can be extremely detrimental as the attacker possibly deletes the entire database at once. At some point, the attacker may also make an illegal account for himself and use it as a backdoor to access some publicly unavailable features.

How does SQL injection work?

As usual, we will use the actual example so that you can take a good grasp of it.

But first, let me remind you this is for educational purposes only. Doing this on any website would be illegal without prior permission from the owner. If you want to, do it only on your own system.

I am telling you this because I believe that it is better to know how it works so you know how to protect yourself and prevent such incidents from happening in the future. If you do want to follow along, you will need OWASP Juice Shop on Docker and Burp Suite. Also, I will be doing it using Kali Linux.

In addition, Kali Linux comes with a pre-installed Burp Suite which is easier to start with. If you are not familiar with Burp Suite, it is a tool which is frequently used by cyber security experts for penetration testing a website application. In this case, we will use it to intercept the website API request and use the SQL code to retrieve the data we need from the database.

Okay now, let’s begin.

Initial Setup

If you don’t know how to install Docker, you can follow the instructions here. To start the container, you can follow the instructions here and go to the Docker Container section. Now, execute Burp Suite application and just click “OK” if JRE notification appears. And then, select “Temporary project” and “Start Burp”.

In order to use Burp Suite, you need to set your web browser proxy to 127.0.0.1 and set the port to 8080 as this application listens to this port by default. To do this, just go to your web browser setting and type “proxy”. After that, choose “Manual proxy configuration” and set the HTTP proxy value with the value stated before and restart your browser.

Steps

1. Go to the URL http://localhost:3000. If you have installed everything correctly, you have to be able to see something like this.

2. Using Burp Suite go to the “Proxy” tab and click “Interception is on”. Refresh your webpage and see the data comes in. You can use “Forward” the data until you get something like this.

GET /rest/products/search?q= HTTP/1.1
Host: localhost:3000
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Firefox/102.0
Accept: application/json, text/plain, */*
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate
Connection: close
Referer: http://localhost:3000/
Cookie: wp-settings-time-1=1669897897; language=en; welcomebanner_status=dismiss; cookieconsent_status=dismiss
Sec-Fetch-Dest: empty
Sec-Fetch-Mode: cors
Sec-Fetch-Site: same-origin
If-None-Match: W/"3250-29q5NwRJpUgGq3tZa3FERP648/E"

Why are we looking into this? Let me give you a quick explanation. The GET /rest/products/search?q= is used for querying the data from the database. You can try changing it to something like GET /rest/products/search?q=orange which simply means that you are asking the database to serve you any data with the word “orange” in it. This is the normal way of how a web page should behave. To do the request, some characters need to be encoded first, so we will use encoding before injecting the code.

We can start to inject our code. Send the data to the repeater by clicking “Action” and click “Send to Repeater”. Now insert quote character (‘) and click “Send”. If you get response HTTP/1.1 200 OK, it means that the connection is successful so we can use this.

This character is usually used by the developer to close the SQL statement and by doing so, we have reopened this up. This is what we call a payload. In fact, you could try many payloads at once by using Burp Suite to search for vulnerability. But it is not our main concern here, so I won’t go any further into this.

3. Still in the “Repeater” tab, insert the code below and click “Send”.

'ORDER BY 1 --

It should give you an error response like HTTP/1.1 400 Bad Request. This is why we need to encode the code before sending it using the “Decoder”.

To do that, simply block the code, right click, and “Send to Decoder”. Click “Encode” and choose “URL” and overwrite our code back to “Repeater”. You should see the response like this.

HTTP/1.1 500 Internal Server ErrorAccess-Control-Allow-Origin: *
X-Content-Type-Options: nosniff
X-Frame-Options: SAMEORIGIN
Feature-Policy: payment 'self'
X-Recruiting: /#/jobs
Content-Type: application/json; charset=utf-8
Vary: Accept-Encoding
Date: Sat, 03 Dec 2022 08:13:34 GMT
Connection: close
Content-Length: 341
{
 "error": {
   "message": "SQLITE_ERROR: near \"order\": syntax error",
   "stack": "Error: SQLITE_ERROR: near \"order\": syntax error",
   "errno": 1,
   "code": "SQLITE_ERROR",
   "sql": "SELECT * FROM Products WHERE ((name LIKE '%'order by 1 --%' OR description LIKE '%'order by 1 --%') AND deletedAt IS NULL) ORDER BY name"
 }
}

4. Check the error. As you can see, we know that this ((name LIKE '%' needs to be closed first. Thus, we can change our code to something like this.

‘)) ORDER BY 1 --

Encode it once again and overwrite the code on “Repeater”, send, and see the result. You should now see something like HTTP/1.1 200 OK get the actual data in the form of JSON which is what we want. If so, congratulations, you have been able to do the SQL injection.

Actually, this code is harmless as it is only used for sorting the result based on a certain column. The double dashes at the end will comment-out the rest of the command so it won’t be executed. We use 1 because we have no clue of the column’s name, so we use its ordinal position instead.

If you want to push it further, you might be able to retrieve the admin login account and password. If you are interested in this, please let me know so I can write about how I do it and the thought process along the way.

Conclusion

SQL injection exploits the vulnerabilities created by the developer. The attacker can use it to take users credentials, do any harm, or even take down the entire system. Also, this attack is on OWASP Top Ten security flaws hence very critical to understand.