escape single quotes

Question

I have a table like this...

select * from myescape;
+-----------+
| name      |
+-----------+
| shantanu' |
| kumar's   |
+-----------+
2 rows in set (0.00 sec)

I need to replace the single quote ' with \'

I will also need to escape double quotes and backslash.

Answer 1

You can use char function.

mysql> SELECT QUOTE('Don\'t!');
        -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);

Helpful link

• http://it.toolbox.com/wiki/index.php/How_do_I_escape_single_quotes_in_SQL_queries%3F
• http://www.webproworld.com/webmaster-forum/threads/61969-single-quote-in-mysql-record

Answer 2

The point of prepared statements is that you don't have to include content in them. Use a PREPARE query with ? placeholders and then EXECUTE ... USING to pass the values in without having to escape them.

Don't try to do escaping yourself, because you're likely to make mistakes. Depending on what encoding you're using, there can be more to it than just backslash-escaping quotes, backslash and null.