Why Does "nl2Br" Applied To Text Retrieved From A Db Field Not Work ?

[phdphd]

Hi All,

I am facing an issue I cannot solve and I just do not understand why.

In a db table, I've got a field that contains text coming from a textarea in a form. If the user had entered breaklines to divide their text into paragraphs, the breaklines are replaced with "\r\n" strings, so the text in the table field may look like "Hello\r\nHow are you?". This works.

Now, assuming that the user wishes to edit that text, I want to retreive that text into a textarea, with the same paragraph layout as that initially used by the user.

Here is my code (without the whole db error checking code) :

 

$link = @mysql_connect('localhost', 'root', '');
mysql_select_db('mydb', $link);
$sql = 'SELECT * FROM table_name Where field_id="31"';
$rs = mysql_query($sql, $link);
$arr = mysql_fetch_array($rs);

$txt =$arr['user_input'];
$txt = nl2br($txt);

echo '<br />retrieved text from db is ' .$txt.'<br />';//Will display "retrieved text from db is Hello\r\nHow are you?"

$txt=str_replace("<br />", "", $txt);

echo '<br /><textarea name="text" cols="100" rows="15" >'.$txt.'</textarea>';// Will display "Hello\r\nHow are you?" in the textarea

 

Now if manually set a variable called "Hello\r\nHow are you?". The following code

$string = "Hello\r\nHow are you?";
$string=nl2br($string);

$string=str_replace("<br />", "", $string);
echo '<br /><textarea name="text2" cols="100" rows="15" >'.$string.'</textarea>';

 

Will generate

"Hello

How are you?"

in the textarea.

 

Why can't I manage to make this work with the text retreived from the database ?

 

Thanks for your help.

 

Phd

[Barand]

Put test into textareas "as is" from the database, complete with "\r\n".

 

You only need nl2br() when writing HTML body text.

[phdphd]

Thanks for your reply. However, for "cosmetics" reasons, I want the text to appear as the user initially wrote it, with the same layout, and without "\r\n". If it works from a manually set variable, then there is no reason why it should not from a text retreived from a database.

[kicken]

$string=nl2br($string);

$string=str_replace("<br />", "", $string);

 

That code is entirely pointless.  You're converting "Hello\r\nHow are you?"; into "Hello<br />\r\nHow are you?"; using the first line (nl2br) and then back to "Hello\r\nHow are you?"; using the second line (str_replace).  Just remove both lines and leave $string as it originally was, same effect.

 

...I want the text to appear as the user initially wrote it, with the same layout

 

A textarea element will properly understand and display text with the new line characters ("\r\n").  So when put into a text area the string "Hello\r\nHow are you?"; will be displayed as:

Hello

How are you?

 

You only need to add the br tags using nl2br when you are displaying the text inside some other HTML element like a paragraph or div tag.

[Barand]

A textarea element will properly understand and display text with the new line characters ("\r\n"). So when put into a text area the string "Hello\r\nHow are you?"; will be displayed as:

Hello

How are you?

 

You only need to add the br tags using nl2br when you are displaying the text inside some other HTML element like a paragraph or div tag.

 

Perhaps if enough people tell him this...

[Christian F.]

I might be misunderstanding something here, but is the "\r\n" an actual part of the text that appears in the textarea? If so, then you have double-escaped the data when going into the database. Not only does this create the issue of newlines appearing as actual content, but it will also make your output escaping pretty much worthless as you're escaping the slashes that's supposed to escape the harmful characters.

 

In short, if this is indeed the case then you have a two-fold problem:

1. You need to fix your SQL output escaping routines, to only escape once (using the proper methods).
   
2. You need to un-escape all of the previously saved, and now invalid, data in your database.
   

 

Should I be wrong in my suspicions, then just ignore my post.

[phdphd]

Well, Christian, you must be right somewhere. I was just writing the following when you sent your post

I noticed in my session variables that before sending data to db, linebreaks appear as "\\r\\n". I think this is due to a mysql_real_escape_string processing in my code.

So I solved the issue using the statement :

$txt = str_replace("\\r\\n", "\r\n", $txt);

now text appears correctly laid out in the textarea.

Thanks to all of you.

[Christian F.]

That's unfortunately just a workaround. It fixes the symptoms of the problem, but not the problem itself. To fix the actual problem you have to do the steps I outlines above, otherwise your code will be vulnerable!

 

However, without seeing your code I cannot tell you exactly where the problem lies, so you'll have to go through it line by line yourself. Try to translate each line to plain English, and verify your assumptions with the PHP manual, and the problem should become quite apparent.

If you need further help, you'll have to post the code that inserts and retrieves the data from the database.

[Barand]

I noticed in my session variables that before sending data to db, linebreaks appear as "\\r\\n". I think this is due to a mysql_real_escape_string processing in my code.

 

The usual cause of double-escaped text is have "magic_quotes" turned on in your ini file. This automatically adds slashes to posted data. Then when you use mysql_real_escape_string() you then escape the added slashes.

 

Check if it is ON by using "get_magic_quotes_gpc()" and use "stripslashes()" to remove them before using mysql_real_escape_string().

[phdphd]

Hi All,

After reading over your answers, I got to the following solution, that tests both export to db and import from db. Hope it helps other newbies like me .

 

if (isset($_POST['user_input']))
{
$user_input=$_POST['user_input'];
$link = @mysql_connect('localhost', 'root', '');
$user_input=mysql_real_escape_string ($user_input);
mysql_select_db('textareachl', $link);
$insert_textarea_into_db_field='INSERT INTO `textareachl`.`textarea` (`text`) VALUES (\''.$user_input.'\')';
$rs = mysql_query($insert_textarea_into_db_field, $link);
$sql = 'SELECT * FROM textarea Where text="'.$user_input.'"';
$rs = mysql_query($sql, $link);
$arr = mysql_fetch_array($rs);
$txt =$arr['text'];
echo '<br />Retreiving from DB into textarea ';
echo '<br /><textarea name="text" cols="100" rows="15" >'.$txt.'</textarea>';
}

 

Thanks again to all of you.

Edited October 29, 2012 by phdphd