Jump to content
Nathan

MYSQL INSERT INTO - How to maintain line breaks

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by MrHappy

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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'] ) );

Share this post


Link to post
Share on other sites

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. :]

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×