MySQL interpreting special characters in generated query

Question

I am trying to migrate the database layer of our framework from SQLServer to MySQL using MySql.data.dll . I have the following generated query in MySQL:

select * from `user` where `user_domainname` = 'domain\beth';

MySQL is interpreting the '\b' in the above string as an bell character whereas SqlServer does not interpret any such escape characters. The solution for this in MySQL -

select * from `user` where `user_domainname` = 'domain\\beth';

To do this in C#, I would have to replace every \b or other such characters with \b which would not be a very feasible option for me considering the number of such transformations I would end up doing.

So my question is- Is there any option in MySQL to avoid it interpreting such special characters at the database level. If not what is there any api I can use to perform such a transformation. I did check out the MySqlHelper class but could not find anything useful there.

Thanks in advance,
Bharath

Answer 1

Starting with MySQL 5.0.1, you can set NO_BACKSLASH_ESCAPES to turn the backslash into yet another ordinary character. Be sure you never rely on a backslash somewhere else though!

To do this on a live server (until it reboots):

SET GLOBAL sql_mode='NO_BACKSLASH_ESCAPES';

Don't forget to add it to your MySQL configuration file (my.cnf or my.ini) so it takes effect on the next startup! Add the line

sql-mode=NO_BACKSLASH_ESCAPE