[SOLVED] Using mysql_real_escape_string(), but still interpret slashes literally

[batch]

I'd like to have MySQL read queries that contain a " \ " literally - even after they have been mysql_real_escape_string()'d. For example:

 

$match = "Ti\m";
$match = mysql_real_escape_string($match);

 

SELECT *
FROM table
WHERE name = '$match'

 

Executing this query returns all rows where it matches the name Tim.

However, I want it to only return rows where it exactly matches the string Ti\m. I know I can force this behavior by adding an extra slash (ie. Ti\\m), but why doesn't it work by default?

 

Isn't mysql_real_escape_string() supposed to escape the string BUT RETAIN ITS ORIGINAL MEANING?  ???

[PFMaBiSmAd]

The problem is because mysql treats The \m in Ti\m as just an m. So, you must actually double the \\ before the mysql_real_escape_string() function is applied.

 

The code -

$match = "Ti\m";

$match = mysql_real_escape_string($match);

 

would give "Ti\\m", but when it is parsed by mysql, that gives "Ti\m", which is the same as "Tim" because 'm' is not a special character that needs to be escaped.

 

However, that would imply that you have a function that tests if the \ is followed by a special character or a normal character and only doubles it if it is followed by a normal character.

[batch]

Edit: Are there any other characters besides the backslash that need extra escaping after being mysql_real_escape_string()'d?

[batch]

I found a useful entry in the MySQL manual [ here ].

Thanks PFMaBiSmAd!

[batch]

I just wanted to share some more information with you. It turns out mysql_real_escape_string() doesn't escape all special characters. For example the % and _ operators for a LIKE clause are not escaped. To properly sanitize the input and literally interpret the $match query I use addcslashes() after mysql_real_escape_string().

 

$match = "Ti\m";
$match = mysql_real_escape_string($match); // Escapes \x00, \n, \r, \, ', " and \x1a 
$match = addcslashes($match, "\\%_");  // Escapes \, % and _

 

Notice how addcslashes() escapes the backslash character (" \ ") again. This is intended behavior so MySQL will interpret the backslash literally. To show you the flow of events:

 

Without addcslashes()

"Ti\m" -> escapes to "Ti\\m" -> MySQL unescapes to "Ti\m", unescapes to "Tim" (since "\m" is also treated as a sequence to be unescaped).

 

With addcslashes()

"Ti\m" -> escapes to "Ti\\m" -> addcslashes to "Ti\\\\m" -> MySQL unescapes to "Ti\\\m" -> one literal slash + "\m" -> MySQL unescapes "\m" -> result: "Ti\m"