PHP Function to replace symbols with character codes to stop SQL Injection

Question

I am trying to write a php function to stop MySQL injection attempts. What I am doing is using str_replace() to remove symbols and replace them with with their HTML character code. My issue is that the codes all contain &#; but I also want to replace those symbols with their codes. How can I do this without changing the code into something like:

&#38&#59;&338&#59;#35&#59;32&#59;

Here is my function:

function replaceSymbols( $text )
{
   $text = str_replace( '#', '&#35', $text );
   $text = str_replace( '&', '&' $text ); 
   $text = str_replace( ';', '&#59', $text );

   $text = str_replace( ' ', ' ' $text );
   $text = str_replace( '!', '!' $text );
   $text = str_replace( '"', '"' $text );   
   $text = str_replace( '$', '$' $text );
   $text = str_replace( '%', '%' $text );  
   $text = str_replace(  "'" '&#39', $text );
   $text = str_replace( '(', '(' $text );
   $text = str_replace( ')', ')' $text );
   $text = str_replace( '*', '*' $text );   
   $text = str_replace( '+', '&#43', $text );
   $text = str_replace( ',', ',' $text );
   $text = str_replace( '-', '-' $text );
   $text = str_replace( '.', '.' $text );   
   $text = str_replace( '/', '&#47', $text );
   $text = str_replace( ':', ':' $text );   
   $text = str_replace( '<', '&#60;' $text );
   $text = str_replace( '=', '&#61;' $text );
   $text = str_replace( '>', '&#62;' $text );   
   $text = str_replace( '?', '&#63', $text );
   $text = str_replace( '[', '&#91', $text );
   $text = str_replace( '\\', '&#92;' $text );
   $text = str_replace( ']', '&#93;' $text );
   $text = str_replace( '^', '&#94;' $text );   
   $text = str_replace( '_', '&#95', $text );
   $text = str_replace( '`', '&#96', $text );
   $text = str_replace( '{', '&#123;' $text );
   $text = str_replace( '|', '&#124;' $text );   
   $text = str_replace( '}', '&#125', $text );
   $text = str_replace( '~', '&#126', $text );

   return $text;

}

Answer 1

Have you looked at mysql_real_escape_string?

Escapes special characters in the unescaped string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query().

Answer 2

Is there any reason that mysql_real_escape_string($text) doesn't fulfill your needs?

Answer 3

Others have mentioned mysql_real_escape_string() which works very well. If you really want to convert to entities have a look at htmlentities(). If that's still not converting all the characters you want you can also use strtr() like so:

$entities = array(
    '#' => '#',
    '&' => '&',
    ....
);

$converted = strtr($input, $trans);

Answer 4

don't try to solve such fundamental problems - they're already solved. except if you want to learn the basics, but then don't use your solutions in production enviroments. the query escaping problem is solved, by either using mysql_real_escape_string or using parameterized queries, and it works. homebrewed solutions often have subtle bugs or specialities that render them useless. i can't find the article now, but jeff atwood at coding horror (or was it joel?) wrote about a friend who tried to do his own strip_tags function ... and failed. recently, the same for encryption: homebrew fails almost always.

your method:
... isn't very well suited for the task at hand, because classical string escaping is fully reversible, while your method is a one way function (not unrelated to hashing ^^).

Answer 5

I strongly advise you to follow the canned solutions others have posted, such as mysql_real_escape_string(). That said, your function is flawed in a key way. When you are securing something, your default policy should be to deny by default. That means that you assume that any given input or request is an attack, and then examine the input and if you can determine something is definately not an attack, allow it. In the context of your function, that would mean that you check to see if the input is in a set of known, acceptable characters, and escape everything else. pseudocode:

function replaceSymbols(text):
    result = ""
    for each character c in text:
       if isalpha(c) or isdigit(c):
           append c to result
       else
           append escape(c) to result
    return result