Double mysql_real_escape_string insert of new line characters. How to get new line breaks back?

Question

Hi,

I have a textarea field that's inserted into a mysql database which may contain line breaks. The textarea data has accidentally gone through two lots of mysql_real_escape_string functions. Therefore the line-break bit of the field data is now stored as "\r\n" instead of an actual new line, as it's been double escaped?

Now when written back to the textarea html field for editing, "\r\n" is shown instead of a new line.

Question: How do I rectify the situation so these actually become new lines again?

Answer 1

You are probably do escaping using mysql_real_escape string twice - as input filter (wrong!) and as a SQL data preparation. You have to get rid of the first one.

Or once, as input filter, and then use prepared statements for the query. Get rid of the first one as well.

Answer 2

You should definitely fix the situation that is leading to the double escaping.

A quick fix for contents already processed that way is

str_replace("\\r\\n", "\r\n");

Answer 3

To get your original back, you have to do the reverse. Make a script that selects your existing rows from the table, and without escaping the input, update the column value. This, in essence, will store back in the single (correctly) escaped input.