SQL Injection

SQL Injection is a common web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It generally occurs when user input is included in an SQL query without proper validation or escaping, allowing an attacker to manipulate the query and execute arbitrary SQL code.

Example Scenario

Suppose we have a PHP application that allows users to log in by providing a username and password. The application checks these credentials against a database. Here’s a simplified version of how this might be implemented:

<?php
$username = $_POST['username'];
$password = $_POST['password'];

// Connect to the database
$conn = new mysqli("localhost", "user", "password", "database");

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Unsafe SQL query
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // User authenticated
    echo "Welcome, " . $username;
} else {
    // Authentication failed
    echo "Invalid username or password.";
}

$conn->close();
?>

SQL Injection Attack

An attacker could exploit this code by entering a specially crafted username and password. For instance, if the attacker provides the following input:

• Username: admin' --
• Password: anything

The resulting SQL query would be:

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

The -- sequence is an SQL comment, which means that the rest of the query is ignored. The query effectively becomes:

SELECT * FROM users WHERE username = 'admin'

If there is a user with the username admin in the database, the query will return that user, and the attacker will be authenticated without needing to know the password.

Preventing SQL Injection

To prevent SQL injection, you should use prepared statements with bound parameters. Here’s the corrected version of the above PHP code using prepared statements:

<?php
$username = $_POST['username'];
$password = $_POST['password'];

// Connect to the database
$conn = new mysqli("localhost", "user", "password", "database");

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare a statement
$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) {
    // User authenticated
    echo "Welcome, " . $username;
} else {
    // Authentication failed
    echo "Invalid username or password.";
}

$stmt->close();
$conn->close();
?>

How Prepared Statements Prevent SQL Injection

Prepared statements separate the SQL logic from the data being passed to the query. Here’s what happens with a prepared statement:

1. The SQL query is defined with placeholders (?).
2. The user input is passed as parameters to the query.
3. The database engine ensures that the parameters are treated as data, not executable code.

This method effectively prevents the user input from interfering with the query structure, thus preventing SQL injection.

Conclusion

SQL injection is a serious security threat that can be mitigated by using prepared statements or other methods that properly handle user input. Always validate and sanitize user inputs, and prefer secure coding practices to protect your applications from such vulnerabilities.