Reformatting MySQL for a "textarea" box

[pault]

This is driving me mad. I've looked in my books and on the Internet but I cannot find the answer.

 

I have a form with a textarea and I need to retain the linebreaks. Obviously, before I send the data to MySQL, I run it through mysqli_real_escape_string and the result is:

(In form)

"Foo"

Bar

(In phpMyAdmin)

\"Foo\"\r\nBar

 

When I extract the data from MySQL, I use stripslashes($var) to get the quote back and end up with:

"Foo"rnBar

 

The problem I am having is changing the \r\n or rn to newlines so that it looks correct in the form. I have tried using nl2br before stripslashes. Even tried using str_replace to change the /r/n to <br /> but I just end up with

"Foo"<br />Bar

in the textarea box.

 

There must be some simple thing that's causing the problem or people wouldn't be using textareas. Help!

[harristweed]

Use addslashes and stripslashes on the text and mysql_real_escape_string on the field key?

http://es2.php.net/manual/en/function.mysql-real-escape-string.php

[pault]

Use addslashes and stripslashes on the text and mysql_real_escape_string on the field key?

http://es2.php.net/manual/en/function.mysql-real-escape-string.php

 

Surely addslashes and mysql_real_escape_string do the same thing? If I use stripslashes on the data from MySQL, I just end up with "Foo"rnBar. It doesn't seem to turn the \r\n into a newline.

[JD*]

Use nl2br on the insert/update to change your \r\n to <br>s. You can then do a str_replace on the way out if you want to replace your <br>s with \r\n

[DavidAM]

You don't use stripslashes on the stuff coming from the database. At least I don't, and I have never had to. I have never seen the backslashes on the quotes actually IN the database. Do you have magic_quotes_gpc and/or magic_quotes_runtime turned ON?

 

[*]If magic_quotes is on, you should stripslashes($Text) when you get the data from the form

  [ You should turn off magic_quotes as it has been depricated and will go away soon ]

[*]Use mysql_real_escape_string($Text) when putting the data into the database

[*]Use htmlspecialchars($Text) when sending the data back to the form to be edited

[*]Use nl2br(htmlspecialchars($Text)) when sending the data to the page to be displayed

[pault]

Thanks all for the advice.

 

One combination I've finally found that seems to work is not to use mysqli_real_escape_string at all. I use addslashes taking the data to the database and stripslashes on extraction. That changes quotes but ignores line breaks. I also use prepared statements to write to the database.

 

Would this combination be secure?

[harristweed]

Surely addslashes and mysql_real_escape_string do the same thing?

Oh? They don't then? Isn't your solution what I said two days ago?

[pault]

Surely addslashes and mysql_real_escape_string do the same thing?

Oh? They don't then? Isn't your solution what I said two days ago?

 

I thought they did but with experimentation found that addslashes leaves newlines alone and mysql_real_escape_string converts them. Your comments got me on the right track: it was the combination of addslashes AND mysql_real_escape_string which confused matters.

[trq]

Your data shouldn't have slashes in it once its in the database. If it does, your doing something wrong.

 

It would appear that you have magic_quotes enabled. In this case, you will need to use stripslahses before mysql_real_escape_string on the way IN to the database.

 

As it is at the moment, you are double escaping everything. Once automatically by magic_quotes & then again once (properly) manually yourself using mysql_real_escape_string.

[dio]

this may be helpful I wrote it for my own problems with forms -> database

 

this will only be the code to echo out the 'notes' field the inserting into db I assume is already covered

 

keep in mind this deals with tabs as well.

<?php
//values from form
$notes=$_POST['notes']; 

// call format_notes function to remove newline,tabs
format_notes($notes);

// define the format_notes function and receive variable
function format_notes($notes) {
  $tab="\t";
  $nbsp="     "; // each   = 1 space
  $no_tabs=str_replace($tab,$nbsp,$notes);
  $clean=nl2br($no_tabs);
  echo $clean;
}
?>

 

output

no indent line 1

    indented once line 2

          indented three times line 3

no indent

 

If I hadn't put the function together it would look like this:

 

no indent line 1 indented once line 2 indented three times line 3 no indent

I'm told this isn't the best way to go but for my own purposes right now it seems to be just fine