How to Prevent SQL Injection in PHP: Best Practices for Secure Web Applications

To safeguard your PHP application, follow these proven techniques to prevent SQL injection effectively.

• Step 1: Use Prepared Statements and Parameterized Queries (Best Practice)

• Step 2: Use an ORM (Object-Relational Mapping)

• Step 3: Escape User Inputs (If You Must Use Legacy Code)

• Step 4: Correctly Setting Up the Database Connection

• Step 5: Enable Error Reporting and Set the Correct Charset

• Step 6: Validate User Inputs

• Step 7: Limit Database Privileges

• Step 8: Keep Your PHP and Database Systems Updated

The Best Way to Prevent SQL Injection

The most effective way to prevent SQL injection attacks is to separate user data from SQL commands. This ensures the data is treated strictly as values, not executable code. The safest method is to use prepared statements and parameterized queries, which securely pass SQL statements to the database server separate from user inputs. This eliminates the risk of malicious SQL injection, even if the data contains harmful content. These techniques ensure secure SQL queries in PHP.

1. Use Prepared Statements and Parameterized Queries (Best Practice)

Prepared statements separate data from SQL commands, ensuring that data is always treated as data — never as executable code. This is the most secure method to prevent SQL injection in PHP.

Example using PDO:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'username', 'password');
$sql = "SELECT * FROM users WHERE email = :email";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll();
?>

Example using MySQLi (PHP 8.2+ with execute_query()):

Since PHP 8.2+ we can make use of execute_query() which prepares, binds parameters, and executes SQL statement in one method:

<?php
$result = $db->execute_query('SELECT * FROM users WHERE name = ?', [$_POST['name']]);
while ($row = $result->fetch_assoc()) {
    // Process data
}
?>

For older PHP versions (up to 8.1):

<?php
$stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Process data
}
?>

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

2. Use an ORM (Object-Relational Mapping)

PHP frameworks like Laravel, Symfony, and CodeIgniter provide ORM tools that automatically handle prepared statements for you.

Example in Laravel Eloquent:

<?php
$users = User::where('email', request('email'))->get();
?>

3. Escape User Inputs (If You Must Use Legacy Code)

For older codebases that cannot be refactored easily, escaping user inputs is a temporary workaround. However, this method is less secure than prepared statements.

Example using mysqli_real_escape_string():

<?php
$conn = new mysqli("localhost", "username", "password", "testdb");
$email = mysqli_real_escape_string($conn, $_POST['email']);
$sql = "SELECT * FROM users WHERE email = '$email'";
$result = $conn->query($sql);
?>

4. Correctly Setting Up the Database Connection

PDO Configuration

When using PDO with MySQL, real prepared statements are not enabled by default. You must disable emulated prepared statements to ensure data and queries are handled separately.

Example:

<?php
$dsn = 'mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4';
$dbConnection = new PDO($dsn, 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>

• PDO::ATTR_EMULATE_PREPARES ensures real prepared statements are used.
• PDO::ATTR_ERRMODE enables error reporting for better debugging.

Note: Older PHP versions (before 5.3.6) silently ignored the charset parameter in the DSN, so ensure your PHP version is up to date.

MySQLi Configuration

For MySQLi, follow this routine:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Enable error reporting
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // Set charset for security
?>

5. Enable Error Reporting and Set the Correct Charset

Proper error reporting and character encoding are crucial to ensuring your database is protected.

PDO Configuration:

<?php
$dsn = 'mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4';
$dbConnection = new PDO($dsn, 'user', 'password');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>

MySQLi Configuration:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4');
?>

6. Validate User Inputs

Validating user input ensures only properly formatted data reaches your database, reducing the risk of SQL injection.

Example of Input Validation:

<?php
if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
    die("Invalid email format");
}
?>

7. Limit Database Privileges

Restrict database permissions to only what's necessary. Avoid using root-level accounts for application queries.

8. Keep Your PHP and Database Systems Updated

Regularly update PHP, MySQL, and libraries to patch security vulnerabilities and stay protected from evolving threats.

Conclusion

By implementing prepared statements, using ORM tools, and validating user input, you can effectively prevent SQL injection in PHP. These practices not only enhance security but also improve the stability and performance of your web applications. For Laravel developers, Eloquent ORM offers built-in protection against SQL injection, making it a preferred choice for modern web development.