How can I prevent SQL injection in PHP?

Nadia S.

The Problem

SQL injection is a common security vulnerability where SQL commands (or code) are entered into user input fields to be executed by the database server. From reading, updating and deleting data to issuing commands to the operating system, attackers use SQL injection to maliciously interact with sensitive data.

The Solution

Preventing SQL injection in PHP should consist of a layered approach of using prepared statements, input validation, limiting database admin privileges, and regularly updating PHP and SQL software.

We’ll look at how you can create prepared statements that separate SQL commands from data using the PDO PHP extension. This solution is robust and reliable. First, we’ll create a database connection using PDO. Next, we use the PDO connection object to handle our SQL database query with user input.

Also, we’ll briefly look at PHP form input validation which alone isn’t reliable for preventing SQL attacks, but provides an additional layer of security.

Connect to the database using PDO

When we make a PDO database connection, it’s important to remember that prepared statement emulation combines the SQL query with values into a single string. We need to separate them so we must disable the use of prepared statement emulation.

We set the PDO::ATTR_EMULATE_PREPARES attribute to false. For example:

// credentials
$host = 'localhost';
$dbName = 'sample_db';
$myUsername = 'root';
$myPassword = 'pasword';

try {
    // connect to the database
    $pdo = new PDO("mysql:host=$host;dbname=$dbName", $myUsername, $myPassword);
    // disable emulated statements
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    // enable PDO error mode to throw exceptions
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

For debugging purposes, we also enable PDO error mode and exception throwing. To do this, we pass the PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION arguments in the setAttribute method.

Prepare and execute the SQL query

Let’s say we have a login form that uses a SQL query to determine if an entered username and password combination exists in our database.

We use our pdo connection object with the prepare method to prepare a SQL statement with :username and :password as placeholders for the user data. The SQL query is parsed by the server as a prepared statement. Next, the user-entered data is bound to the placeholders using the execute method. For example:

// get the user input from login form
$username = $_POST['username'];
$password = $_POST['password'];

// SQL query with placeholders for username and password
$sqlQuery = "SELECT * FROM users_test WHERE username = :username AND password = :password";

// create a prepared statement of the query
$stmt = $pdo->prepare($sqlQuery);

// bind the user input to the prepared statement's parameters
$stmt->execute(['username' => $username, 'password' => $password]);

PHP input validation

We can use a regular expression to check if the user’s input matches the expected input. However, malicious input could still pass these validation checks making this technique an inadequate defense when used by itself.

For example, we can validate that an entered username is between three and 10 characters long, and only contains letters, numbers, hyphens, and underscores.

We use the preg_match() function with the/^[A-Za-z0-9_-]{3,10}$. regular expression as the first argument and the user input as the second argument. If the input matches the regular expression, we assign the input to another variable for use in our PDO prepared statement as above. For example:

$userInput = $_POST['username'];
$username = '';

if (preg_match('/^[A-Za-z0-9_-]{3,10}$/', $userInput)) {
    echo 'Valid username';
    $username = $_POST['username'];
} else {
    echo 'Username can only contain letters, numbers, underscores, and hyphens. It must be between 3 and 10 characters long.';
}

We can use a regular expression generator to create a regular expression for the password, or other input fields, as well.

Conclusion

A powerful way to prevent SQL injection is to use the PDO database extension to create prepared statements with placeholders for variable binding.

Instead of the user’s input being substituted into a SQL query directory, placeholders are used to represent the input in a template-like statement. Then, user input is bound to the statement, separating it from the SQL query.

Validating user input using regular expressions can help defend against SQL injection attacks; however, it can’t be solely relied on.