Save textarea to MySQL and preserve line breaks

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

I want to preserve line breaks and print them later. I know I can do this with PHP's nl2br-function, but how do I protect this string against SQL-injection attacks (let's assume I can't use pre开发者_StackOverflow社区pared statements). If I use mysql_real_escape_string on it, it does not show me line breaks anymore.

$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 */

mysql_real_escape_string doesn't remove line breaks, it escapes them.

It should work okay to escape the string when storing it, and applying nl2br (possibly in combination with htmlspecialchars() to prevent users from entering raw HTML) when the data is output. That's the best way to go.

mysqli_real_escape_string will replace your carriage return with \r\n, hence you will need to get it back using str_replace(). However, it still won't be visible in your browser / email client. That's where nl2br() comes into play:

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

The code is tested, you can use it.

If I use mysql_real_escape_string on it, it does not show me line breaks anymore.

don't you see "\n" literals in place of line breaks?
if so, your code doing some nasty things, most likely you escape your data twice.
or, don't you do mysql_real_escape_string() after getting data from database?

Anyway, you've got to do some debugging - an investigation, to see what happens to your data on every stage. Just print out $_POST['textarea_name'], SQL query, etc.
To see the moment you lose your breaks and to find out an offender

Notes:
mysql_real_escape_string do not protect anything from any attack. It escapes delimiters.
nl2br do not preserve anything. It adds an HTML tag to line breaks.