INSERT INTO - How to maintain line breaks

[Nathan]

I'm inserting into my MYSQL database via a PHP page. For some reason it keeps losing the formatting and line breaks.

 

What do I need to change to retain these? It's inserting into a MEDIUMTEXT field.

 

Current Code:

 

mysql_query("INSERT INTO projects (UserID, Title, Description, CreateDate)
VALUES ('$userid', '$ProjectName', '$ProjectDescription', '$Date')");

 

This data is coming from the page before where the user enters the data in a <textarea> field from a html form.

[Nathan]

Blah, got it finally

 

My normal code accepting the data from the form in the next page was:

$ProjectDescription = $_POST['ProjectDescription']

 

It had to be modified to

$ProjectDescription = nl2br(htmlentities($_POST['ProjectDescription'], ENT_QUOTES, 'UTF-8'));

 

$_POST['ProjectDescription'] is the text provided by either the form or textarea. $ProjectDescription is the returned text from nl2br and htmlentities, to be stored in your database. ENT_QUOTES will convert both double and single quotes, so you will have no trouble with those.

[MrHappy]

Blah, got it finally

 

My normal code accepting the data from the form in the next page was:

$ProjectDescription = $_POST['ProjectDescription']

 

It had to be modified to

$ProjectDescription = nl2br(htmlentities($_POST['ProjectDescription'], ENT_QUOTES, 'UTF-8'));

 

$_POST['ProjectDescription'] is the text provided by either the form or textarea. $ProjectDescription is the returned text from nl2br and htmlentities, to be stored in your database. ENT_QUOTES will convert both double and single quotes, so you will have no trouble with those.

 

You still have some security issues with that, I always suggest to sanitize all input data with mysql_real_escape_string.

Now to prevent XSS injection (JavaScript mainly,) when displaying any MySQL data that can be user-edited, use htmlspecialchars which will replace html characters with a display-safe value.

 

However as far as your line breaks go you have the right idea, however it would be better to do the parse when dropping the data out of the database, that way you can control when/where it is occurring.

Edited June 2, 2012 by MrHappy

[Nathan]

Ok, how would I do this? I assume it wouldn't be done on the form page but onthis page that inserts the data, the above code is where the text is passed to.

[MrHappy]

Ok, how would I do this? I assume it wouldn't be done on the form page but onthis page that inserts the data, the above code is where the text is passed to.

 

I would suggest doing it where you are converting the $_POST/$_GET/$_COOKIE into the variable.

EG;

 

$ProjectDescription = nl2br ( mysql_real_escape_string ( $_POST['ProjectDescription'] ) );

[Nathan]

And the helps make it more secure by doing what?

[MrHappy]

And the helps make it more secure by doing what?

 

It will fully sanitize the input to prevent mysql injection, essentially what it does is compile the variable as if it was being sent into the database, and then sanitizes it. This prevents any really clever hacker from using methods like CHAR(32) (<- Tells MYSQL to generate a double quote, which is not parsed by addslashes and other SQL sanitation methods.) to do real SQL injection. :]

[Marc]

In short meaning you could essencially enter another sql statement after it such as delete from .... Never fun

 

Sent from my GT-I9100 using Tapatalk 2

[webdevuser]

I would suggest doing it where you are converting the $_POST/$_GET/$_COOKIE into the variable.

EG;

 

$ProjectDescription = nl2br ( mysql_real_escape_string ( $_POST['ProjectDescription'] ) );

 

Even what MrHappy said is not considered to be secure anymore.

 

In php5.5 all mysql_* functions will be mark as deprecated (which means their will be no new updates of these functions and will be deleted in php 5.6)

 

It is recommended to use either PDO (Very secure) or mysqli_* (Which is lighter and faster than mysql_* functions)