SECURITY: SQL Injection(SQLi)

SQL (Structured Query Language) Injection, mostly referred to as SQLi, is an attack on a web application database server that causes malicious queries to be executed on the database. SQL injection is a broad term that encompasses various types of attacks where an attacker injects malicious SQL code into an application’s input fields to manipulate the database.

Keep Reading
- Simple Definition
- Types
- Examples

WHAT IS SQL INJECTION(SQLi)
SQL Injection (SQLi) is a type of Cyber attack where malicious actors insert or manipulate SQL (Structured Query Language) code within user inputs or data entry fields of a web application. This can lead to unauthorized access to a database, data theft, data manipulation, or even full control over a vulnerable application’s database.

When a web app uses invalidated user input(when the input is not validated by the web application) to communicate with a database, it opens the door for attackers to steal, modify, or delete private customer data. It can also lead to attacks on the app’s authentication methods for private customer areas. This makes SQL injection (SQLi) not only one of the oldest web app vulnerabilities but also one of the most destructive.

TYPES
There are 3 types in total of SQLi
1. In-Band
2. Blind (Boolean based, Time based)
3. Out Of Band

1. In-Band SQLi:
   In-band SQL injection (SQLi) is a type of Cyber attack where an attacker uses the same communication channel (or "in-band") as the application to both inject malicious SQL code into a database query and retrieve the results. This usually involves manipulating user inputs in a way that allows unauthorized access to or manipulation of a database.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.

There are two common types of in-band SQL injection:

1. Error-Based SQL Injection: In this type of attack, an attacker intentionally injects SQL code that will cause the application to generate error messages containing information about the database structure or content. These error messages can provide valuable information to the attacker, such as table names, column names, and even data from the database.
2. Union-Based SQL Injection: In union-based SQL injection, an attacker uses the SQL UNION operator to combine the results of their injected query with the legitimate query executed by the application. By observing the output and manipulating the number and types of columns returned, the attacker can extract data from the database.

Example:
Def: It 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.

Example: Imagine a website that displays user profiles, and the URL structure for viewing these profiles is like this:

https://example.com/profile?user_id=123

In this URL:

https://example.com is the website domain.

/profile is the page or endpoint for viewing user profiles.

user_id=123 is a query parameter that specifies the user whose profile to display. In this case, it's set to user ID 123.

Now, let’s say an attacker wants to view the profile of a different user, user ID 456. Instead of navigating through the website’s user interface, the attacker decides to manipulate the URL. They change the user_id parameter to 456:

https://example.com/profile?user_id=456

If the web application doesn’t properly validate or secure this input, it might directly use the user_id value from the URL to construct an SQL query like this (in a simplified form):

SELECT * FROM user_profiles WHERE user_id = 456;

Without proper input validation and security measures, this URL manipulation can result in the web application querying the database for the profile of user ID 456, even if the attacker is not authorized to access it. This is a simple example of how a minor change in the URL can lead to unintended access to sensitive database information, which is a common form of SQL injection. In reality, SQL injection attacks can be more complex and damaging.

2. 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. It might surprise you that all we need is that little bit of feedback to successful enumerate a whole database.

Authentication Bypass: One of the most straightforward Blind SQL Injection techniques is when bypassing authentication methods such as login forms. In this instance, we aren’t that interested in retrieving data from the database; We just want to get past the login.

Login forms that are connected to a database of users are often developed in such a way that the web application isn’t interested in the content of the username and password but more whether the two make a matching pair in the users table. In basic terms, the web application is asking the database “do you have a user with the username bob and the password bob123?”, and the database replies with either yes or no (true/false) and, depending on that answer, dictates whether the web application lets you proceed or not.

Taking the above information into account, it’s unnecessary to enumerate a valid username/password pair. We just need to create a database query that replies with a yes/true.

Practical:

Level Two of the SQL Injection examples shows this exact example. We can see in the box labelled “SQL Query” that the query to the database is the following:

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

