SQL injection in a web app - example

SQL Injection (SQLi) is a common attack vector in which an attacker inserts malicious SQL code into a query, potentially allowing unauthorized access to a database. Here’s a detailed example to illustrate how SQL Injection works and how to prevent it.

Example Scenario

Imagine a simple web application with a login form where users enter their username and password. The backend code constructs an SQL query to check the credentials against a database.

Vulnerable Code

Here is an example of a vulnerable PHP script:

<?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.";
}
?>

SQL Injection Attack

If an attacker inputs the following as the username:

' OR '1'='1

and anything as the password (or even leaves it blank), the resulting SQL query becomes:

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

The OR '1'='1' condition always evaluates to true, so the query returns all rows in the users table, potentially bypassing authentication checks.

Consequences

• Unauthorized access to user accounts.
• Possible data leakage, modification, or deletion.
• Compromise of entire database security.

Preventing SQL Injection

1. Using Prepared Statements and Parameterized Queries

Prepared statements ensure that user input is treated as data and not executable code.

Example in PHP using MySQLi:

<?php
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);

$username = $_POST['username'];
$password = $_POST['password'];
$stmt->execute();

$result = $stmt->get_result();

if ($result->num_rows > 0) {
    echo "Login successful!";
} else {
    echo "Invalid username or password.";
}
?>

Example in PHP using PDO:

<?php
$stmt = $conn->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);

$username = $_POST['username'];
$password = $_POST['password'];
$stmt->execute();

if ($stmt->rowCount() > 0) {
    echo "Login successful!";
} else {
    echo "Invalid username or password.";
}
?>

2. Input Validation and Sanitization

Ensure that user input is validated and sanitized before using it in SQL queries.

Example:

<?php
$username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
$password = filter_input(INPUT_POST, 'password', FILTER_SANITIZE_STRING);
?>

3. Least Privilege Principle

Ensure that the database user account used by the web application has the minimum privileges necessary to perform its tasks. Avoid using administrative or highly privileged accounts.

4. Use ORM Libraries

Object-Relational Mapping (ORM) libraries abstract the database queries and use parameterized queries by default, reducing the risk of SQL Injection.

Example using PHP’s Eloquent ORM:

$user = User::where('username', $_POST['username'])
             ->where('password', $_POST['password'])
             ->first();

if ($user) {
    echo "Login successful!";
} else {
    echo "Invalid username or password.";
}