Understanding SQL Injection

SQL injection occurs when user input is not properly sanitized or validated before being used in a database query. Attackers can exploit this by inserting malicious SQL code into input fields such as login forms, search bars, or URL parameters.

For example, consider a PHP script that authenticates users with the following vulnerable code:

$username = $_GET['username'];
$password = $_GET['password'];

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($connection, $query);

If an attacker inputs ' OR 1=1 -- as the username and any value as the password, the resulting SQL query becomes:

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

This can allow the attacker to bypass authentication entirely.

Best Practices to Prevent SQL Injection

1. Use Prepared Statements with PDO or MySQLi

The most effective way to prevent SQL injection is to use prepared statements, which separate SQL logic from data.

Using PDO (PHP Data Objects):

$pdo = new PDO("mysql:host=localhost;dbname=testdb", "user", "password");

$username = $_GET['username'];
$password = $_GET['password'];

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

$user = $stmt->fetch(PDO::FETCH_ASSOC);

Using MySQLi with Prepared Statements:

$mysqli = new mysqli("localhost", "user", "password", "testdb");

$username = $_GET['username'];
$password = $_GET['password'];

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

$result = $stmt->get_result();
$user = $result->fetch_assoc();

Both examples bind user input to the SQL query safely, preventing malicious SQL from being executed.

2. Validate and Sanitize Input

Always validate and sanitize user input before using it in SQL queries. Input validation ensures the data is of the expected type and format.

$username = filter_input(INPUT_GET, 'username', FILTER_SANITIZE_STRING);

if (empty($username)) {
    die("Invalid username.");
}

For numeric input:

$userId = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);

if ($userId === false) {
    die("Invalid user ID.");
}

3. Avoid Dynamic SQL Construction

Never build SQL queries by concatenating strings. This is a common source of injection vulnerabilities.

Bad Practice:

$query = "SELECT * FROM users WHERE id = " . $_GET['id'];

Good Practice:

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);

Comparison of SQL Security Techniques

TechniqueDescriptionSecurity LevelNotes
mysql_real_escape_string()Escapes special characters in stringsLowDeprecated; not secure against all attacks
mysqli::real_escape_string()Similar to mysql_real_escape_string()LowStill vulnerable if not used with prepared statements
Prepared statementsSeparate SQL logic from dataHighRecommended for all queries
ORM (e.g., Eloquent)Abstracts SQL and uses prepared statements under the hoodHighReduces direct SQL manipulation
Input validationEnsures data conforms to expected formatMediumShould be combined with other methods

Implementing a Secure Login System

Here's a secure example of a login system using prepared statements and input validation:

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
    $password = filter_input(INPUT_POST, 'password', FILTER_SANITIZE_STRING);

    if (empty($username) || empty($password)) {
        die("Username and password are required.");
    }

    $pdo = new PDO("mysql:host=localhost;dbname=testdb", "user", "password");

    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
    $stmt->bindParam(':username', $username);
    $stmt->execute();

    $user = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($user && password_verify($password, $user['password'])) {
        echo "Login successful.";
    } else {
        echo "Invalid credentials.";
    }
}

This example uses both input validation and prepared statements to protect against SQL injection.

Learn more with useful resources