Preventing SQL Injection in PHP

1st January 2017

SQL Injection is a method of an attacker altering SQL commands to expose, override data or execute dangerous commands. Preventing SQL injection in PHP can be achieved by following the steps in the post below.

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

' or '1'='1

The original SQL query that authenticates the user could be similar to the following:

SELECT * FROM Users WHERE User='' AND Password='';

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

SELECT * FROM Users WHERE User='' or '1'='1' AND Password='' 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.

What’s more is that the attacker could add a DROP statement in the username field for example, filling in the field with the following:

123; DROP TABLE Users; --

The value might result in the SQL statement looking like:

SELECT * FROM Users WHERE User = 123; DROP TABLE USERS;

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 User is equal to 123, then delete the table.

There are several ways of preventing SQL injection. Methods include constructing prepared statements.

We could use the PDO extension to achieve this.

$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
}

Or by using MySQLi.

$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
}

Note: This article is based on PHP version 5.5.