Prevent SQL Injection Vulnerabilities in PHP Applications and Fix Them

SQL injection (SQLi) refers to an injection attack wherein an attacker can execute arbitrary SQL statements by tricking a web application in processing an attacker’s input as part of a SQL statement. This post will focus on how to prevent SQL injection vulnerabilities within PHP applications and fix them.

This post assumes you have a basic understanding of SQL injection and the different variations of SQL injection.

The Problem

Before we delve into how to best tackle SQL injection in PHP, we need to understand what an application vulnerable to SQL injection looks like. In this example, we’ll be using a very simple application which accepts an ID inside of a GET parameter (this can very well be a POST request or any other HTTP method) and prints the name of a user on the screen.

Note — This example shall be using MySQL, however, the same principles apply for other databases.

Our simple application will have a database with the following table called ‘users’.

Warning — The Following Code Block Contains SQL Injection Vulnerabilities

<?php

/* 
 * Check if the 'id' GET variable is set
 * Example - http://localhost/?id=1
 */
if (isset($_GET['id'])){
  $id = $_GET['id'];

  /* Setup the connection to the database */
  $mysqli = new mysqli('localhost', 'dbuser', 'dbpasswd', 'sql_injection_example');

  /* Check connection before executing the SQL query */
  if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
  }

  /* SQL query vulnerable to SQL injection */
  $sql = "SELECT username 
      FROM users
      WHERE id = $id"; 

  /* Select queries return a result */
  if ($result = $mysqli->query($sql)) {
    while($obj = $result->fetch_object()){
      print($obj->username);
    }
  }
  /* If the database returns an error, print it to screen */
  elseif($mysqli->error){
    print($mysqli->error);
  }
}

The following is an example of a legitimate HTTP request that could be made to the vulnerable application above.

http://localhost/?id=1
> johnsmith

The following is an example of a malicious HTTP request that could be made to the vulnerable application above.

http://localhost/?id=-1 UNION SELECT password FROM users where id=1
> $2a$10$SakFH.Eatq3QnknC1j1uo.rjM4KIYn.o8gPb6Y2YBnNNNY.61mR9K

This application has a few issues, let’s go through each problem and see how it contributes to SQL injection.

A quick scan with the ‘SQL Injection’ Scan Type in Acunetix confirms the vulnerability.

The Fix

Parameterized Queries

Fixing a SQL injection vulnerability is actually quite straightforward if you follow the advice outlined in our ‘Defence in Depth’ series — “Parameterize SQL queries.”

Parameterized queries are simple to write and understand. They force you to define the SQL query beforehand and use placeholders for the user-provided variables within the query. You can then pass in each parameter to the query after the SQL statement is defined, allowing the database to be able to distinguish between the SQL command and data inputted by a user. If SQL commands are inputted by an attacker, the parameterized query would treat these as untrusted input, and the injected SQL commands will never get to execute.

Note — Avoid sanitizing user input by escaping or removing special characters. An attacker can leverage encoding tricks to bypass such protections. Stick to using parameterized queries in order to avoid SQL injection vulnerabilities.

Using PHP Data Objects (PDO)

Many PHP developers likely learned to access databases by using PHP’s MySQL or MySQLi extensions. While it is possible to write parameterized queries with PHP’s MySQLi extension, PHP 5.1 introduced a better way to work with databases — PHP Data Objects (PDO). PDO not only provides methods that make parameterized queries easy to use, but also makes code more portable (PDO works with several databases, not just MySQL) and is easier to read.

The example below shows the same application as above, but instead of being vulnerable to SQL injection and MySQLi without making use of parameterized SQL queries, this example uses PDO with parameterized statements to fix the SQL injection vulnerability in the example above.

<?php

/**
 * Check if the 'id' GET variable is set
 * Example - http://localhost/?id=1
 */
if (isset($_GET['id'])){
  $id = $_GET['id'];
  /**
   * Validate data before it enters the database. In this case, we need to check that
   * the value of the 'id' GET parameter is numeric
   */
   if ( is_numeric($id) == true){
    try{ // Check connection before executing the SQL query 
      /**
       * Setup the connection to the database This is usually called a database handle (dbh)
       */
      $dbh = new PDO('mysql:host=localhost;dbname=sql_injection_example', 'dbuser', 'dbpasswd');

      /**
       * We are going to use PDO::ERRMODE_EXCEPTION, to capture errors and write them to
       * a log file for later inspection instead of printing them to the screen.
       */
      $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      /**
       * Before executing our SQL statement, we need to prepare it by 'binding' parameters.
       * We will bind our validated user input (in this case, it's the value of $id) to our
       * SQL statement before sending it to the database server.
       *
       * This fixes the SQL injection vulnerability.
       */
      $q = "SELECT username 
          FROM users
          WHERE id = :id";
      // Prepare the SQL query
      $sth = $dbh->prepare($q);
      // Bind parameters to statement variables
      $sth->bindParam(':id', $id);
      // Execute statement
      $sth->execute();
      // Set fetch mode to FETCH_ASSOC to return an array indexed by column name
      $sth->setFetchMode(PDO::FETCH_ASSOC);
      // Fetch result
      $result = $sth->fetchColumn();
      /**
       * HTML encode our result using htmlentities() to prevent stored XSS and print the
       * result to the page
       */
      print( htmlentities($result) );

      //Close the connection to the database
      $dbh = null;
    }
    catch(PDOException $e){
      /**
       * You can log PDO exceptions to PHP's system logger, using the Operating System's
       * system logging mechanism
       *
       * For more logging options visit http://php.net/manual/en/function.error-log.php
       */
      error_log('PDOException - ' . $e->getMessage(), 0);
      /**
       * Stop executing, return an 'Internal Server Error' HTTP status code (500),
       * and display an error
       */
      http_response_code(500);
      die('Error establishing connection with database');
    }
   } else{
    /**
     * If the value of the 'id' GET parameter is not numeric, stop executing, return
     * a 'Bad request' HTTP status code (400), and display an error
     */
    http_response_code(400);
    die('Error processing bad or malformed request');
   }
}

By properly parameterizing SQL queries, any user input that is passed to the database is treated as data and can never be confused as being part of a command.

A scan with the ‘SQL Injection’ Scan Type in Acunetix confirms that the parameterized code is not vulnerable to SQL injection.

Wrap-Up

Parameterizing queries solves SQL injection vulnerabilities. While this example uses PDO to fix the vulnerability, you can still use MySQLi PHP functions to prevent SQL injection. However, since PDO is more portable across databases and supports the use of named parameters (we used :id as a named parameter in our example), it generally makes it easier to use than the MySQLi.