SQL (or Structured Query Language) injection, often referred to as SQLi, is an attack on a web application database server that causes malicious queries to be executed.
Let’s learn more about SQLi and how to stop it in our #FromZeroToHacker challenge.
| Table of contents |
| Introduction |
| What I have learnt today? |
| Stats |
| Resources |
Introduction to SQL Injection
SQL (or Structured Query Language) injection, often referred to as SQLi, is an attack on a web application database server that causes malicious queries to be executed.
When a web application uses input that isn’t validated by a user, there is a potential for to data be stolen, deleted or altered. SQLi is one of the oldest web application vulnerabilities.
What is a Database?
If you are not familiar with databases, let’s start learning the basics.
What is a database?
A database stores collections of data in an organised manner. A database is controlled by a DBMS (DataBase Management System) and there are two types: Relational and No-Relational. Today we will be using only relational databases, that you may be familiar with: MySQL, PostgreSQL, SQLite, MariaDB, etc.
A DBMS can contain multiple databases: For example, we could have one database called shop, another staff, a third called accounts, etc. Each database has its own series of tables, which stores multiple instances of an object (For example, a shop database with three tables: users, products and orders).
What are tables?
A table is made up of columns and rows (imagine a grid), like this:
Columns
Each column, or field, has a unique name per table. When we create the table, we set the type of data it will contain (Integer or numbers, strings or text, dates, etc.).
Setting the data type ensures that incorrect information, such as storing ‘Hello, World’ in a field reserved for dates, won’t be stored.
There is sometimes a field called key, which is an integer with the auto-increment feature enabled. This unique data for each row is useful as an ID which we can reference (Think of your personal ID or National identification number).
Rows
Rows, or records, are individual lines of data. Each row represents a unique object.
Let’s simplify this. If we want to store the users in a database, we create a table called users, the table would have the Username, Password and Email fields or columns, and each user registered would be a row in the users table.
Easy, right?
Relational vs Non-Relational databases
A relational database stores information in tables that oftentimes have shared information between them. A school database has a parents table and a student table, each one with its own information, but each student has one or two references to the parents table (a unique ID or primary key), pointing to their parents. That’s why this kind of database is called relational database.
Non-relational databases or NoSQL are databases that don’t use tables, columns and rows to store the data. Unlike relational databases, this type doesn’t have a set structure they have to abide to. One row can be a user and the next one could have data about a car. Popular NoSQL databases are MongoDB, Cassandra and ElasticSearch.
What is SQL?
SQL (Structured Query Language) is a language used for querying databases. The most common commands are retrieve (or select), update, insert and delete data. It is worth remembering that SQL syntax is not case-sensitive.
SELECT
The SELECT query is used to retrieve data from the database.
SELECT * FROM users;
The first word, SELECT, tells the database that we want to retrieve data, * tells the database that we want to receive all columns from the table, and FROM users tells the database which table we want to retrieve data from. The semicolon tells the database that this is the end of the query.
SELECT username, password FROM users;
Similar to the previous query, but this time instead of all the columns ( * ), we require just username and password columns.
SELECT * FROM users LIMIT 1;
Like the first query, but this time we LIMIT the results to just one (the first it founds).
SELECT * FROM users WHERE username='admin';
Now we introduced WHERE, used to lay down conditions (in this case, the username must be admin).
SELECT * FROM users WHERE username !='admin';
Similar to the last query, this time the username mustn’t be admin.
We can concatenate AND or OR:
SELECT * FROM users WHERE username='admin' OR username='jon';
We are asking for all the users where the username is either admin or jon.
SELECT * FROM users WHERE username='admin' AND password='p4ssword';
Now we want users with username admin and, at the same time, their username is p4ssword.
SELECT * FROM users WHERE username LIKE 'a%';
Select only users with username beginning with the letter a.
SELECT * FROM users WHERE username LIKE '%a';
Select only users with username ending with the letter a.
`SELECT * FROM users WHERE username LIKE ‘%mi%*’;
Select only users with username containing the string mi.
UNION
The UNION statement combines the results of two or more SELECT, to retrieve data from either single or multiple tables. The UNION statement must retrieve the same number of columns in each SELECT, the columns have to be of a similar date type (string, integer, date…) and the column order has to be the same.
It may be complicated to understand, so let’s see an example. Here, we have two tables, customers and suppliers:
SELECT name,address,city,postcode FROM customers UNION SELECT company,address,city,postcode FROM suppliers;
INSERT
The INSERT statement tells the database that we want to introduce a new object or row of data into the table.
INSERT INTO users (username,password) values ('bob', 'password123');
Here we are adding a new user, with username bob and password password123.
UPDATE
The UPDATE statement tells the database that we want to update an existing row (or more).
UPDATE users SET username='root', password='pass123' where username='admin';
Here we look for the username admin, and update their username to root, and the password is set to pass123.
DELETE
The DELETE statement tells the database that we want to delete one or more rows.
DELETE FROM users WHERE username='martin';
![[day_025_delete.png]]
Warning: If you don’t specify a condition, all the data will be deleted!
What is SQL Injection?
What is SQL Injection?
Now that we know how SQL works, finally, we can answer this question: A SQL injection is when a web application using SQL lets the user include data in the SQL query when they shouldn’t.
What does it look like?
Let’s say we have found an online blog where each blog entry has a unique ID number or key. The blog entries may be set to public or private, depending if they are ready for public release.
Here, we can see that this URL will retrieve the blogpost with the ID 1, from the database. The SQL may look something like this:
SELECT * FROM blog WHERE id=1 AND private=0 LIMIT 1;
You should understand this SQL statement after what we saw minutes ago.
SQL Injection happens when the user input is introduced into the database query.
Let’s think that the second article with an ID equal to 2 is locked as private. We shouldn’t read it with the normal URL:
But we are clever and we know we can manipulate the URL with something like this
Now, instead of:
SELECT * FROM blog WHERE id=2 AND private=0 LIMIT 1;
the SQL looks like this:
SELECT * FROM blog WHERE id=2;-- AND private=0 LIMIT 1;
The semicolon has marked the end of the SQL statement, and the two dashes (—) treat everything afterwards as a comment:
SELECT * FROM blog WHERE id=2;
And this will return the article with an ID of 2, no matter if public or private.
This is just an easy example of what an SQL Injection vulnerability can do. There are 3 types: In-Band (the one we just saw), Blind and Out of Band.
In-Band SQLi
In-Band SQL Injection
In-Band SQL Injection, also called ‘Classical SQL Injection’, is the easiest type to detect and exploit. In this type, the attacker receives the result as a direct response using the same communication channel. If the attacker performs the attack manually using a web browser, the result will be displayed in the same web browser.
Error-Based SQL Injection
This type is the most useful for easily obtaining information about the database structure, as the error messages are sent back to the web application. It can be used to enumerate a whole database.
Union-Based SQL Injection
This type uses the SQL UNION operator to return additional results to the page. It is the most common method to extract large amounts of data via SQLi.
Blind SQLi – Authentication Bypass
Blind SQLi
Unlike in In-Band SQLi, we get no feedback from the web application, as the error messages are disabled. Despite that, the injection still works regardless.
Authentication Bypass
One of the most straightforward Blind SQL injection is when bypassing authentication methods such as login forms. Here, we don’t want to get data from the database, we just want to get past the login.
Logins are connected to a database of users, and often the code doesn’t care about the content of the username and password fields, but if both make a matching pair in the users table. The web application asks the database “There is a user with THIS username and their password is THAT?”, and the database answers with just a YES or NO (true/false).
We don’t need to enumerate the database for a valid pair of username and password, we just need to make the database reply with a yes or true.
Blind SQLi – Boolean Based
Boolean based
Boolean-based SQL Injection refers to the response received from our injection attempts, which could be either a binary answer (yes/no, 0/1, true/false, etc.). That confirms to us that our SQL Injection payload was either successful or not.
While we get only a binary answer, we can enumerate a whole database structure and content with this type.
Blind SQLi – Time based
A time-based blind SQL Injection is similar to the boolean based as they don’t have a visual confirmation if we are right or wrong.
Instead, the indicator of a correct query is based on the time a query takes time to complete. This delay is included with built-in methods such as SLEEP(x). This method will only be executed if there is a successful UNION SELECT statement.
For example, if we want to know how many columns there are in a table, we can use:
admin123' UNION SELECT SLEEP(5);--
If there is no pause, try the next:
admin123' UNION SELECT 1,SLEEP(5);--
Rinse and repeat, until you have a 5-second delay in your answer.
Out-of-Band SQLi
Out-of-Band SQL Injection is not as common as the other two, as it depends of specific features being enabled on the database server side, which makes external network calls based on the results from an SQL query.
It is classified by having two different communications channels: One to launch the attack, and other to gather the results.
For example, the attack channel could be a web request and the data gathering channel could be monitoring HTTP/DNS requests made to a service you control.
In this example, an attacker makes a request to a website vulnerable to SQL Injection with an injection payload (1). The website then makes a SQL query to the database which passes the hacker’s payload (2). The payload contains a request which forces an HTTP request back to the hacker’s machine containing data from the database.
Remediation
Developers have ways to protect their web applications from them:
Prepared statements (with parameterized queries)
In a prepared query the developer writes the SQL query using parameters instead of the user input. Writing prepared statements ensures that the SQL code structure is tight and doesn’t change. Also, makes the code looks cleaner and easier to read.
An example in PHP:
$servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname);
Input validation
Input validation can save a lot of headaches. Employing an allow list can restrict input to only certain strings, or a string replacement method can filter out the characters you wish to allow o disallow.
Escaping user input
Allowing user input containing characters such as ‘, “, $ or \ can cause SQL queries to break, or even letting injection attacks as we just saw.
Summary
Time for a recap. We have learned about:
- What is SQL
- What is SQL injection
- Databases: What they are, how they work, their structure and different types
- What is In-Band SQLi, Blind SQLi and Out-of-Band SQLi
- How to fix our code against SQLi
Stats
From 150.868th to 145.744th. Still in the top 8% in TryHackMe!
Here is also the Skill Matrix:
