Preventing SQL Injection in PHP Applications

SQL injection (SQLi) continues to be one of the most critical and prevalent threats in web security. For PHP developers, it is essential to understand how this vulnerability operates, and the steps needed to prevent it in order to build secure web applications.

What is SQL injection?

SQL injection occurs when user-supplied data is embedded directly into a SQL query without proper security measures. Malicious input can modify the SQL code, leading to unauthorized access, data theft, or manipulation of database contents.

SQL injection example

The example is provided for security professionals and developers to understand the principle of the attack, and in no way encourages illegal actions.

Here’s an example of a PHP login implementation that is vulnerable, as it directly concatenates user-provided username and password values into the SQL query:

$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($conn, $query);

The application proceeds with authentication by verifying whether the database query returns any results, assuming a non-empty response indicates valid credentials.

Important: in production applications, passwords should never be sent, stored, or compared in plain text format — only password hashes must be compared.

If an attacker knows that a default user named admin exists, they can gain access by entering ‘admin’ — as the username and any value as the password (based on application logic). This manipulates the SQL query:

SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'

The — sequence begins a comment in SQL, which means everything following it is ignored by the database. The database executes:

SELECT * FROM users WHERE username = 'admin'

As long as the admin user exists in the database, the manipulated query will always return a non-empty result set, allowing the attacker to bypass the password check completely and gain unauthorized access to the admin account.

SQL injection can also be exploited by attackers for a variety of other malicious purposes.

Why is escaping not enough?

In certain cases, legacy code relies on mysqli_real_escape_string() to sanitize user input in an attempt to prevent SQL injection:

$username = mysqli_real_escape_string($conn, $_GET['user']);
$query = "SELECT * FROM users WHERE name = '$username'";

But escaping alone cannot protect against all SQL injections, so it is not enough if used as a standalone practice.

SQL injection prevention basics

The most reliable way to protect against SQL injection in PHP is by using parameterized queries with prepared statements.

This method isolates user input from the SQL code, ensuring that any special characters or SQL keywords within the input cannot interfere with or modify the query’s structure.

Important: while both examples below use hard-coded credentials for simplicity, in a real application the database connection details should be loaded from environment variables (using getenv()) or a secure configuration source to avoid exposing sensitive information.

Using PDO (PHP Data Objects)

PDO provides an interface for accessing databases. It offers support for named placeholders and includes built-in mechanisms for error handling, enhancing both code readability and security.

Here is an example of a parameterized query that retrieves users based on a numeric status value:

// Hardcoded credentials for illustration only
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "user", "pass");
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name AND status = :status");
$stmt->bindParam(':name', $_GET['user'], PDO::PARAM_STR);
$stmt->bindParam(':status', $status, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll();

Using named placeholders enhances both the security and readability of the code, while binding parameters by data type with bindParam() helps prevent misuse and reduces the risk of data entry errors.

Using MySQLi

MySQLi is another safe method available for executing database queries in PHP. It also supports positional placeholders, making it a suitable option when PDO is not being used.

This demonstrates the PDO version of the code for MySQLi:

// Hardcoded credentials for illustration only
$mysqli = new mysqli("localhost", "user", "pass", "testdb"); 

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$stmt = $mysqli->prepare("SELECT * FROM users WHERE name = ? AND status = ?");
$user = $_GET['user'];
$status = 1;
$stmt->bind_param("si", $user, $status); // "s" for string, "i" for integer
$stmt->execute();
$result = $stmt->get_result();

In this case, the bind_param() function utilizes positional placeholders and enforces proper type handling, providing protection for both numeric and string inputs.

Additional SQLi prevention methods in PHP applications

While parameterization is essential for preventing SQL injection, incorporating additional best practices further strengthens the overall security of an application. However, none of them alone is sufficient to protect against SQLi.

Validating and sanitizing user input

Input validation should be consistently applied to confirm that it adheres to the expected data types, formats, and values.

A few general rules to follow:

• Checking the expected value types. If the parameter is expected to be an integer, it is recommended to use filter_var($input, FILTER_VALIDATE_INT) to ensure the input is a valid integer before processing it.
• To select from a closed and known set, using a list of allowed values (whitelist) to accept only those.
• Avoiding relying solely on filtering specific keywords (blacklisting) or removing special SQL characters.

Avoiding dynamic SQL

As a best practice, dynamic SQL, where queries are built by directly incorporating user input, should be avoided, as it increases the risk of SQL injection.

However, if it is absolutely necessary (such as when dealing with dynamic column names), all input must be validated or mapped to a predefined list of allowed values. For instance:

$columns = ['name', 'email', 'created_at'];
$sortColumn = in_array($_GET['sort'], $columns) ? $_GET['sort'] : 'name';
$query = "SELECT * FROM users ORDER BY $sortColumn";

Using stored procedures with parameters

Stored procedures are precompiled SQL procedures stored on the database server. They can encapsulate complex logic and, when used with parameterized input, provide a safe way to interact with the database.

Here is an example that shows how to define a simple stored procedure in MySQL that retrieves user records based on username. This code is typically executed directly in the MySQL command-line client or in a database management tool like phpMyAdmin or MySQL Workbench:

CREATE PROCEDURE GetUser(IN uname VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE name = uname;
END

Calling this from PHP using:

$stmt = $pdo->prepare("CALL GetUser(:uname)");
$stmt->bindParam(':uname', $_GET['user']);
$stmt->execute();

While stored procedures add an extra layer of protection, they can still be used insecurely. It is important not to create dynamic SQL inside stored procedures without safe parameter handling.

Careful error handling

It is crucial to never expose unhandled database errors in production. Verbose error reporting should be disabled and securely logged. This prevents attackers from gaining information about the structure of queries or database behavior:

ini_set('display_errors', 0);
error_reporting(0);

// Log errors to a secure file outside the web root
ini_set('log_errors', 1);
ini_set('error_log', '/var/log/php_errors.log');
error_log("Application error at " . $_SERVER['REQUEST_URI']);

Principle of least privilege

Although having full administrative access during development is often convenient and allows unrestricted operations without concerns about user permissions, deploying an application with these privileges in a production environment can pose serious security risks.

To minimize potential damage in the event of a successful injection attack, the application should be restricted from performing unnecessary operations—particularly those like DROP, GRANT, or access to administrative tables.

Testing applications for SQL injection vulnerabilities

Even when code adheres to all best practices, it is crucial to ensure that an application is secure in practice.

Why is DAST important?

Dynamic Application Security Testing (DAST) tools examine an application externally, mimicking the actions of a potential attacker. They simulate malicious input during runtime to attempt to exploit vulnerabilities such as SQL injection.

A quality DAST solution can:

• Automatically detect exploitable SQL injection points, including blind SQLi and time-based ones. It is important to pay attention to whether the tool covers all types of SQL injections, like Invicti (formerly Netsparker), because missing such a vulnerability can be costly for companies. If you want to test this solution for free, please contact us in a way that is convenient for you.
• Provide accurate results by safely exploiting and confirming real vulnerabilities, like Invicti does, instead of burdening developers with false positives.
• Integrate with CI/CD for automatic scanning, as well as with ticketing systems for easy vulnerability management.
• Offer practical remediation recommendations and vulnerability details to help teams resolve issues faster.

Thus, these best practices help protect PHP applications from SQLi, effectively reducing security risks.