Saving quoted text to DB

Hi People,

I'm wondering how you do this.  I have a text area which people can write a comment in.  The problem is, if they write:

Today's a lovely day, isn't it

I get an error when I update the database because of the quotes (appostrophes) embedded in the text.

How do I let the text get saved without losing the quotes?

Also, if someone types:

    Hi there!

    How's it going?

How do I get the carriage returns to be replaced with breaks?  And, how can I convert the spaces to  ?

I'm sure there is a really simple way of doing all this, I just can't find it :)

Thanks for your help,

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Richard QuadlingConnect With a Mentor Senior Software DeveloperCommented:
1 - To store the quotes, use addslashes() on the string being stored. This is NOT on the entire SQL statement, just the data supplied.


$sText = addslashes($sText);

I would use this when I STORE the data.

2 - To convert the line breaks to <br> HTML Tags, use the function nl2br().


$sText = nl2br($sText);

I would use this when I about to DISPLAY the data.

3 - Converting spaces to &nbsp; is slightly odd. The reason I say that is if you do not force the input form to hardbreak the data on a line wrap, you will end up with 1 line of text that LOOKS like it is on many lines. If you then convert all the spaces to no-break, you will now force the line to be 1 line long.

I've forgotten what you need to add to the <input> tag to force the breaks at line wraps. Sorry.


Richard Quadling.

$value = " How's it going?  \n blbala";
$query = "update table1 set yourfield='$value'";

1)Rquadling is correct....
to self <<- Then why are u poking u'r nose??

Just wanted to add something...

If u want to put the text back into a text box....

U'll have to use stripslashes() otherwise u won't like the string that shows up in the text box.

So u will

addslashes($string1) <<- before putting in db

stripslashes($string1) --> after taking from db

Refer to the PHP manual for complete details...

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Stuart_JohnsonAuthor Commented:
Hi Richard,

Thanks for the info.  Unfortunately the AddSlashes function doesn't work.  If I have a line to text that says:

Yes it's a nice day

the output is

Yes it\\\'s a nice day

So I'm still getting the appostrophe.

The nl2br function worked a treat, so that's one step forward!  Cheers!

The reason I want to convert spaces to &nbsp; is because people are entering a comment in a text area, and this comment is displayed later in grid.  So, if they type in:

This is my points:
  1. Point 1
    a. Sub Point a
    b. Sub Point b  
  2. Point 2
    a. Sub Point a
    b. Sub Point b
  3. Point 3

I don't want it to look like:

This is my points:
 1. Point 1
 a. Sub Point a
 b. Sub Point b  
 2. Point 2
 a. Sub Point a
 b. Sub Point b
 3. Point 3

US111: The problem is the appostrophe.  If that's left in there, the query fails because it thinks the text has finished at that point, so any text after the appostrophe is considered illegal SQL.

annadatabhaskarsubramanyam:  Thanks' for pointing that out.  I'll definately do that.


Richard QuadlingSenior Software DeveloperCommented:
If the user types in

This isn't my text.

What do you get back in PHP.

Is it

This isn\'t my text.

If so, then the field has already been escaped. This is a PHP setting (can't remember top of the head which one it is).

You should be able to insert this straight into an SQL statement without further processing.

Can you try echoing the return field before you send it to the SQL and the entire SQL statement before executing it, just in case.

I am sure you could convert the leading spaces in a single statement, but I am not too sure.

Stuart_JohnsonAuthor Commented:

If I echo This isn't my text to the screen, I get This isn\'t my text.  I can't save this to the database for reasons I mentioned above.  

For example.  If I have a statement that says:

insert into messages
values (1, '20/05/2002', 'This isn\'t my text.')

it won't work because of the extra appostrophe.

I need to replace the appostrophe with it's URL equivellent.  Like when you have a space in your URL it replaces it with %20 (which in the case of an appostrophe it would be %27).  Can I do that without doing a String Replace?

Well I don't know if you understood expert comments.

You have a form, with a textarea called COMMENT. It contains : I'm explaining
You submit the form. By default PHP escapes the ' so the data looks like I\'m explaining.
Now you query:
$query = "insert into table (COMMENT) values ('$COMMENT')";

I'm explaining is inserted into the database and not I\'m explaining.

So use AddSlashes as RQuadling said.

Otherwise you could urlencode the data :
$query = "insert into table (COMMENT) values ('".urlencode($COMMENT)."')";
this may be of help to do with the returns and speach marks. i had a similar problem and solved it in this way
Stuart_JohnsonAuthor Commented:

I've figured out how to do it.  I do a str_replace and replace all the appostrophes with a "special" character string.  I then replace that with an appostrophe when displaying the string.

Thanks to all who helped.  I'll review all the comments and award the points to the person who provided some good info.

Stuart_JohnsonAuthor Commented:

Yes, I did understand what they were saying perfectly.  The problem is, unfortunately everything that they suggested didn't work.  

I'm using stored procedures to post my data back to the database (meaning I don't have to manually increment my keys, check to see if a posting already exists etc, it does it for me), so I don't call "insert" directly from the PHP script.

