How to prevent SQL injections in PHP

What is SQL Injection?

SQL Injection is an attack that uses code injection through an unfiltered user input data. Its target is data-driven applications. A successful SQL injection can damage your data, put on a risk the sensitive information from your database, or even gets control over your SQL server.

A practical example of an SQL injection is a form submit with malicious data.

$email = "info@domain.com' OR 1=1";
mysql_query("SELECT * FROM users WHERE email = '$email'");

// will execute:
// SELECT * FROM users WHERE email = 'info@domain.com' OR 1=1

Avoidance Techniques

To prevent SQL injections always escape user input data using prepared statements, type casting or database-specific string escape function (e.g. mysql_real_escape_string().

• PDO prepared statements

  # using named placeholders
  $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (:email, :pswd)");
  $stmt->bindParam(':email', $email);
  $stmt->bindParam(':pswd', $pswd);
  $stmt->execute();
  
  # or
  $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (:email, :pswd)");
  $stmt->execute(array(
      ':email' => $email, 
      ':pswd' => $pswd
  ));
  
  # using positional ? placeholders
  $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (?, ?)");
  $stmt->bindParam(1, $email);
  $stmt->bindParam(2, $pswd);
  $stmt->execute();
  
  # or
  $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (?, ?)");
  $stmt->execute(array($email, $pswd));
  

• MySQLi prepared statements

  $stmt = $mysqli->stmt_init();
  if ($stmt->prepare("INSERT INTO users (email, pswd) VALUES (?, ?)")) {
      $stmt->bind_param("ss", $email, $pswd);
      $email = $_POST['email'];
      $pswd = $_POST['pswd'];
      $stmt->execute();
      $stmt->close();
  }
  

• Change data type

  settype($id, 'integer');
  $result = $mysqli->query("SELECT * FROM invoices WHERE id = $id");
  

• Type casting

  $id = (int) $id;
  $result = $mysqli->query("SELECT * FROM invoices WHERE id = $id");
  

• Data format

  $result = $mysqli->query(sprintf("SELECT * FROM invoices WHERE id = %u", $id));
  

• Escape special characters

  $id = $mysqli->real_escape_string($id);
  $result = $mysqli->query("SELECT * FROM invoices WHERE id = '$id'");
  

• Download Large Files with PHP
• HeadCouch - the CouchDB PHP Client
• Method Chaining

• SQL Injection, by PHP.net
• SQL Injection, by OWASP

If you have questions about preventing SQL injections in PHP, leave a comment below. And do not forget to share this article. Thanks so much for reading!