How to Prevent SQL Injection in PHP Applications

SQL injection is a fatal vulnerability in PHP applications! It exploits malicious input to directly attack your database. Want to avoid data theft or application crashes? Below we teach you how to prevent SQL injection.

1. Use prepared statements (parameterized queries)

Using prepared statements ensures user input is treated as data, not executable SQL code, which is key to defending against SQL injection.

PDO Example

<?php
// 创建数据库连接
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'username', 'password');

// 编写准备好的语句
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $userInput]);

// 获取结果
$result = $stmt->fetchAll();
?>

MySQLi Example

<?php
// 创建数据库连接
$mysqli = new mysqli("localhost", "username", "password", "testdb");

// 准备查询
$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $userInput); // "s" specifies the data type (string)

// 执行查询
$stmt->execute();
$result = $stmt->get_result();
?>

2. Validate and sanitize input

Validate: ensure input matches the expected format (e.g., email validation or numeric only).

Sanitize using filter_var() : clean the input with this or a similar function.

Example

<?php
$userEmail = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
if ($userEmail === false) {
    die("Invalid email address");
}
?>

3. Use a database ORM

Use an ORM (e.g., Laravel's Eloquent or Doctrine) to interact with the database; it automatically handles parameterized queries, making data safer and code cleaner.

Example

// 使用Eloquent查询
$user = User::where('email', $userInput)->first();

4. Escape special characters

If you are not using prepared statements, be sure to escape special characters with functions like mysqli_real_escape_string() to protect your data.

Example

<?php
$escapedInput = $mysqli->real_escape_string($userInput);
$query = "SELECT * FROM users WHERE email = '$escapedInput'";
$result = $mysqli->query($query);
?>

5. Avoid dynamic SQL queries

Avoid constructing SQL queries dynamically with untrusted input to reduce the risk of SQL injection.

<?php
// Vulnerable example
$query = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";
?>

6. Use stored procedures

Properly using stored procedures helps defend against SQL injection. Stored procedures run on the server and accept parameterized input, enhancing security.

Example

DELIMITER //
CREATE PROCEDURE GetUser(IN userEmail VARCHAR(255))
BEGIN
    SELECT * FROM users WHERE email = userEmail;
END //
DELIMITER ;

7. Restrict database privileges

Configure database users with the minimum necessary privileges. For example, a user that only reads data should have read‑only rights, and avoid granting DROP, DELETE, or ALTER unless absolutely needed.

8. Use a Web Application Firewall (WAF)

Deploy a WAF such as Cloudflare or ModSecurity to filter malicious requests and add an extra layer of protection.

9. Monitor and log attacks

Implement logging to monitor suspicious activity and analyze logs to detect and mitigate SQL injection attempts.