Nick Duncan

SQL Injections can leave your website crippled and useless and most developers haven’t even thought about this. Do you know what a SQL injection is? Here is the official 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.

How they do it
SQL injections can be inserted through the use of your website’s forms as well as your global variables. An attacker would analyse your forms and attempt to manipulate the way you insert data into your database.

Example:
Lets say one of your pages is as follows:
http://examples.co.za/product.php?product=salt
And the attacker adds ‘ or ‘a’='a to the end of the URI like such: http://examples.co.za/product.php?product=salt’ or ‘a’='a
What this is essentially doing is changing your unprotected SQL query to something like
SELECT * FROM product WHERE product=’salt’ or ‘a’='a’
Instead of the query now looking for products that equal ’salt’, it now selects everything regardless! By using this in a log in form the attacker may be able to gain access to the site without actually logging in. This is an extremely easy method of manipulating your query. There are a lot more malicious techniques out there.

What to do
If you are using a solid framework you are relatively safe but in actual fact, SQL injections are hard to stop as there are many ways to pull this off. Here are some steps you can take to ensure some heartless moron doesn’t come along and wipe your database off the face of the earth or worse, gain access to passwords you don’t want them to know about and wreak more havoc.

Sanitizing Function
This simple yet effective sanitizing function escapes special characters in a string for use in a SQL statement.

<?php
  function sanitize_slash($string) {
    // make sure you are connected to your DB before attempting to return this function
    return mysql_real_escape_string($string);
  }
?>

The above method will add a ‘\’ to single and double quotes to ensure your query cannot be manipulated. The next method eliminates ALL special characters except for a-z, A-Z and 0-9. Your choice is dependent on your websites functionality.

<?php
  function sanitize_all($string) {
    return preg_replace( "/[^a-zA-Z0-9 ]/i", "", $string );
  }
?>

For example, if you would like to sanitize the input of a text field, use the first function. If you would like to sanitize the input of a users username, use the second function.

Example:

<?php
  $aboutme = sanitize_slash($_POST['website']);
  $firstname = sanitize_all($_POST['firstname']);
  $surname = sanitize_all($_POST['surname']);
  $email = sanitize_slash($_POST['email']);
  // you should get the picture now...
?>

Another good method to use in conjunction with these functions is to check if the given input is the expected data type. IE: If you are expecting a number format, double check it with the is_numeric() function.

One last thing to remember, make sure you turn off error_reporting(); The last thing you need is to show the attacker the database error details!

<?php error_reporting(1); //add this right at the beginning of your file ?>

So in closing, make sure you follow these steps to try and eliminate any potential attacks that may occur:

• Never trust user input.
• Sanitize your variables before attempting to insert them in a SQL query.
• Make sure your form < input > names are not the same as your table’s field names.
• Make sure you double check expected data types.
• Turn off error_reporting()

Good luck and as always, if you have anything to add to this, please feel free to insert your suggestions or examples below.