Prevent SQL Injection in PHP

 

SQL injection is a serious vulnerability that can compromise the security of your application and database. It occurs when attackers manipulate SQL queries through untrusted user input to gain unauthorized access, modify data, or even delete critical information. Fortunately, preventing SQL injection in PHP is straightforward if you follow best practices.

This guide explores how SQL injection works, why it's dangerous, and the best methods to prevent it with detailed examples.

 

---

 

What is SQL Injection?

SQL injection exploits a web application's vulnerability by injecting malicious SQL code into a query. For instance, consider this insecure query:

 

<?php
// Insecure SQL query
$email = $_POST['email'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE email = '$email' AND password = '$password';";
$result = mysqli_query($conn, $sql);
?>

    

 

An attacker could input something like this into the email field:

 

' OR '1'='1

    

 

The resulting SQL query would be:

 

SELECT * FROM users WHERE email = '' OR '1'='1' AND password = '';

    

 

This query always evaluates to true, granting access to the attacker without valid credentials.

 

---

 

How to Prevent SQL Injection in PHP

 

Use Prepared Statements

 

Prepared statements with placeholders ensure user input is treated as data rather than executable code. PHP provides two primary libraries for this:

 

• PDO (PHP Data Objects)
• MySQLi (MySQL Improved)

 

Using PDO

 

<?php
// Establish a secure database connection
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$username = 'root';
$password = '';

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Secure query execution
$email = $_POST['email'];
$password = $_POST['password'];

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND password = :password");
$stmt->execute([
    ':email' => $email,
    ':password' => $password, // Use proper password hashing in production
]);

$user = $stmt->fetch();
if ($user) {
    echo "Login successful!";
} else {
    echo "Invalid email or password.";
}
?>

    

 

Using MySQLi

 

<?php
// Secure MySQLi connection
$mysqli = new mysqli("localhost", "root", "", "testdb");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Secure query execution
$email = $_POST['email'];
$password = $_POST['password'];

$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ? AND password = ?");
$stmt->bind_param("ss", $email, $password); // 'ss' indicates string parameters
$stmt->execute();
$result = $stmt->get_result();

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

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

    

 

Validate and Sanitize Input

 

Always validate and sanitize user inputs to ensure they conform to expected formats.

 

Email Validation

 

<?php
$email = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);
if (!$email) {
    die("Invalid email format");
}
?>

    

Escape Special Characters

 

<?php
$email = mysqli_real_escape_string($conn, $_POST['email']);
$password = mysqli_real_escape_string($conn, $_POST['password']);
?>

    

 

Limit Database Privileges

 

Restrict the database user’s privileges to only what is necessary. For example:

• Use a user account that can only read and write, not delete or drop tables.
• Avoid using root accounts for database access in applications.

 

Use Stored Procedures

 

Stored procedures execute predefined SQL code on the database server, separating logic from user input.

 

<?php
$stmt = $pdo->prepare("CALL GetUser(:email, :password)");
$stmt->execute([
    ':email' => $email,
    ':password' => $password,
]);
?>

    

 

Regular Security Audits

 

Perform regular code reviews and penetration testing to identify and fix vulnerabilities.

 

---

 

Conclusion

 

Preventing SQL injection in PHP is crucial for building secure applications. The most effective defenses include:

• Using prepared statements with PDO or MySQLi.
• Validating and sanitizing user inputs.
• Restricting database privileges.
• Leveraging stored procedures when necessary.
• Regularly auditing your code and testing for vulnerabilities.

By adhering to these best practices, you can safeguard your PHP applications from SQL injection attacks and protect your users' data effectively.