Preventing SQL Injection in PHP

Preventing SQL Injection in PHP

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PHP
  • 1 min read

The best way to prevent SQL injection in PHP is to separate the data from SQL so that data will never be interpreted as commands at the time of parsing SQL commands. Below are the two examples. The first is using the PDO, and the second one is using the MySQLi.

Example 1: Prevent SQL Injection in PHP

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

 $stmt->execute([ 'name' => $name ]);

 foreach ($stmt as $row) {
     // Do something with $row
 }

Example 2: Using the MySQLi

$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
 }

Also, please see below the correct way to set up the connection:

$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);

The above commands disable the emulation for the prepared statements.

See also:

Reference: