SQL Injection problem

Hi,
I have had an SQL Injection attack on a website. It got through the code (pasted below) that I use to prevent such attacks.

The issue only occurs when I use the $type == 'text' option. It seems that someone has found a way past the no_injection function. While I didn't think it was fool proof, I did hope that since I only use the $type == 'text' option VERY rarely it would be sufficient...but I guess once is enough to be vulnerable.

Here is an example of the injection:

.php?text=-2+union+all+select+1,concat_ws(0x3a,0x5e5e,date(...........

So I figure the issue is with the 0x3a and 0x5e5e being used rather then special characters that the no_injection function would escape.

I have recently started using mysqli_stmt_bind_param for all of my inserts and updates that don't just involve numbers. That should prevent the problem for those.

In the rare occasion that I need to use $type == 'text' how can I prevent this type of code from working???

Any other suggesting to shore up my code would also be very much appreciated!

Here is the code:

function stopInject($type, $value, $maxlen = 100) {
	if($type == 'int') {
		if(is_numeric($value)) {
			return intval($value);
		}
		else {
			return 0;
		}
	}
	else if($type == 'float') {
		if(is_numeric($value) || is_float($value)) {
			return floatval($value);
		}
		else {
			return 0;
		}
	}
	else if($type == 'string') {
		// numbers, letters and a few select special characters, limit length
		$value = preg_replace("/[^0-9A-z\#\._-\s]/", '', $value);
		return substr(no_injection($value), 0, $maxlen);
	}
	else if($type == 'password') {
		// limit to 20 characters 
		return substr(no_injection($value), 0, 20);
	}
	else if($type == 'email') {
		// should have already checked for a valid email in validation
		if(checkEmail($value)) {
			return  no_injection($value);
		}
		else {
			return 'invalid email';
		}
	}
	else if($type == 'text') {
		// eeek try not to use...
		return  no_injection($value);
	}
	else {
		return '';
	}
}

function no_injection($string) {
	$string = htmlspecialchars($string);
	$string = trim($string);
	$string = stripslashes($string);
	$string = mysql_real_escape_string($string);
	return $string;
}

Code (markup):

 

you can remove certain words from the parameter, if they would never be used anyways...

IE: parameter is Full_name, you can remove any occurrence of "SELECT, DELETE, etc", since no one would have select or delete as their name.

Alternatly, in the database could store any words such as SELECT as a key instead (IE; SELECT = 284132, which points to another table where 284132 = SELECT). Then replace all words in the parameter which have keys assigned to it, to the key # instead.

 

Thanks for your reply. I do want to avoid checking each and every parameter in it's own unique way. I use the 'string' option for things like names which strips out everything but numbers letters and a few special characters....and it limits the number of characters.

But I do like your idea of changing the key words in MYSQL. I am going to look into that further. Could be useful.

Anyone have ideas on preventing the this type of injection in the PHP:

page.php?text=-2+union+all+select+1,concat_ws(0x3a,0x5e5e,......a bunch of column names.........,0x5e21)+from+tablea+a,tableb+b+where+a.cID=b.id++order+by+2+desc+limit+0,1--

Code (markup):

maybe strip out + ????

Chris

 

If your database defaults to an 8-bit character set but you've set a table to utf8, mysql_real_escape string won't save you - you need to use prepared statements.