How do I remove backslashes from records in MySQL

Question

Long story short, I have backslashes in my data due to PHP magic quotes. I have straightened out what was wrong there, but have some records now which have unnecessary backslashes.

1 | This is an example of \' single, \" double, and \\ backslash (in the messed up records)
2 | This example is how it gets stored now \ backslash ' single " double

When I want to output my data, I need to use strip slashes to fix the issue and get the following output:

1 | This is an example of ' single, " double, and \ backslash

But then this one is wrong:

2 | This example is how it gets stored now backslash ' single " double

Thus stripslashes in my php is removing a slash which isn't technically used to escape anything.

I want to not have to use strip slashes, since my data should be clean like this:

1 | This is an example of ' single, " double, and \ backslash (how data should be)

How can I query to replace \' and \" and \\ with ' " and \?

Answer 1

DROP TABLE IF EXISTS tmpTable;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable ( BadText VARCHAR(100));

INSERT INTO tmpTable
SELECT 'This is an example of \\\' single, \\\"double, and \\\\ backslash';

select BadText from tmpTable;

update tmpTable
set BadText = replace(replace(replace(BadText,'\\\'','\''),'\\\"','"'),'\\\\','\\');

select BadText as GoodText from tmpTable;

Answer 2

A REGEX_REPLACE would be the easiest for this since you can remove more than one slash and replace it with nothing. Unfortunately, there is no such thing in MySQL but there is a nice UDF for RegExReplace on Launchpad (need to compile it yourself but that shouldn't be a problem IMO).

So with the UDF installed, something like

SELECT REGEX_REPLACE(your_column, '.*backslash{2,}.*', 'backslash') FROM table 

Haven't tested it but it should work. It uses the same REGEX as already supported.

Answer 3

Since you are using PHP, you can use the stripslashes() function. They work with magic quotes too. Click here to see the PHP doc.

Answer 4

The question is how to you stop getting them. use JSON_MERGE(YOUR_JSON_STRING, JSON_OBJECT()) any time you do anything with a JSON variable.