SQL injection

Structured-Query Language (SQL) injection attack. SQL is standard language for storing, manipulating and retrieving data in databases.

Important notes in SQL

1. INFORMATION_SCHEMA

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges.

MySQL :: MySQL 8.4 Reference Manual :: 28.2 INFORMATION_SCHEMA Table Reference

INFORMATION_SCHEMA.TABLES

A particular metadata provided by INFORMATION_SCHEMA would be TABLES. This provides information about all tables and views within a database.

The TABLES table has these columns:

1. TABLE_CATALOG

The name of the catalog to which the table belongs.

1. TABLE_SCHEMA

The name of the schema (database) to which the table belongs.

1. TABLE_NAME

The name of the table.

1. TABLE_TYPE

BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table.

INFORMATION_SCHEMA.COLUMNS

Another metadata provided would be COLUMNS, which table provides information about columns in tables.

The COLUMNS table has these columns (only shown a few):

1. TABLE_CATALOG

The name of the catalog to which the table containing the column belongs.

1. TABLE_SCHEMA

The name of the schema (database) to which the table containing the column belongs.

1. TABLE_NAME

The name of the table containing the column.

1. COLUMN_NAME

The name of the column.

Information about INFORMATION_SCHEMA.TABLES retrieved from:

MySQL :: MySQL 8.4 Reference Manual :: 28.3.38 The INFORMATION_SCHEMA TABLES Table

Information about INFORMATION_SCHEMA.COLUMNS retrieved from:

MySQL :: MySQL Information Schema :: 4.8 The INFORMATION_SCHEMA COLUMNS Table

Types of SQL injection attacks

1. In-Band SQLi

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.

1. 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.

Possible feedbacks that can help us in a blind SQLi attack:

a) Boolean response based

b) Time based

• Using the built-in SLEEP() method

• The SLEEP() method will only ever get executed upon a successful UNION SELECT statement.

1. Out-of-Band SQLi

Out-of-band SQL Injection 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.

Example of Blind SQLi

Suppose there is a login form with input fields for username and password. The SQL query may something like this:

select * from users where username=<input> and password=<input> LIMIT 1;

As long as this SQL statement resolves to a true value, the login will succeed. Without proper validation, an attacker can maliciously inject SQL commands to force the query to be true without having a valid username/password pair.

In this case, the input to the password field would look something like this:

' OR 1=1;--

This input does the following:

• ': Escape from the password field

• OR: logical statement

• 1=1: A statement that always resolves to a true value

• ;: Indicates the end of SQL query statement

• --: Comments out everything after

Knowledge taken from the following TryHackMe tutorial:

TryHackMeTryHackMe

Practical challenge

TASK 8 from:

Burp Suite: RepeaterTryHackMe

Challenge Objective

Your objective in this challenge is to identify and exploit a Union SQL Injection vulnerability present in the ID parameter of the /about/ID endpoint. By leveraging this vulnerability, your task is to launch an attack to retrieve the notes about the CEO stored in the database.

1. 1'

• To force an error:

Invalid statement:

SELECT firstName, lastName, pfpLink, role, bio FROM people WHERE id = 1';

The information provided in the error message tells us about the SQL query used

1. 8 UNION ALL SELECT group_concat(column_name),null,null,null,null FROM information_schema.columns WHERE table_name='people'

• The value 8 (or any other values that does not match an entry in the database) is used to force the first part of the query to not return any results, so as to allow us to view the results from the subsequent UNION statement

• Displays all the columns in the people table

1. 8 UNION SELECT notes,null,null,null,null from people where id=1;

• The ID of the CEO is 1

• This query will retrieve the content stored in the notes