How to prevent SQL injection in PHP?

To prevent SQL injection in PHP, follow these best practices:

1. Use Prepared Statements with Parameterized Queries

This is the most secure and recommended method. Prepared statements separate SQL logic from data, ensuring user input is treated as data (not executable code).

Using PDO (PHP Data Objects):
- php

// Create a PDO connection
$pdo = new PDO("mysql:host=localhost;dbname=mydb", "username", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare a statement with placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND status = :status");

// Bind parameters and execute
$stmt->execute([
    ':email' => $_POST['email'],
    ':status' => 'active'
]);

// Fetch results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Using MySQLi:
- php

// Create a MySQLi connection
$mysqli = new mysqli("localhost", "username", "password", "mydb");

// Prepare a statement
$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ? AND status = ?");

// Bind parameters ("ss" for two strings) and execute
$stmt->bind_param("ss", $_POST['email'], 'active');
$stmt->execute();

// Get results
$result = $stmt->get_result();
$results = $result->fetch_all(MYSQLI_ASSOC);

2. Avoid Dynamic SQL Queries

Never directly embed user input into SQL strings:

php

// ❌ Dangerous (prone to SQL injection)
$sql = "SELECT * FROM users WHERE id = " . $_GET['id'];

3. Sanitize Inputs (If Prepared Statements Aren’t Possible)

While not recommended as a primary defense, use escaping functions like mysqli_real_escape_string() for legacy code:

php

$email = mysqli_real_escape_string($mysqli, $_POST['email']);
$sql = "SELECT * FROM users WHERE email = '$email'";

4. Validate and Sanitize User Input

Restrict input formats (e.g., emails, numbers) using filters:

php

// Validate email format
if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
    die("Invalid email");
}

// Sanitize integers
$id = filter_var($_POST['id'], FILTER_SANITIZE_NUMBER_INT);

5. Use Allowlists for Input

For fields like sort order or table names, use predefined values:

php

$allowed_orders = ["name", "email", "created_at"];
$order = in_array($_GET['order'], $allowed_orders) ? $_GET['order'] : 'name';
$sql = "SELECT * FROM users ORDER BY $order";

6. Limit Database Permissions

Run database queries with a user account that has the minimum required privileges (e.g., avoid root).

7. Avoid Deprecated Extensions

Never use outdated extensions like mysql_* (e.g., mysql_query()). Use PDO or MySQLi instead.

Key Takeaways:

Always prioritize prepared statements (PDO/MySQLi).
Never trust user input: validate, sanitize, and parameterize.
Use tools like PHP’s filter_var() for input validation.
For modern projects, consider ORM libraries (e.g., Eloquent in Laravel) that abstract SQL and auto-handle parameterization.

By following these practices, you can effectively mitigate SQL injection risks in PHP.