Stripping User Input ¦ Prevent SQL Injection

a little advice please

[ukgimp]

Say a search box on a site needs to be stripped of dodgy characters prior to each term being whacked into an array how far should it be taken. Is the following snippet enough?

$test = "word1 word2 word3 ";
$test = trim(urldecode($test));
$test = ereg_replace("([ ' ]+,)"," ",$test);
$test = preg_replace("(\s+)", " ", $test);
$stringArray = explode(" ", $test);

The process should also ensure that each array value has nothing other than the word with no whitespace or similar.

Cheers

[Dreamquick]

Generally speaking I know a little about this - it would appear you're testing input against a list of known issues - am I right? The problem with this method (exclusive validation) is that you can only detect problems you have coded for - meaning anything you haven't coded for will slip past undetected.

A much better strategy is to filter the input against expected contents (inclusive validation) - for instance if your input can only contain A-Z & 0-9 (theoretical) then it's much easier to check that each character is either A-Z or 0-9 and reject those that aren't, rather than trying to filter out an much larger number of SQL injection routes.

This way you can limit your exposure to possible injection methods, for those possible routes you choose to expose you can then handle each one gracefully resulting in a much more secure handling system.

Aside from this the only other SQL injection related advice I can offer is; "treat any input you don't have full control over as unsafe until you have reliably proven that it's safe"

- Tony

[ukgimp]

Tony

I have come across that option on my travels and I suppose I wonder which is the best option(s) to do a variey of things.

Cheers

[jaski]

I guess a whitelist is a better option than a blacklist .. you can loosen it as you go .. rather than tighten it at discovery of every vulnerability. Depends on situation really.

btw user comments on this page might be worth a look as well

[in2.php.net...]

[Dreamquick]

Based on my experience I'd say that an inclusive model ("whitelist" works too) is always the best choice - primarily it provides perfectly validated inputs, the fact that it's more secure is just a bonus!

Admittedly there are times when you simply want to give the user more freedom, but generally once you have the basic model working you can adapt it to work with a much bigger range of rules - ie starting with a username input with basic rules and working all the way up to something like a feedback textarea.

Before now I've created my rules as server-side regular expressions - with a little bit of cunning you can make the client side validation use the exact same regular expression which makes life a little easier for the user too as well as reducing the amount of duplicated work you have to do on client side validation.

- Tony