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;

}

Solution 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().

Solution 2

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

Solution 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);

Solution 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 ^^).

View more solutions