Step by step storing textarea mysql php

Question

so i'm getting confused by all the topics about storing and displaying textareas with correct linebreaks and not allowing any HTML markup whatsoever.

Right now i am escaping the input, then storing it as a text and then trying to display it with echo nl2br($text); It still won't work though.

So how is it supposed to be handled so that the input is safe, it won't allow any HTML on display, how to display it correctly and so on?

This is what happens when i run my current code..
Step 1:
Textarea input:
ROW 1

ROW 3
ROW 4

ROW 6

Escaped variable :
$text = $mysqli->real_escape_string($_POST['textarea']);

Step 2:
SQL-query to insert into db. Stored in database as:
ROW 1\r\n\r\nROW 3\r\nROW 4\r\n\r\nROW 6

Step 3:
Fetch it with SQL, display with an echo nl2br($text); which results as
ROW 1\r\n\r\nROW 3\r\nROW 4\r\n\r\nROW 6

I guess that the way it is stored prohibits the usage of nl2br since there ain't really any newlines stored but only \r etc, i'm kinda lost at this one and it's getting late so...

Any guidance would be appreciated.

Answer 1

In your case - since you want to strip any markup from the input....

$text = strip_tags($_POST['textarea']);
$text = $mysqli->real_escape_string($text);
mysqli->query("INSERT INTO yourtable (content) VALUES ('$text')");

...but when you want to output it again to a browser - you STILL NEED TO escape it appropriately....

if ($result = $mysqli->use_result()) {
        while ($row = $result->fetch_assoc()) {
            print "<div>" . nl2br(htmlentities($row['content'])) . "</div>";
        }
}

The only time you apply any sanitization to data within PHP is at the point where it leaves PHP (going to a database, going to a browser, going to a log file....) and the method you use for transforming the data is dependant on where the data is going