Zend PHP 7 Certification – Security – SQL Injection

1st September 2017

This post covers the SQL Injection section of the PHP Security chapter when studying for the Zend PHP 7 Certification.

SQL Injection is a type of attack where an attacker creates or alters existing SQL commands to expose, override data or execute dangerous commands against the host. It can be as simple as adding malicious content in form fields.

Consider a simple form that contains a username and password field. Without sufficient security measures, the attacker could add the below values into both form fields.

' or '1'='1

Whilst this looks strange, the values entered are used in combination with the existing database query, potentially causing devastating effects.

The original SQL query that authenticates the user could be similar to the below.

SELECT * FROM USERS WHERE USER='' AND PASS='';

All of a sudden, the modified query looks like this:

SELECT * FROM USERS WHERE USER='' or '1'='1' AND PASS='' or '1'='1';

As '1' = '1' will always result to true, the statement will return all of the columns data for the users table, exposing the data to the attacker.

The attacker could even go as far as adding a DROP statement in a username form field for example, filling in the field with the following:

123; DROP TABLE SOMETABLE; --

The value might result in the SQL statement looking like the below.

SELECT * FROM USERS WHERE UserID = 123; DROP TABLE SOMETABLE; --

As most databases support batched SQL statements that are separated by a semicolon, we therefore have two separate queries: select the record from the USERS table where the UserID is equal to 123, then delete the table.

There are several ways of preventing SQL injection by using prepared statements. A common method of using these queries is done via PHP Data Objects (PDO).

$pdo = new PDO('mysql:host=example.xom;dbname=database', $user, $pass);

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

As PDO is more portable across databases and supports the use of named parameters, it is preferred over using MySQLi and its prepared statements, although both options will protect you against SQL injection if the code is written well.

$dbConnection = new mysqli("example.com", "user", "password", "database");

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

Other SQL injection avoidance techniques should also be taken into consideration, such as not connecting to the database as a superuser or as the database owner. Instead, use customised users with very limited privileges.

View the other sections:

Note: This article is based on PHP version 7.0.