You correctly identified the problem I'm having (and I have said what you said).  PHP is changing the appostrophe from ' to \' which means I have a slash and an appostrophe.  An appostrophe is an illegal character when you're trying to post a string, as it designates the start and end of the string.  If you place an appostrophe mid way through a string, the database goes "what the ****?  This guy is mad! I can't do that!".  

So, let me do this one more time:

I have a string I'm inserting into my database.  The string is "It's a nice day".

When PHP gets the string, it changes it too : "It\'s a nice day".

I post that data back to SQL Server like this:

AddUserComment $userkey."," $date("d/m/Y, h:ma").", '".$comment."'";

So, if we convert this to an insert (without the variables), we get:

insert into usercomments
values (1, 09/05/2002, 'It\'s a nice day')

See the problem?  The first part is fine.  The userkey and comment date is perfect.  It's the comment that's causing the error.  SQL Server can post upto the \, but after that it's interpreting the last part of the string as more SQL.

And that's where the problem is.  The way I fixed it was to do this:

$comment = str_replace("'", "$*$", $HTTP_POST_VAR["USERCOMMENT"]);

then, when I read it back from the database, I do this:

$comment = str_replace("$*$", "'", $commentrow["COMMENT"]);

That works like a dream.

I'm quite happy to be told to do it another way, as I think this is bodgey.  I'm not familiar enough with PHP yet and the way I do it in Delphi is completely different (I'm a Delphi programmer, not a web programmer.  This is something I'm doing as a hobby).

Thanks to all for your help.  I hope I've clarified what's happened, and give you all a fully detailed explanation of what I'm trying to achieve.


Thanks Stuart Johnson  for your explanation
Now I understood the real problem
Stuart_JohnsonAuthor Commented:
Hi Richard,

I'm going to award the points to yo because your nl2br function solved one of the problems I had.  

I solved the spaces problem by using a while loop which works fine.

If anyone in the future can help me solve the problem with the quoted text, I'll be happy to open a new question with another 100 points.

us111: I thought I had made it pretty clear in each of my other posts what the problem was I was having :)

Thanks again to every one.

Richard QuadlingSenior Software DeveloperCommented:
It looks like your stored proc is re-pre-processing the data it receives.

The example you gave of ...

insert into usercomments
values (1, 09/05/2002, 'It\'s a nice day')

is EXACTLY what I would expect you to be sending to SQL.

The \ is an means that the next character is to be used as a normal character and NOT to be interpreted, in this case, as a string terminator.

What does your storedproc do?

Stuart_JohnsonAuthor Commented:
This is my stored proc:

  @Fullname Char(100),
  @ABANumber Char(10),
  @TELEPHONE Char(20),
  @Mobile Char(20),
  @Address1 Char(150),
  @Address2 Char(150),
  @Postcode Int,
  @Suburb Char(30),
  @EMAILADDRESS Char(150),
  @IDKEY Char(50)


  @ACTIVE Int,

set @ACTIVE=0

set @USERKEY = (select max(USERKEY) +1 from usernames)
if (@USERKEY = 0) or (@USERKEY is null)
  set @USERKEY = 1

insert into USERNAMES

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.