Last modified: May 11, 2025

This article is written in: 🇺🇸

SQL Injection Attacks

SQL Injection Attacks are a security concern in web applications. We'll explore how these attacks occur, examine concrete examples, and discuss effective prevention strategies. By the end of this journey, you'll have a solid understanding of SQL Injection and how to protect your applications from such vulnerabilities.

After reading the material, you should be able to answer the following questions:

What is SQL Injection?

SQL Injection is a technique where attackers exploit vulnerabilities in an application's interaction with its database. By inserting malicious SQL code into input fields, they can manipulate queries to access unauthorized data, modify or delete records, and even take control of the entire database. Think of it as someone sneaking harmful instructions into a conversation between your application and its database.

How Does SQL Injection Happen?

At the core, SQL Injection occurs when user input is incorporated directly into SQL queries without proper validation or sanitization. This unfiltered input can alter the structure of the SQL commands, leading to unintended and potentially dangerous outcomes.

I. User Input Submission

Users provide input through forms, URL parameters, or other data entry points.

II. Query Construction

The application builds SQL queries by combining static code with user input.

III. Query Execution

The database executes the constructed query, which may have been tampered with if the input was malicious.

[ User Input ] --> [ Application ] --> [ Query Construction ] --> [ Database Execution ]

Vulnerable Code Example

Imagine a login form where users enter their username and password. A vulnerable application might handle this input as follows:

<?php
// User-provided input
$username = $_POST['username'];
$password = $_POST['password'];

// Vulnerable SQL query construction
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

// Execute the query
$result = mysqli_query($connection, $query);

// Check if user exists
if (mysqli_num_rows($result) > 0) {
    echo "Welcome, $username!";
} else {
    echo "Invalid username or password.";
}
?>

In this example, user inputs $username and $password are directly embedded into the SQL query without any checks. This opens the door for SQL Injection.

Examples of SQL Injection Attacks

Let's explore how attackers can exploit such vulnerabilities with real-world scenarios.

Authentication Bypass

An attacker aims to gain unauthorized access by bypassing the login authentication.

The attacker inputs the following:

SELECT * FROM users WHERE username = 'admin' --' AND password = 'irrelevant'

SELECT * FROM users WHERE username = 'admin'

[ Malicious Input ]
        |
        v
[ Altered Query ]
        |
        v
[ Unauthorized Access ]

Data Extraction

An attacker tries to retrieve sensitive information from the database.

The attacker inputs:

SELECT * FROM users WHERE username = 'john' UNION SELECT username, password FROM users --' AND password = 'irrelevant'

Data Manipulation

An attacker wants to modify data, such as elevating their privileges.

The attacker inputs:

SELECT * FROM users WHERE username = ''; UPDATE users SET role='admin' WHERE username='attacker'; --' AND password = 'irrelevant'

Denial of Service

An attacker aims to disrupt the database's functionality.

The attacker inputs:

SELECT * FROM users WHERE username = ''; DROP TABLE users; --' AND password = 'irrelevant'

Preventing SQL Injection Attacks

Understanding prevention is crucial to safeguard applications from SQL Injection.

Use Parameterized Queries (Prepared Statements)

Parameterized queries ensure that user input is treated strictly as data, not executable code.

Secure Code Example in PHP using PDO:

<?php
// User-provided input
$username = $_POST['username'];
$password = $_POST['password'];

// Prepare the SQL statement
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');

// Bind parameters
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);

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

// Check if user exists
if ($stmt->rowCount() > 0) {
    echo "Welcome, $username!";
} else {
    echo "Invalid username or password.";
}
?>

[ User Input ] --> [ Application ] --> [ Parameterized Query ] --> [ Safe Execution ]

Validate and Sanitize User Input

Always check that inputs meet expected criteria before using them.

<?php
// Validate username (e.g., only letters and numbers)
if (!preg_match('/^[a-zA-Z0-9]+$/', $_POST['username'])) {
    die('Invalid username.');
}

// Sanitize inputs
$username = htmlspecialchars($_POST['username']);
$password = htmlspecialchars($_POST['password']);
?>

Use Stored Procedures

Stored procedures are precompiled SQL statements stored in the database, which can be executed with parameters.

DELIMITER //
CREATE PROCEDURE AuthenticateUser(IN p_username VARCHAR(50), IN p_password VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE username = p_username AND password = p_password;
END //
DELIMITER ;

Calling the Stored Procedure in PHP:

<?php
// User-provided input
$username = $_POST['username'];
$password = $_POST['password'];

// Prepare and execute the stored procedure
$stmt = $pdo->prepare('CALL AuthenticateUser(:username, :password)');
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

// Check if user exists
if ($stmt->rowCount() > 0) {
    echo "Welcome, $username!";
} else {
    echo "Invalid username or password.";
}
?>

Implement Least Privilege Principle

Limit the database permissions of the application's user account.

Example of Restricting Privileges in MySQL:

GRANT SELECT, INSERT, UPDATE ON mydatabase.users TO 'app_user'@'localhost' IDENTIFIED BY 'securepassword';

Escape User Input

If parameterized queries aren't available, ensure special characters are properly escaped.

<?php
// Escape special characters
$username = mysqli_real_escape_string($connection, $_POST['username']);
$password = mysqli_real_escape_string($connection, $_POST['password']);

// Construct the query
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
?>

Table of Contents

    SQL Injection Attacks
    1. What is SQL Injection?
    2. How Does SQL Injection Happen?
    3. Vulnerable Code Example
    4. Examples of SQL Injection Attacks
      1. Authentication Bypass
      2. Data Extraction
      3. Data Manipulation
      4. Denial of Service
    5. Preventing SQL Injection Attacks
      1. Use Parameterized Queries (Prepared Statements)
      2. Validate and Sanitize User Input
      3. Use Stored Procedures
      4. Implement Least Privilege Principle
      5. Escape User Input