PHP Security 1: SQL Injections

Introduction

PHP is the world’s most popular server-side web programming language. According to W3Techs data from April 2019, 79% of websites are powered by PHP. Among those websites are Facebook, Yahoo, and Wikipedia.

Since PHP is so popular, PHP security is essential and the number of vulnerable PHP applications is large. Most PHP web applications share parts of code or scripts with other web applications. If the shared piece of code is found to be vulnerable, all the applications that are using it are also vulnerable.

Causes of Vulnerabilities

Most vulnerabilities are the result of bad coding habits or lack of PHP application security awareness among developers. The primary cause is the fact that user input is treated as trusted.

When you write code, you must apply two key procedures: validation and sanitization. If you implement both these procedures for user data, you ensure that what is processed and executed is valid and meets specified criteria. You must also ensure that the HTML output data is escaped so that no malicious code is executed in case an attacker injected it into the content. If you follow certain simple and basic procedures for every web page, you significantly minimize the possibility of being exposed to a critical security issue.

Object-Oriented Programming (OOP) plays a big role in applying PHP security procedures. Well-written reusable code can greatly increase the overall security of a system. It ensures that the same data processing procedure is always followed.

SQL Injections in PHP

SQL Injection is one of the most dangerous web application vulnerabilities. It is consistently ranked as number one by OWASP and shadowing other vulnerabilities such as Cross-site Scripting (XSS) or Cross-site Request Forgery (CSRF). If you insert user input directly into an SQL query (unvalidated/unsanitized), an attacker can manipulate the query itself. They can force it to return a different result than the expected one.

A successful SQL Injection attack can result in a data breach, which can expose usernames, passwords, email addresses, credit card information, and other sensitive data. In some cases, even a common attack can result in the compromise of an entire web server.

Insecure Code Sample

In the following source code example, the article parameter is passed to the query in an insecure way:

$articleid = $_GET['article'];
$query = "SELECT * FROM articles WHERE articleid = '$articleid'";

A malicious user can send a specially crafted value that will be included in the SQL query before the query is executed. For example:

1'+union+select+1,version(),3'

The query becomes:

$query = "SELECT * FROM articles WHERE articleid = '1'+union+select+1,version(),3''";

The attacker can use requests similar to the above code to enumerate all the tables/columns of the database and gain access to sensitive information.

The solution to this problem is to use parameterized SQL queries (prepared statements). If you use parameterized queries, you let the database know, which part is the query and which is the data (user input) because you send them in two separate requests. This eliminates the possibility of mixing user input and the SQL query.

It is recommended that you use PHP Data Objects (PDO) to execute prepared statements in your PHP scripts. PDO is included in the latest version of PHP. It was introduced in PHP 5.1 and available as a PECL extension in PHP 5.0. It is not accessible for earlier PHP versions.

Avoid using mysql and mysqli extensions in your PHP code. They are deprecated/outdated but still often used and you can rewrite the code to use PDO instead.

Secure Code Sample

In this sample PHP file, you do not pass the user_id to the query directly. Instead, you replace it with a placeholder. The backend database will know what to replace the placeholder with when you run the execute() function.

// User ID must not be empty, must be numeric and must be less than 5 characters long
if((!empty($_GET['user_id'])) && (is_numeric($_GET['user_id'])) && (mb_strlen($_GET['user_id'])<5)) {

  $servername = "localhost";
  $username = "username";
  $password = "password";
  $database = "dbname"; 

  // Establish a new connection to the SQL server using PDO
  try { 
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password); 

    // Assign user input to the $user_id variable 
    $user_id = $_GET['user_id']; 

    // Prepare the query and set a placeholder for user_id 
    $sth = $conn->prepare('SELECT user_name, user_surname FROM users WHERE user_id=?');

    // Execute the query by providing the user_id parameter in an array
    $sth->execute(array($user_id));

    // Fetch all matching rows
    $user = $sth->fetch();

    // If there is a matching user, display their info
    if(!empty($user)) {
      echo "Welcome ".$user['user_name']." ".$user['user_surname']; 
    } else {
      echo "No user found"; 
    }

    // Close the connection
    $dbh = null; 
  } catch(PDOException $e) {
    echo "Connection failed."; 
  }
} else {
  echo "User ID not specified or invalid."; 
}

For more details on preventing SQL Injections in PHP, see the following article: Preventing SQL Injections.