Hands on: SQL injections

The result? If there’s a table called users, with a username and email column, they’ll get a handy list of users, followed by a colon and then their email address. So, how can you stop this happening?

First, always validate data before feeding it straight into a database query. The basic protection added to a script by Dreamweaver isn’t enough. If there are only certain values that can be used for the category, check for them.

And if the only possible values are numeric, use that to your advantage. PHP contains a function called is_numeric, which you can use to check if it really is just a number you’ve got, or you can use intval to extract the numeric part of a string. Either would protect your script.

To see what people are trying to do to your server, add a short snippet of code like this, before the query:
if ( isset($_GET[‘catid’]) && ! is_numeric($_GET[‘catid’])) {

// send an email to admin
$errbody = sprintf(“A script was called with invalid parameters\nRequested URL: %s\nOriginating IP: %s\nQuery string: %s\nReferrer: %s\n”,

$_SERVER[‘REQUEST_URI’], $_SERVER[‘REMOTE_ADDR’], $_SERVER[‘QUERY_STRING’], 4 $_SERVER[‘HTTP_REFERER’]) ;
mail(‘admin@mysite.com’, ’Script error’,$errbody) ;
header(‘Location: /index-error.php’) ;
exit ;
}

This will redirect the visitor to the page index-error.php, where you can issue dire warnings, or just say, “Sorry, you typed something wrong.”
Obviously, you can’t use such a simple check if catid can be a range of words; instead, you would need to use something like this:
( $_GET[‘catid’] != ‘brakes’ ) && ( $_GET[‘catid’] != ‘fuel’)