How to prevent SQL injection in PHP

SQL injection is a web security vulnerability where attackers insert malicious SQL commands into input fields. To prevent it in PHP, use prepared statements, sanitize user input, limit privileges, and validate input. Here is how it's done.

What is SQL injection?

SQL injection is a web security vulnerability that occurs when an attacker inserts malicious SQL commands into a web form or other input field that is later executed by a database server. This can allow an attacker to gain access to sensitive data or modify, delete or insert new data into the database.

For example, suppose a login form on a website accepts a username and password and checks them against a database of user credentials. If the form does not properly sanitize user input, an attacker could input something like this in the password field:

' OR 1=1 --

This input would cause the SQL query to always return true because of the "OR 1=1" condition, and the double dash "--" would comment out the rest of the SQL query, effectively bypassing the password check and allowing the attacker to log in without a valid password.

How SQL Injection Works

The following are the steps that an attacker could take to perform an SQL injection attack:

The attacker identifies a web form or other input field that accepts user input and is vulnerable to SQL injection.
The attacker submits a malicious SQL command as part of the user input. This could include commands to retrieve, modify or delete data from the database.
If the application does not properly sanitize user input, the malicious SQL command is executed by the database server.
The attacker can then use the output from the SQL command to gain access to sensitive data or modify the database in ways that were not intended by the application.

Prevention of SQL injection in PHP

Use prepared statements

Prepared statements are a way of pre-compiling SQL statements and binding variables to them. This can prevent SQL injection attacks by ensuring that user input is not executed as part of the SQL command. Here's an example of how to use prepared statements in PHP with the mysqli library:

// prepare the SQL statement
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username=? AND password=?");

// bind parameters to the statement
$stmt->bind_param("ss", $username, $password);

// set parameters
$username = "user1";
$password = "pass1";

// execute the statement
$stmt->execute();

// get results
$result = $stmt->get_result();

In this example, the SQL statement is prepared with placeholders for the username and password values. The values are then bound to the statement using the bind_param() method, and the statement is executed with the execute() method.

Use parameterized queries

Similar to prepared statements, parameterized queries use placeholders for user input that are replaced with the actual values during execution. This prevents SQL injection attacks by separating user input from the SQL code. Here's an example of how to use parameterized queries in PHP with the PDO library:

// prepare the SQL statement
$stmt = $pdo->prepare("SELECT * FROM users WHERE username=:username AND password=:password");

// bind parameters to the statement
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);

// set parameters
$username = "user1";
$password = "pass1";

// execute the statement
$stmt->execute();

// get results
$result = $stmt->fetchAll();

In this example, the SQL statement is prepared with placeholders for the username and password values using named parameters. The parameters are then bound to the statement using the bindParam() method, and the statement is executed with the execute() method.

Sanitize user input

Before using user input in an SQL statement, it should be sanitized to remove any potentially dangerous characters or commands. This can be done using functions such as mysqli_real_escape_string() or PDO::quote(). Here's an example of how to sanitize user input in PHP with mysqli_real_escape_string():

// sanitize user input
$username = mysqli_real_escape_string($mysqli, $_POST['username']);
$password = mysqli_real_escape_string($mysqli, $_POST['password']);

// prepare the SQL statement
$sql = "SELECT * FROM users WHERE username='$username' AND password='$password'";

// execute the statement
$result = $mysqli->query($sql);

In this example, the user input for the username and password fields is sanitized using mysqli_real_escape_string() to prevent SQL injection attacks. The sanitized values are then used in the SQL statement to retrieve user data from the database.

Limit privileges

Use a database user account with limited privileges, instead of using a root-level account, to restrict the potential damage from an SQL injection attack. This can help to prevent an attacker from modifying or deleting data in the database.

For example, you can create a new database user with limited privileges, as follows:

CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON dbname.* TO 'webapp'@'localhost';

This creates a new user named "webapp" with limited privileges on the "dbname" database. The user can only perform SELECT, INSERT, UPDATE, and DELETE operations on the database.

Validate user input

Use regular expressions or other validation techniques to ensure that user input matches expected formats and types, and reject any input that is unexpected or suspicious. Here's an example of how to validate user input in PHP with regular expressions:

// validate user input
if (!preg_match("/^[a-zA-Z0-9]*$/", $_POST['username'])) {
    die("Invalid username");
}

// sanitize user input
$username = mysqli_real_escape_string($mysqli, $_POST['username']);
$password = mysqli_real_escape_string($mysqli, $_POST['password']);

// prepare the SQL statement
$sql = "SELECT * FROM users WHERE username='$username' AND password='$password'";

// execute the statement
$result = $mysqli->query($sql);

In this example, the user input for the username field is validated using a regular expression to ensure that it only contains alphanumeric characters. If the input is not valid, the script terminates with an error message. If the input is valid, it is sanitized using mysqli_real_escape_string() before being used in the SQL statement.

By following these best practices, you can help to prevent SQL injection attacks in your PHP code and improve the security of your web application.

Updated March 27, 2023