SQL Injection related question

PHP
1

Hai folks,

every piece of user input (ex. login) will have to go through the below two functions in my project.
how good these functions are against an sql injection?

<?php

function filter($str){

	$str=strip_tags($str);
	$str=mysql_real_escape_string($str);
	
	return $str;
}


function compare($str){
	
	$arr = array 
	("select","union","order","by","update","drop","use","group","by","insert","load_file","into","in","to","outfile","having","substr","hex","unhex","where","--","/","\\'","\\"");
	
	for($i=0;$i<sizeof($arr);$i++){
		 $q=strpos(strtolower($str),$arr[$i]);
		 if($q!==false){
			return true;
			exit;
		 }
	}
	return false;
}
?>
2
  1. mysql_real_escape_string will only prevent sql injection in case of string values (that is, variables you put between quotes in your query). In a case like this, it won’t help:

// user input: $_POST['id'] = '2 OR 1 = 1'
$id = mysql_real_escape_string($_POST['id']);
$query = '
  SELECT *
  FROM tablename
  WHERE id = $id
';

If you echo the value of $query, it’ll be


SELECT *
FROM tablename
WHERE id = 2 OR 1 = 1

So you’ll have to distinguish between alphanumeric and numeric values, and sanitize accordingly.

  1. For the second function, you might want to look into foreach and [URL=“www.php.net/in_array”]in_array
3

or [FPHP]preg_match[/FPHP]

4

Thanks guido and StarLion for the suggessions.

5

OT SQL injection is only part of it, check out http://htmlpurifier.org/

6
  1. ^ what they said
  2. Seems a bit over the top and missing the point. What if really want the word “update” or “select” in my text? They are not really uncommon words, but your function would stop me from using them. I’d do away with that function.
7

If you use PREPARE/BIND then the query and data are kept completely separate and SQL injection is therefore impossible.

8

What felgall said. I would avoid using the standard MySQL library and go with either PDO or MySQLi so you can use prepared statements.