[PHP Series] Structured Query Language Injection (SQLi)

[Byte-ul]

What is it?

• SQLi is the execution of user-supplied SQL code into an application. It can cause damage to the data store involved, damage and/or theft to the data being persisted, and can also give unauthorised access to admin and user accounts.
  You'll hear of first- and second-order injection attacks. When we prepare our SQL statements to insert user-supplied data, we're preventing first-order injection attacks. A second-order injection attack is just where malicious data that is lying dormant inside of a database is queried and then directly reused inside of an unprepared query. This may happen because the developer forgets that the data being stored inside of his database actually originates from the user - and if no validation was applied upon initial insertion, then that data is still just as lethal to the database as it was upon first insertion.
  Good application design can help to prevent this, as well as being aware of where the data inside of your application has come from (good practice #3 from section A). So provided you're preparing your queries from this first- and second-hand data, you should have no problems at all.

How do I Prevent it?

• Prevention of SQL injection comes in two forms. The first is optimistic escaping where input data is simply escaped before being sent to the data store. The second is pessimistic sanitising, where the data is firstly validated through integrity checks to ensure it contains expected and valid values. It is then inserted into the data store (with or without protection from escaping - depending upon the strictness of the previous validation rules).
  Optimistic Escaping
  • 
    How we escape the data before being sent to the database will depend upon the API we're working with. For the purpose of this section, we will be focusing on using PDO, but will also occasionally reference the MySQLi API since it is also commonly used.
    The typical method of escaping user input to be sent to a database is to use prepared statements (otherwise known as parametrised queries). These work by safely binding values (either in the form of literals or variables) to a query before it is executed, mitigating all possible injection attacks and leaving no room for human error.
    Caveat:
    • Be aware that the

mysqli_real_escape_string() function's primary use is to only escapes quotes (single or double) and backslashes; it does not escape grave accents (commonly used in MySQL to bypass naming convention restrictions with reserved words). If it is also used upon a parameter that is not encased inside quotes (because the value is expected to be an integer) inside a query, then its usage becomes redundant. It's human error like this that can leave your web application open to attackers.

PDO supports named and unnamed (also known as positional) placeholders, unlike the MySQLi API which only supports unnamed. When preparing our queries, PDO also lets us explicitly or implicitly bind parameters to our queries (again, unlike the MySQLi API, which supports explicit binding only). Let’s start by looking at how we can explicitly bind both variables and literals (the MySQLi API cannot bind literals either) to our prepared queries through an example:

<?php
// binding variables
$insertQuery = $db->prepare('INSERT INTO table_name VALUES (:columnA, :columnB)');
$insertQuery->bindParam('columnA', $valueA, PDO::PARAM_STR);
$insertQuery->bindParam('columnB', $valueB, PDO::PARAM_INT);
$insertQuery->execute();

// binding literals
$selectQuery = $db->prepare('SELECT columnB FROM table WHERE columnA LIKE :value');
$selectQuery->bindValue('value', "%{$value}%", PDO::PARAM_STR);
$result = $selectQuery->execute();

Tip:

• Named placeholders must always begin with a colon, and then simply follow the same naming conventions as variables in PHP. (This means named placeholders are also case-sensitive.)

The bindParam() and bindValue() methods above are used to bind parameters to a prepared query, and they require at least two arguments, along with an optional third.
The first argument is the name of the placeholder, the second argument is the variable or value we want to bind to the query, and the optional third argument is the type to bind the variable/value as (the default is PDO::PARAM_STR, however I always specify the type for clarity). The bindParam() method also enables us to specify an optional fourth and fifth parameter, the data type length and any additional driver options respectively.
Tip:

• The bindParam() and bindValue() methods are orthogonal to one another; either or both of them can be used upon one query when binding values to it. This, however, does not work with named and unnamed placeholders; only one or the other may be used upon an individual prepared query.

Lastly, the execute() method is invoked upon to execute the prepared query once all parameters have been bound to it.

Implicit binding is effectively the short-hand version of the above, where we simply prepare our query and head straight to the execute() method, passing to it all parameters to be bound to the query in an array format. The following is an example of using positional placeholders in an implicitly bound parametrised query:

<?php
$insertQuery = $db->prepare('INSERT INTO table_name VALUES (NULL, ?, ?)');
$insertQuery->execute(array('valueA', 'valueB'));

if($insertQuery->rowCount() !== 0) {
    echo 'Success';
}

Tip:

• If named placeholders are being used, then an associative array will need to be passed to the execute() function (keys being placeholder names, and their respective values are those that need binding); if positional placeholders are used, then an indexed array is passed to execute().

We begin by preparing our query and putting the unnamed placeholders (the question marks) in position, and then invoke the execute() method with an array containing the values to be bound to our prepared query. This array being passed can contain either (or both) variables and strings. The downside to this short-hand method is that we aren’t able to specify the type of parameters being bound to our prepared query. Next we question if any rows were updated by using the return value from the rowCount() method, which will contain the number of rows affected from the previous operation. Provided the number of rows does not equal zero, then we consider it a success.
Tip:

• Always be sure to disable emulating prepares when using the PDO API, since it emulates prepares by default (

which can enable for edge-case security vulnerabilities). The MySQLi API always does true prepared statements, and so this isn't a worry if you're using it.

Pessimistic Sanitising

• 
  As was seen in section A of this series, validating data can be done through either a comparison of submitted data to a set of predefined values (i.e. using the in_array() function), or by forcing input data to the correct type (i.e. typecasting), or alternatively by returning an error to the end user if the data submitted is invalid. We will therefore not be covering this form of sanitising again, so please refer back to section A, part 1 of this series for more information.

Credits: http://www.hackforums.net/showthread.php?tid=4238146