SQL Injection – An important lesson

Are you inserting user data into a database using PHP and looking for the best way to prevent SQL injection? Well, I recently came across this question on LinkedIn (linked from another Q&A site) where a user asking how to prevent an SQL injection attack posted this code:

$unsafe_variable = $_POST[‘user_input’];
mysql_query(“INSERT INTO table (column) VALUES (‘” . $unsafe_variable . “‘)”);

The reason I bring this up is not because of the question – it’s a very valid one for developers – but because of the answers. Read on to find out why…

What is SQL Injection anyway?

According to w3schools, the definition of SQL injection is as follows:

SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input.

And it’s pretty straightforward. You have an input field to accept a string for “last name” (for example), the user types in a command to drop all tables instead of his last name and in the middle of your insert statement – BOOM – all your tables are gone. It’s a big problem to deal with and one we’d all rather avoid.

So? What was wrong with the answers?

Nothing really. They were all the same, sensible suggestion (mostly) to use PDO with parameters instead of mysql_query.

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. (php.net)

And it’s true that PDO helps prevent SQL injection by ensuring that the data is of the correct type as you specified in the query and since all the mysql_* functions have all been deprecated since PHP5.5 this is a great idea, but this doesn’t touch on why or tell the whole story. The story is…

Never trust user input!

This was a lesson drummed into me as a junior developer fresh from my first job building websites for small businesses and thrust into the corporate world. Why shouldn’t we trust users?

Users are unreliable.

Now hold on – I don’t mean to imply that users are malicious in any way, most don’t mean to do it. They’ve simply gotten confused and put their last name where their ID number was supposed to go or something similar to that. A form that’s asking for a city name and providing a text field is simply asked for duplicates via creative spelling differences. And there are some really badly designed forms out on the internet.

However, some do it on purpose. They attempt to gain access to your application by retrieving user passwords or they delete your database and ruin your day among other the things that an enterprising hacker can do.

So what do I do?

But Kevan, I hear you ask, what can I do about this? Well, three things really:

  1. Validate your input – check whether it’s the correct type of input or if it even exists. If your input field is for a phone number, don’t expect a boolean. In some cases, you may decide that you can pass the input on to be sanitized anyway (mostly strings, anyway).
  2. Sanitize your input – remove any unwanted characters from the input by stripping HTML tags and other characters based on the type of input (URL’s require characters that may be disallowed otherwise)
  3. Please, please, please don’t use your MySQL root password in your application. Create a limited access user with at least read and write access and use that instead.

Please check out PHP’s filter_vars functionality for native validation and sanitation.

Disclaimer…

I don’t mean to sound like I know everything about this. This isn’t ground-breaking or mind-blowing. There’s nothing new in this post. And, for the most part, those answers were correct – our questioner will most likely never to know more than “PDO prevents SQL injection”. Nor will he likely need to know how his framework handles SQL injection, just that (weirdly) he has never experienced any SQL injection while using said framework as per the documentation. But at the core of both PDO and the unnamed framework are these words:

“Never trust user input.”