How should i store newlines in a MySQL database?

Question

I'm going around and around in circles in my mind trying to decide how best I should store newlines in a MySQL database.

A bit of background; the user is asked to complete a textarea. This has to stored in a MySQL database before being read back out and the content included in an HTML email.

Should I store them as:

1) String literal - surely this is dangerous and bad practice

2) As a string with \r\n in - when I read this back out of the database its read as 4 characters so nl2br() fails to correctly replace them.

3) As HTML <br /> - as it has to be html entity encoded to be stored it ends up being stored as <br /> so when it gets to the email <br /> is printed rather than an actual newline. Passing it through html_entity_decode() would decode other characters that need to be encoded.

Any help grately appreciated.

Answer 1

Store it as it is but escape first. This is your option 1. When you need to present this data apply whatever function you need to format it. If you need to show it in HTML use nl2br() and htmlentities() functions. That'll work for mail also.

If you have a text area data like this,

$text="Hello Sir,
I am Robert'); Drop table students; --
.....
Yours most obedient pupil
.....";

Just store it as it is after you escape it, or use a prepared statement.

$text = $mysqli->real_escape_string($text);

Answer 2

use the javascript string replace function str.replace(/\n/g,'\\n')

to transform all newlines to escaped literals in the textarea while forming the url or post string.

Retrieving the same using PHP's json_encode() and displaying it through a textarea works well for me.