CODEWITHSUNDEEP
mysql
shantanuo
shantanuo

Reputation: 32276

escape single quotes

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.

Upvotes: 8

Views: 22989

Answers (4)

Jeff Davis
Jeff Davis

Reputation: 4797

Try this:

SELECT REPLACE( REPLACE( name , "'", "\\'" ) , '"', '\\"' )
FROM myescape

Upvotes: 2

Ghostpsalm
Ghostpsalm

Reputation: 538

Try this;

UPDATE myescape SET name = REPLACE(name, "'", "\\'");

You may want to think precisely about why you might want to do this (as Tomalak has said). Even in a stored procedure these fields should be strings, not commands.

Upvotes: 9

bobince
bobince

Reputation: 536715

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.

Upvotes: 19

Pramendra Gupta
Pramendra Gupta

Reputation: 14873

You can use char function.

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

Helpful link

Upvotes: 2

Related Questions