How to Prevent SQL Injection in PHP

This snippet will provide you with comprehensive information about <kbd class="highlighted">SQL</kbd> 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 <kbd class="highlighted">PDO</kbd> like this:

php use pdo

<?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 <kbd class="highlighted">MySQLi</kbd> for <kbd class="highlighted">MySQL</kbd> like this:

php mysql

<?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 <kbd class="highlighted">PDO</kbd>, as shown above. It is essential to set up the connection correctly. While using <kbd class="highlighted">PDO</kbd> for accessing a <kbd class="highlighted">MySQL</kbd> 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 <kbd class="highlighted">PDO</kbd> is demonstrated in the example below:

php create connection pdo

<?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 <kbd class="highlighted">Fatal Error</kbd> once something fails. With it, the developer has an opportunity to <kbd class="highlighted">catch</kbd> any errors that are thrown as <kbd class="highlighted">PDOException</kbd>.

However, the first <kbd class="highlighted">setAttribute()</kbd> line is mandatory. It is used for telling <kbd class="highlighted">PDO</kbd> 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 <kbd class="highlighted">MySQL</kbd> server.

What is SQL Injection

It is a technique in which a malicious <kbd class="highlighted">SQL</kbd> 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 <kbd class="highlighted">SQL</kbd> injection occurs when a user is asked for input as to their username and password. Instead of it, the user sends an <kbd class="highlighted">SQL</kbd> statement that might be unknowingly run on your database.