MySQL Real escape string

Question

I have an insert function where I use MySQL real_escape_string() to remove illegal characters only its not actually removing those characters, can anybody see where I'm going wrong?

    $interest = mysql_real_escape_string(urldecode($_GET['interest']));

    $query   = "INSERT INTO user_interests (user_id, interest) VALUES('{$user_id}' , '{$interest}')";
    mysql_query($query) or die(mysql_error());
    echo $interest;

Answer 1

There are no "illegal characters". mysql_real_escape_string just encodes all characters so that they can be safely put into a query. If you want to remove a character c, use str_replace:

$input = urldecode($_GET['interest']);
$input = str_replace('c', '', $input);
$interest = mysql_real_escape_string($input);

Answer 2

mysql_real_escape_string just escapes characters in your string that might cause problems when you try to write them to your database. This does not mean that it removes them.

Imagine you are taking user input and a user puts a quote into the input field. When you try to insert that string to your database, the quote will be interpreted as a quote in the sql query and the query won't work right.

INSERT INTO table (string)
VALUES ("this is a string with an extra " in it")

If you use mysql_real_escape_string on this string first, then your sql query will essentially look like this:

INSERT INTO table (string)
VALUES ("this is a string with an extra \" in it")

See the escape backslash above. You can see this extra quote even messes up the formatting here on SO.