How to Prevent SQL Injection in PHP

  1. Using PDO
  2. Using MySQL
  3. What is SQL Injection
  4. Related Resources

This snippet will provide you with comprehensive information about SQL injection and the ways to prevent it.

Two main options can be highlighted for achieving that goal.

Using PDO

The first option we recommend is applying PDO like this:

<?php

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute(['name' => $name]);
foreach ($stmt as $row) {
  // Do something with $row
}

?>

Using MySQL

The second option is implementing MySQLi for MySQL like this:

<?php

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
  // Do something with $row
}

?>

However, the universal method is using the PDO, as shown above. It is essential to set up the connection correctly. While using PDO for accessing a MySQL database, real prepared statements are not applied by default. To settle that, it is necessary to disable the emulation of the prepared statements.The correct way of creating a connection with PDO is demonstrated in the example below:

<?php

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

Here, the error mode is not a must but highly recommended. In this way, the script will not be prevented by a Fatal Error once something fails. With it, the developer has an opportunity to catch any errors that are thrown as PDOException.

However, the first setAttribute() line is mandatory. It is used for telling PDO to deactivate the emulated prepared statements using real prepared. That assures that the values and the statement are not parsed by PHP prior to sending it to the MySQL server.

What is SQL Injection

It is a technique in which a malicious SQL code gets passed from the frontend input field to the backend of the database-driven application. All modern applications are considered database-driven. It means that the user should log in to be able to use the functionality of the application properly. An SQL injection occurs when a user is asked for input as to their username and password. Instead of it, the user sends an SQL statement that might be unknowingly run on your database.