SQL Injection. We’ve all heard the term. But what exactly is it? Sounds dirty and vile… or maybe something you get at the doctor’s office. A new type of plastic surgery maybe? Well, let’s find out, shall we?

From Wikiedpia.com:

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

What does this all mean? Basically it means someone takes your queries, inserts additional commands, causes your code to do things you didn’t intend it to do – potentially damaging or changing your data, gaining administrative priveleges, or worse.

Many PHP developers don’t think about this, assuming that PHP and mySQL take care of everything to make your code bullet proof. This is unfortunately not the case – or is it? PHP has built-in functions which allow you to sanitize and secure your code and queries, but they’re not automatic – you have to call them manually.

So what does a SQL Injection look like? How does one happen?

Let’s take the following example:

You have a login-form that accepts a username and password. When submitted, the form values are passed through the $_POST variable and received and processed.

login_form.php

An attack happens when a user enters in values that you don’t plan for and through that, tricks SQL into doing all the aforementions evil evil things.

Take the following process_form.php form example:

BAD process_form.php

If the would-be attacker entered admin and iamgod in the previously mentioned form, the sql statement would look like this to the SQL server:

But what if the would-be attacker entered in something different… what if for the password field, they entered the following:

iamgod’ OR 1=’1

The SQL statement would then look like this:

The 1=1 would always be true, therefore side-stepping around the username and password check. Using methods similar to this, an attacker could manipulate your code in a variety of malicious ways by injecting whatever they wanted.

So the million dollar question…

HOW DO I PREVENT A SQL INJECTION

There are a variety of ways to prevent SQL Injections. The most basic one is the one I will discuss. For further reading on the subject, please see the links at the bottom of this article. Sanitizing your $_POST variables is very easy, and is a good beginning towards developing bullet-proof code. In PHP, I recommend at the very least that you do the following:

Going along with our previous example, here’s process_form.php rewritten:

BETTER process_form.php

Did you see it? mysql_real_escape_string() function escapes any characters that could potentially change your query.

From www.php.net:

[mysql_real_escape_string…] escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL’s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

And that’s how to protect yourself from basic SQL injections. There are other preventative measure you can take. As a rule of thumb, I always try to validate my form data before it even gets to the sql statement. Am I expecting a string or an integer? What did I receive? What do I want to do with this?

But basically, to be safe, at the least remember the following rules:

• Never trust the user!
• Validate your data!
• Always escape your characters!
• Never trust the user!

If you can do that, then you’re off to a good start. Good luck, keep it clean kiddies.

Further reading on SQL Injections:

• www.acunetix.com: SQL Injection – Finding and Fixing it
• www.governmentsecurity.org: SQL Injection – Modes of Attack, Defense, and Why it Matters