Beginners Guide to SQL Injection & How to Prevent it From Happening to You

This article is meant to be a brief overview of SQL injection and how you as a developer can take a few simple steps to stop it from happening. Simple injection attacks can cause headaches, unnecessary stress in a client relationship, and can even sometimes have a financial impact on yourself or a client.

SQL Injection is nowadays a dying problem in the web development community due to stricter coding and the mass use of popular open source projects like WordPress, but it is still definitely something that can not be ignored. Simple coding mistakes can cause a huge vulnerability to the entire site structure.

Let me start by giving a general overview of what an SQL injection attack is:

Wikipedia definition

SQL injection is a code injection 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 an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.

So what does that mean in English? At the core of it, it means that anywhere you have a form field, or declare a variable in the URL of your site you are leaving yourself open to an injection attack unless you properly escape the string.

So for example, if I have a database with a table full of staff bios for my organization, I might number each row with an ID. In my PHP I might attach this ID to the end of my "bio.php" page as something similar to

bio.php?id=12

where "12″ is equal to a specific bio that I am trying to read.

There is nothing wrong with using this technique to code your sites with, but if you do no properly escape the string or define the variable as being numeric only, you open yourself up to be attacked.

If you are using the _GET method to read the ID there are a couple of options to guarantee the safety of your site.

Here is an example of vulnerable code:

$id = $_GET['id'];

If I am a hacker and I notice that your site has links in this format (yoursite.com/bio.php?id=12) I know that you are defining the variable $id as 12. It is also safe to assume that you are accessing the database with this specific id using code that probably looks something like this:

$sql = "SELECT * FROM $table_name WHERE id = $id";

If you haven't escaped the string a hacker could easily type in his own code to the end of the URL after "12″. An example of that would be:

<a href="http://www.yourdomain.com/bio.php?id=12;DROP">http://www.yourdomain.com/bio.php?id=12;DROP</a> TABLE users;

This would obviously wipe out any table you had named "users" in your database. This is not good. If your business depended on membership to your site and all of a sudden a hacker deleted all of your user information, you might as well close up shop.

Luckily for all of the membership driven shop owners out there, I have a solution! (Well, PHP has a solution, I can't take credit for that part of it...)

One way to prevent this from happening is by using the "mysql_real_escape_string" function. This functions sole existence is to strip out any special characters that have been attached to the end of the variable. Example:

$id = mysql_real_escape_string($_GET['id']);

Now if someone was to try and attach our "drop table" code, it would strip out the semicolon and would not work.

Another way to beat injection attacks is to use the "intval" function. This forces PHP to convert any string of text and numbers to the actual integer values that are present.

So for example if the hacker typed:

http://www.yourdomain.com/bio.php?id=12;DROP TABLE users;

and your PHP code was:

$id = intval($_GET['id']);

PHP would try and convert the "id" variable to a number. Since ";DROP TABLE users;" is all non-numeric, it would simply ignore that string and just produce "12″ as the value of ID.

One extremely useful tool for PC users (sorry Mac people) to check the vulnerability of your website is the

Havij SQL Injection Tool

. I have used this tool for quite a while now and it is really an eye opener to seeing just how vulnerable your sites might be.

Hopefully you have found this short tutorial on SQL injection useful. It can be a huge headache to try and restore a site if it has been defaced by a hacker. It is thousands of times easier to take the steps up front to prevent such an attack from happening in the first place.

My name is Jarred Smith and I am the owner of http://www.phpsandbox.org where I write articles, tips and tutorials for PHP development.