Stopping SQL Injections

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.

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.

myScoop – from concept to current and beyond

Every now and again I get this sudden urge to develop something that’s beyond my reach. Enter myScoop, my latest “lets-see-if-I-can-do-it” project. myScoop is essentially a combination of most of my PHP skills that I have learnt in the passed few months and when I started with the site it was originally supposed to be a social bookmarking tool, similar to that of Muti. I’m not 100% sure of when the focus shifted to blog aggregation. Either way, I’m pretty happy of the result.

myScoop Logo

Every now and again I get this sudden urge to develop something that’s beyond my reach. Enter myScoop, my latest¬†“lets-see-if-I-can-do-it” project. myScoop is essentially a combination of most of my PHP skills that I have learnt in the passed few months and when I started with the site it was originally supposed to be a social bookmarking tool, similar to that of Muti. I’m not 100% sure of when the focus shifted to blog aggregation. Either way, I’m pretty happy of the result.

The first stage was nothing too advanced. I wanted users to be able to submit their favourite bookmarks and let my site crawl that page, retrieve the title tag and then retrieve the first paragraph of the blog/page. The user would then simply just select a category and throw in a couple of tags and press submit. The concept was easy enough and worked perfectly. myScoop would automatically assign you a shortened URL and go on to post that URL, Title, shortened description and the first tag to Twitter. Soon after that I got another bee in my bonnet and decided each submission needed its own stats page whereby a user can view how many hits it had received in the passed 30 minutes or hits per hour for the current day.

About a week later I launched the blog aggregation side of the site with the ability to add your own blog and follow other blogs. Again, the concept is simple enough; you would submit your blog address and the blog RSS feed location, and the site would crawl your RSS feed and fetch your article information. During this stage the site underwent a bit of a face lift with the help of Bonita who created the amazing myScoop logo and some great ideas from Bryan.

There are currently 2 articles discussing myScoop in fair detail:

There will be many more changes and upgrades to myScoop in the coming months. The only way myScoop is going to succeed is if it has functionality that is easy to use and understand. The only way I can get this accomplished is with the help of YOU. So im asking everyone that uses myScoop for all your ideas, comments and suggestions. Thanks for the support!