PHP avoiding SQL Injection

Created by
Saturday, August 6, 2016

What is SQL Injection?

SQL Injection is a web application vulnerability. Simply put SQL Injection is a practise in which a  foreign attacker attempts to insert lethal SQL commands into the back-end database. Once this malicious SQL is present inside a database it can be called again. Generally SQL code that is passed into a database is there to do one job only. That job is to damage the web application.

(SQL Injection can get very in depth) Troy Hunt wrote a great article which goes much further into it here.

 

Let’s make this very simple.

A simple textbox on a website that asks the front end user to enter their first name and save it.



Let’s imagine a standard web application where you want to update your first name. In terms of the front end user they would see something like the above. PHP would handle this some like this:

UPDATE Users SET FirstName = ‘Johnny’ WHERE UserId = 13265

 

Now usually when a user would change their first name it would go from say ‘John’ to ‘Johnny’. Simple and completely reasonable change of first name. In the database the string ‘Johnny’ gets updated and inserted in place of ‘John’. Every Time Johnny now logs in to his web app account he sees his name on his profile. This name was read and returned from the database.

Let’s now imagine for example that an attacker attempts to insert something rather than ‘Johnny’. Instead this malicious user actually tries to insert and update his own SQL Script. The next time the web application tries to return that first name from the database instead of reading ‘Johnny’ it reads a SQL Script. PHP will read and execute the SQL code thinking it is legitimate code.

DROP DATABASE database_name

or

DROP TABLE table_name

 

Even worse. Imagine we were able to update the entire users table and set the same thing. In that case the attacker would have basically changed every users password to something they only know themselves. A lot harder but still very possible.

Not good!

Here is a pretty cool SQL Injection Infographic which sums SQL Injection up much better than i did.

 

How does it affect different frameworks?

I have been discussing PHP like it is the only vulnerable code. In theory any database back end is prone to SQL Injection. Ultimately under any app we never want front end users to be able to insert anything other than what they are expected to insert. Most popular frameworks these days go to extensive lengths to validate user input and to parameterise anything passing into the back end database. Regardless of that badly written code is still badly written and can still be a potential entry point.

How to avoid SQL Injection in PHP.

Using prepared statements and parameterized queries. Essentially we are using SQL statements that are sent to and parsed by the database avoiding parameters.

You have two ways of achieving this

 

  1. PDO - PHP Data Objects
$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name'); $stmt->execute(array('name' => $name)); foreach ($stmt as $row) { // do something with $row }

 

  1. MySQLi - Official
$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 }

 

When you decide to use PDO access to a MySQL database real prepared statements are not used by default. To resolve this you need to disable the emulation of the prepared statements.

Create a simple connection using PDO.

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass'); $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Adding the error mode is a highly advised route to take. Scripts will not stop with a fatal error. It also gives the developer the opportunity to catch errors thrown as PDO exceptions.

setAttribute() is the important part. This tells the PDO to disable all prepared statements and to use real statements. Essentially this makes sure that values are not parsed by PHP before sending it to the MySQL Server. This in turn never gives malicious attackers the chance to insert malicious code.

All SQL statements that are passed to prepare are parsed and compiled by the database server. By specifying parameters with either a ? or a named parameter like :firstname you are telling the database engine where you want to filter on. When you call execute the prepared statement is combined with the parameter values that you wish to pass through.

Prepared PDO data queries are the answer to PHP MySQL injection.

0
Rating
1 2 3 4 5

Save

Saved

Report

Reported
Reasons
Cancel
1 comment
Sunday, August 7, 2016

What is the difference between emulated and real prepared statements? Why are emulated prepared statements the default in MySQL?