N.B The %username% and %password% values are taken from the login form fields, the initial values in the SQL Query box will be blank as these fields are currently empty.

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;

Because 1=1 is a true statement and we’ve used an OR operator, this will always cause the query to return as true, which satisfies the web applications logic that the database found a valid username/password combination and that access should be allowed.

3. Blind SQLi — Boolean Based
A time-based blind SQL Injection is very similar to the above Boolean based, in that the same requests are sent, but there is no visual indicator of your queries being wrong or right this time. Instead, your 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(x) alongside the UNION statement. The SLEEP() method will only ever get executed upon a successful UNION SELECT statement.

URL=admin123' UNION SELECT SLEEP(5);--

4. How can protect their web applications from SQL injections?

Developers do have a way to protect their web applications from SQL injections by following the below methods:-

a. Prepared Statements:
> Use a database Library that supports prepared statements. Ex: PDO in PHP, Prepared Statement in Java.

> Instead of directly embedding user inputs into SQL queries to get database from the database, create a prepared statement.

> The Prepared statement separates the SQL query from the user input and treats the user input as parameters. This ensures that SQL code is never mixed with the SQL code.

> Bind the user input values to the parameters in the prepared query.This step associates the parameter placeholders in the query with the actual values provided by the user.

> Execute the prepared statement to run the SQL query with the bound parameters. Example code below:

<?php
// Assume you have already established a database connection using PDO.
// We take the username from the user and add it to the predefined SQL query.

// User input (e.g., from a web form)
$userInput = $_POST['username'];

// Define the SQL query with a placeholder for the user input
$sql = "SELECT * FROM users WHERE username = :username";

// Create a prepared statement
$stmt = $pdo->prepare($sql);

// Bind the user input to the parameter in the prepared statement
$stmt->bindParam(':username', $userInput);

// Execute the prepared statement
$stmt->execute();

// Fetch the results
$result = $stmt->fetch();

// Use the retrieved data (e.g., display it on a web page)
if ($result) {
    echo "User ID: " . $result['user_id'] . "<br>";
    echo "Username: " . $result['username'] . "<br>";
    // ... (output other user data as needed)
} else {
    echo "User not found.";
}
?>

b. Input Validation:
Input validation is the process of checking user input to ensure it meets specific criteria or constraints before it’s processed. This can involve verifying that the input conforms to expected data types, lengths, and formats.

Example: Let’s say you have a web form where users can search for products by name. To prevent SQL injection, you can validate the user’s input to ensure it contains only letters, numbers, and spaces. Here’s a simple Python example using regular expressions for input validation:

import re

user_input = "Product123"
if re.match("^[A-Za-z0-9 ]+$", user_input):
    # User input is valid, proceed with the search query
else:
    # Reject the input or provide an error message

c. Escaping User Input:
Escaping user input involves encoding or escaping user-provided data before including it in SQL queries. This prevents the data from being treated as SQL code.

Example: Suppose you have a PHP application that takes user input to search for products by name. To protect against SQL injection, you can use parameterized queries and parameter binding to ensure user input is treated as data, not code. Here’s a simple PHP example:

$user_input = "Product123";
$conn = new mysqli("localhost", "username", "password", "database");

// Prepare a parameterized SQL query
$stmt = $conn->prepare("SELECT * FROM products WHERE name = ?");

// Bind the user input as a parameter and execute the query
$stmt->bind_param("s", $user_input);
$stmt->execute();

// Fetch results
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Process the results
}

// Close the statement and the database connection
$stmt->close();
$conn->close();

In this PHP example, the user input is properly escaped and treated as a parameter in a prepared statement. The database system ensures that the input is interpreted as data and not SQL code. This approach is much safer than directly concatenating user input into SQL queries, which can lead to SQL injection vulnerabilities.

Thanks for reading my article. If you learned something then you can also check out my other articles. Keep learning