Preventing SQL Injection in PHP

SQL injection is a serious security vulnerability that occurs when untrusted data is inserted into a SQL query, allowing attackers to manipulate the query and potentially gain unauthorized access to the database.

Prepared Statements

Use prepared statements with parameterized queries when executing SQL queries in PHP. Prepared statements separate the SQL query from the user input, preventing attackers from altering the query's structure.

Example:

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

// Set user input
$username = $_POST['username'];
$password = $_POST['password'];

// Execute the query
$stmt->execute();

Parameterized Queries

When using parameterized queries, always bind the user input to placeholders in the SQL query. This ensures that the user input is treated as data and not part of the SQL query syntax.

Example:

// Using Parameterized Queries
$username = $_POST['username'];
$password = $_POST['password'];

// Sanitize user input
$username = $mysqli->real_escape_string($username);
$password = $mysqli->real_escape_string($password);

// Execute the query
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $mysqli->query($sql);

Input Validation

Validate and sanitize user input before using it in SQL queries. Ensure that the input adheres to the expected format and discard or sanitize any unwanted characters.

Example:

// Input Validation
$username = $_POST['username'];
$password = $_POST['password'];

// Validate and sanitize input
if (preg_match("/^[a-zA-Z0-9]+$/", $username) && strlen($password) >= 8) {
    // Proceed with the query
    $sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
    $result = $mysqli->query($sql);
} else {
    // Handle invalid input
}

Least Privilege Principle

Create a separate database user with the least privilege required for your PHP application. This limits the potential damage an attacker can do if they manage to execute a SQL injection.

Escaping User Input

If you need to include user input in SQL queries directly, use proper escaping functions like mysqli_real_escape_string() or PDO::quote() to prevent SQL injection.

Example:

// Escaping User Input
$username = $_POST['username'];
$password = $_POST['password'];

// Escape user input
$username = $mysqli->real_escape_string($username);
$password = $mysqli->real_escape_string($password);

// Execute the query
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $mysqli->query($sql);