Four backslashes for single backslash?

[RedRocky]

I am currently working on using the mysql_real_escape_string function in PHP to make sure that values entered in a web page form for a search query are properly escaped.

 

However, if I have a record with a field value containing single backslash in it, I need to convert the single backslash to 4 backslashes in order to get the field value when using LIKE.

 

For example:

If I have a record with a forename field value of:  Pa\ul

 

The following SQL does not return this record:

SELECT * FROM LISTOFNAMES WHERE FORENAME LIKE 'Pa\\ul'

but the following does return this record:

SELECT * FROM LISTOFNAMES WHERE FORENAME LIKE 'Pa\\\\ul'

 

While writing this I did another search on Google and I seem to have found an explanation for this here:

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

Scroll down to this note:

Because MySQL uses C escape syntax in strings (for example, "\n" to represent a newline character), you must double any "\" that you use in LIKE strings. For example, to search for "\n", specify it as "\\n". To search for "\", specify it as "\\\\"; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

 

On other hand, if I use = instead of LIKE, I only need to use two backslashes to represent a single backslash.

 

And if I use the mysql_real_escape_string function, it only converts a single backslash to two backslashes. If I am using LIKE, which I am, I will need to manually change this to 4 backslashes.

 

It all seems a bit confusing, although I think I understand it now. Any thoughts?

Stephen

[PFMaBiSmAd]

It sounds like your data was escaped twice when it was inserted into the database.

 

If you have one actual \ in the data, when you look directly in the table using your favorite database management tool there should be only one \ character in the table.

[RedRocky]

It sounds like your data was escaped twice when it was inserted into the database.

If you have one actual \ in the data, when you look directly in the table using your favorite database management tool there should be only one \ character in the table.

Yeah there's definitely only one \ in the record in the table. I'm just developing the system at the moment and using test data, so I'm using phpMyAdmin quite a bit to test and change data.