PHP Security Mini Guide – SQL Injection

PHP is the world’s most popular (Server-side) Web Programming Language. According to W3Techs, as of November 2017, 83% of the websites are powered by PHP. Among those websites are Facebook, Yahoo and Wikipedia. Since PHP is so popular, PHP security is essential since the number of vulnerable web applications is large. Finding a web application which does not share part of a code/script with another is rare. That means that if the shared piece of code is found to be vulnerable, all the applications which are using it are also vulnerable.

When developing a web application, it is extremely important to have security in mind and be aware of the different risks. If one does not know the risks and the mechanics behind each vulnerability, there is no way to protect against it.

Most of the vulnerabilities are a result of bad coding habits or lack of PHP security awareness by the developers. The source of probably all of them relies in the fact that user input, which plays a critical role in the security of a web application, is being trusted. This is probably the single point of failure which results in the many different attacks we have seen over the years.

Two things need to be taken into consideration when writing code, Validation and Sanitization. By implementing both, we ensure at a certain level that what is processed and executed is valid and meets certain criteria which we specify based on what needs to be allowed and what is not. When it comes to output, data must be Escaped to ensure that no malicious code is executed in case it was injected in the content during input.
If a developer follows some simple and basic procedures, they significantly minimize the possibility of being exposed to a critical vulnerability. At this point it is important to mention that OOP (Object Oriented Programming) plays a big role in applying PHP security procedures. Properly written reusable code can dramatically help in the overall security of a system as it ensures that the same data processing procedure is always followed.

Let’s get started with some examples of common vulnerabilities in PHP and how they can be mitigated.

SQL Injection

SQL Injection is one of the most dangerous vulnerabilities a web application can be prone to. When user input is being passed (unvalidated/unsanitized) as part of an SQL query it means that the user can manipulate the query itself and force it to return a different result to what it was supposed to return. Successful exploitation of an SQL injection vulnerability can result in a data breach which can expose usernames, passwords, email addresses, credit card information and other sensitive data. In some cases, it can even result in the compromise of an entire server.

In the following example the article parameter is being insecurely passed to the query:

Insecure code sample:

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

A user can send a specially crafted value which will be included in the SQL query before it is executed. An example would be: 1'+union+select+1,version(),3'. The query becomes:

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

Now the attacker with a few more requests can enumerate all the tables/columns of the database and exfiltrate sensitive information.

The solution to this problem is to use parameterized SQL queries (prepared statements). By using parameterized queries, we essentially let the database know which part is the query and which is the data (user input) by sending them in two separate requests, thus eliminating the possibility of mixing user input and the SQL query.

As we can see in the example below, the parameter user_id is not being passed to the query directly but instead a placeholder is replacing it. The backend database will know what to replace the placeholder with when we run the execute() function.

Secure code sample:

// We first check if the request meets the specified criteria. To proceed, the user_id variable must not be empty, be numeric and less than 5 characters long.

if((!empty($_GET['user_id'])) && (is_numeric($_GET['user_id'])) && (mb_strlen($_GET['user_id'])<5)) { 

// If the request is valid we proceed 
$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 $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.";
}

Using the above method, we can safely select data from a database. You can read more about preventing SQL Injection and PDO functionality here.

Avoid using mysql(i)_ extensions

Mysql(deprecated) and Mysqli extensions are outdated but still being used to date. It is recommended to replace your code with PDO (PHP Data Object) which not only is Object Oriented and supports prepared statements but is also versatile as it allows you to move to another SQL database server if needed, with only minor changes to the code.