Jump to content

MYSQL INSERT INTO - How to maintain line breaks


Recommended Posts

  • Administrators

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.

Link to post
Share on other sites
  • Administrators

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.

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

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

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

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...