Save text box in MySQL and save line breaks

Question

Imagine a blog system or cms (PHP and MySQL). I want the user to enter text in a text box and save it in the database. The type of field in the database is TEXT.

I want to save line breaks and print them later. I know that I can do this using the PHP nl2br function, but how to protect this string from SQL injection attacks (let's say I can't use prepared instructions). If I use mysql_real_escape_string on it, it will no longer show me line breaks.

 $text = 'one line another line'; $text = mysql_real_escape_string($text); /* save to db, fetch it some time later */ echo nl2br($text); /* output: one line\r\nanotherline */ 

Answer 1

mysql_real_escape_string does not remove line breaks, it eludes them.

It should work just fine to avoid a string when storing it and applying nl2br (possibly in combination with htmlspecialchars() so that users cannot enter raw HTML) when the data is output . This is the best way to go.

Answer 2

mysqli_real_escape_string will replace the carriage return with \r\n , so you will need to return it using str_replace() . However, it will not yet be visible in your browser / mail client. That nl2br() comes into play:

 $text = 'one line another line'; echo nl2br(str_replace("\\r\\n"," ", mysqli_real_escape_string($dbc, $text ))); 

The code is verified, you can use it.

Answer 3

If I use mysql_real_escape_string on it, it no longer shows me line breaks.

Can't you see "\n" literals instead of line breaks?
if so, your code does some unpleasant things, most likely you are avoiding the data twice.
or, you do not do mysql_real_escape_string () after retrieving data from the database?

In any case, you need to do some debugging - an investigation to find out what happens to your data at each stage . Just print out $ _POST ['textarea_name'], SQL query, etc.
To see the moment when you lose your breaks and recognize the criminal

Notes:
mysql_real_escape_string does not protect anything from any attack. This eludes the delimiters.
nl2br does not save anything. It adds an HTML tag to line breaks.