Last modified: May 11, 2025
This article is written in: 🇺🇸
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:
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.
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 ]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.
Let's explore how attackers can exploit such vulnerabilities with real-world scenarios.
An attacker aims to gain unauthorized access by bypassing the login authentication.
The attacker inputs the following:
admin' --irrelevant
SELECT * FROM users WHERE username = 'admin' --' AND password = 'irrelevant'-- sequence comments out the rest of the SQL query.
SELECT * FROM users WHERE username = 'admin'
[ Malicious Input ]
|
v
[ Altered Query ]
|
v
[ Unauthorized Access ]An attacker tries to retrieve sensitive information from the database.
The attacker inputs:
john' UNION SELECT username, password FROM users --irrelevant
SELECT * FROM users WHERE username = 'john' UNION SELECT username, password FROM users --' AND password = 'irrelevant'UNION operator combines the results of two queries.An attacker wants to modify data, such as elevating their privileges.
The attacker inputs:
'; UPDATE users SET role='admin' WHERE username='attacker'; --irrelevant
SELECT * FROM users WHERE username = ''; UPDATE users SET role='admin' WHERE username='attacker'; --' AND password = 'irrelevant'-- comments out the rest of the original query.An attacker aims to disrupt the database's functionality.
The attacker inputs:
'; DROP TABLE users; --irrelevant
SELECT * FROM users WHERE username = ''; DROP TABLE users; --' AND password = 'irrelevant'DROP TABLE users command deletes the entire users table.Understanding prevention is crucial to safeguard applications from SQL Injection.
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 ]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']);
?>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.";
}
?>Limit the database permissions of the application's user account.
SELECT, INSERT).Example of Restricting Privileges in MySQL:
GRANT SELECT, INSERT, UPDATE ON mydatabase.users TO 'app_user'@'localhost' IDENTIFIED BY 'securepassword';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'";
?>