Introduction

SQL injection is one of the most common vulnerabilities in web applications. If not properly handled, malicious users can gain unauthorized access to or manipulate your database. 

In this guide, we’ll walk through essential techniques to help you prevent SQL injection in PHP. By following these practices, you can protect your application and your users' data from security breaches.

1. Use Prepared Statements with Parameterized Queries

One of the most effective ways to prevent SQL injection in PHP is by using prepared statements and parameterized queries. Prepared statements separate the SQL logic from the data, which ensures that user input is treated only as data, not executable code.

Here’s an example using MySQLi:

<?php // Create a connection $mysqli = new mysqli("localhost", "username", "password", "database"); // Check connection if ($mysqli->connect_error) {    die("Connection failed: " . $mysqli->connect_error); } // Prepare a statement $stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?"); $stmt->bind_param("s", $email); // 's' specifies the type (string) // Get user input $email = $_POST['email']; // Execute the query $stmt->execute(); // Get the result $result = $stmt->get_result(); // Fetch data while ($row = $result->fetch_assoc()) {    echo $row['username']; } // Close the statement and connection $stmt->close(); $mysqli->close(); ?>

In this example, the SQL query is prepared first, and the bind_param() function safely binds the user input ($email) to the query, preventing it from being treated as part of the SQL code.

2. Use PDO with Prepared Statements

The PHP Data Objects (PDO) extension is another way to securely interact with a database. PDO provides a consistent interface for multiple database types and supports prepared statements.

Here’s an example using PDO:

<?php // Set up the PDO connection $pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Prepare the SQL query $stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email"); // Bind the user input to the query $stmt->bindParam(':email', $_POST['email'], PDO::PARAM_STR); // Execute the query $stmt->execute(); // Fetch results while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {    echo $row['username']; } ?>

In this case, the :email placeholder is used, and the bindParam() function binds the user input to the prepared statement, preventing SQL injection.

3. Validate and Sanitize User Inputs

While prepared statements handle the majority of SQL injection attacks, it’s still a good practice to validate and sanitize user inputs. This ensures that the data conforms to the expected format before being processed.

• Validate inputs by checking if they meet specific criteria (e.g., a valid email format or a number).
• Sanitize inputs to remove any unnecessary characters, especially those that could be harmful (like semicolons, quotation marks, etc.).

Example of sanitizing an email input:

$email = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL);

This strips out any invalid characters from the email address and ensures it is properly formatted.

4. Avoid Using Dynamic SQL Queries

Avoid building SQL queries directly by concatenating user input into the query string. This is a direct way of introducing vulnerabilities. Instead, use prepared statements, as shown in the examples above.

Incorrect (unsafe) way to include user input in SQL:

$sql = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'"; $result = mysqli_query($connection, $sql);

This approach allows an attacker to inject SQL code via the email field, which can lead to serious security risks.

5. Use Least Privilege for Database Access

Ensure that the database user your application connects with has minimal privileges. For example, if the application only needs to read data, ensure the user account has only SELECT privileges. This limits the potential damage in case of an attack.

6. Escape User Inputs (Last Resort)

If you’re absolutely required to use dynamic SQL queries for any reason, make sure to escape the input using functions like mysqli_real_escape_string() or PDO::quote() to mitigate risks. However, this is not a recommended solution as it’s prone to errors and is less secure than using prepared statements.

Example of escaping input:

$email = mysqli_real_escape_string($connection, $_POST['email']);

However, always prefer prepared statements over manual escaping whenever possible.

Conclusion

SQL injection is a serious security vulnerability that can have devastating consequences for your application. By using prepared statements, parameterized queries, and properly validating and sanitizing user inputs, you can significantly reduce the risk of SQL injection attacks in your PHP code. 

Following these best practices will not only protect your database but also improve the overall security and integrity of your web application.