Description

SQL Injection (SQLi) is a common security vulnerability that allows attackers to manipulate SQL queries executed by a web application. This can lead to unauthorized access to the database, data theft, or data manipulation. From a developer’s perspective, understanding how SQLi works and how to prevent it is crucial.
How SQL Injection Works

SQL Injection occurs when user input is not properly sanitized and is directly used in SQL queries. This allows an attacker to inject malicious SQL code into the query, which can change its intended behavior.
Example of SQL Injection Vulnerability

Consider the following PHP code snippet, which is vulnerable to SQL injection:

<?php
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
    echo "Login successful!";
} else {
    echo "Invalid username or password.";
}
?>

In this code, the user input ($username and $password) is directly used in the SQL query. An attacker can manipulate this input to alter the query. For example, if the attacker enters the following as the username:

' OR 1=1 --

The resulting query becomes:

SELECT * FROM users WHERE username = '' OR 1=1 --' AND password = ''

The -- denotes a comment in SQL, so everything after it is ignored. This effectively changes the query to:

SELECT * FROM users WHERE username = '' OR 1=1

Since 1=1 is always true, the query will return all rows in the users table, potentially allowing the attacker to log in as any user.

Types Of SQLI

SQL Injection (SQLi) comes in various forms, each exploiting different aspects of SQL queries and database interactions. Here are the main types of SQLi:

1. Classic SQL Injection
   Classic SQL Injection, also known as in-band SQL Injection, involves using the same communication channel for both the attack and the data retrieval. This is the most common type of SQLi.

   SELECT * FROM users WHERE username = 'user' OR '1'='1' --' AND password = 'pass';
   

2. Error-based SQL Injection
   Error-based SQL Injection leverages error messages returned by the database to gather information about the database structure.

   SELECT * FROM users WHERE id = 1 AND 1=CONVERT(int, (SELECT @@version));
   

3. Union-based SQL Injection
   Union-based SQL Injection uses the UNION SQL operator to combine the results of two or more SELECT queries into a single result, allowing an attacker to retrieve data from different tables.

   SELECT id, username, password FROM users WHERE id = 1 UNION ALL SELECT NULL, version(), NULL;
   

4. Blind SQL Injection
   Blind SQL Injection occurs when the database does not return error messages or data, making it difficult to exploit. Attackers infer information by observing the behavior of the application.
   a. Boolean-based Blind SQL Injection
   This technique involves sending SQL queries that result in true or false conditions and observing the application’s behavior.

   SELECT * FROM users WHERE username = 'admin' AND 1=1 -- ' AND password = 'pass';
   SELECT * FROM users WHERE username = 'admin' AND 1=2 -- ' AND password = 'pass';
   

   b. Time-based Blind SQL Injection
   This technique involves sending SQL queries that cause a time delay if the condition is true, allowing the attacker to infer information based on the time it takes for the response.

   SELECT * FROM users WHERE IF(username='admin' AND SLEEP(5), 1, 0) -- ' AND password = 'pass';
   

   Preventing SQL Injection

To prevent SQL Injection, you should always use prepared statements and parameterized queries. Here’s how you can modify the above code to use prepared statements with the mysqli extension:

<?php

$username = $_POST['username'];
$password = $_POST['password'];
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
    echo "Login successful!";
} else {
    echo "Invalid username or password.";
}
$stmt->close();
?>

In this example:
the prepare method creates a prepared statement,
bind_param binds the user input to the statement.
The ss argument specifies that both parameters are strings.
Prepared statements ensure that the user input is treated as data and not as part of the SQL query
—