gunnard.org

How can I prevent SQL injection in PHP?

Preventing SQL injections is a popular topic. #1 on stackoverflow (for php) and also listed on the OWASP top 10. So what is an SQL Injection? The first part “SQL” refers to Sequential Query Language which is used in querying databases like Mysql, Postgres SQL or mariaDB. The term is meant to be used as a generalization for any database connected to a web application. “Injection” is defined by OWASP as:

an attacker’s attempt to send data to an application in a way that will change the meaning of commands being sent to an interpreter. For example, the most common example is SQL injection, where an attacker sends “101 OR 1=1” instead of just “101”. 

https://owasp.org/www-community/Injection_Theory

What this means is that while the programmer has developed their code in a way that will query the database for specific data, the attacker will manipulate the query to return different data. The data that the attacker requested can be as damaging to the company under attack as the more private the data stored on the server.

As stated in the quote above the basic example of an attack is using “OR 1=1”. Let me explain how this works. When the intended query is excecuted by the server, “SELECT * FROM USERS WHERE “username” = username” for example, (the first username is whatever is typed in from the web page and the second username is the field in the database), if vulnerable to SQL Injections, the attacker would insert “OR 1=1” to the end followed by their malicous query “SELECT * FROM CREDIT_CARDS”. The key is that the attacker is extending the query with “OR” and then giving the logic of “1=1” which is always “true”. The server will first try and match up the user input for “username” with a stored value in the database but if nothing is found, the “OR” says “if no username is there, try this next bit” and the malicious query will then run. Hopefully you can see the dangers of attacks like these.

How to prevent SQL Injections in PHP

1) Use prepared statements and parameterized queries.

Prepared statements are SQL statements/queries that are sent to the database server separately from any parameters. This makes it practically impossible for an attacker to inject malicious code. There are two ways to implament this.

1. PDO

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Using PDO (for any supported database driver):

$query = $pdo->prepare('SELECT * FROM users WHERE name = :name'); 
$query->execute([ 'name' => $name ]); 
foreach ($query as $row) { // Do something with $row } 

2. MySQLi (for MySQL)

$query = $dbConnection->prepare('SELECT * FROM users WHERE name = ?'); 
$query->bind_param('s', $name); // 's' specifies the variable type => 'string' 
$query->execute();
$result = $query->get_result();
while ($row = $result->fetch_assoc()) { // Do something with $row }

If you are using a database other than MySQL there are database specific options for PDO. PostgreSQL for example uses PDO_PGSQL DSN to connect to the database.

Turning off emulation of prepared statements

By default, prepared statements are not used by default and have to be enabled for PDO to access MySQL. Emulation must be disabled in order for the connection to be created.

$conn = new PDO('
mysql:dbname=wordpress;
host=127.0.0.1;
charset=utf8', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The error mode attribute is not necessary for the prepared statements but is advisable and recommended for developers to use. The key to this code is the first setAttribute() line. This tells PDO that we want to use the real prepared statements. Once enabled, this will make sure that the statements and values aren’t parsed by PHP before being sent to MySQL. The attacker will not have an opportunity to inject malicious code once this is enabled.

You can read more about PHP PDO in order to secure your code.