Solved

Saving quoted text to DB

Posted on 2002-05-02
14
237 Views
Last Modified: 2006-11-17
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,

Stu.
0
Comment
Question by:Stuart_Johnson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 100 total points
ID: 6986512
1 - To store the quotes, use addslashes() on the string being stored. This is NOT on the entire SQL statement, just the data supplied.

e.g.

$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().

e.g.

$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.

Regards,

Richard Quadling.
0
 
LVL 8

Expert Comment

by:us111
ID: 6986608
try

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


0
 

Expert Comment

by:annadatabhaskarsubramanyam
ID: 6986917
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...

Thanx...
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6987910
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.

Cheers,

Stu
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 6988151
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.

Richard.
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6990667
Richard,

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
  (USERKEY, POSTDATE, MESSAGE)
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?

Stu
0
 
LVL 8

Expert Comment

by:us111
ID: 6993257
Well I don't know if you understood expert comments.

Example:
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)."')";
0
 

Expert Comment

by:keano84
ID: 6996012
this may be of help to do with the returns and speach marks. i had a similar problem and solved it in this way


http://www.php.net/manual/en/function.htmlspecialchars.php
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6996088
Hi,

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.

Stu
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 6998919
us111,

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
(USERKEY, COMMENTDATE, COMMENT)
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.

Cheers,

Stuart.
0
 
LVL 8

Expert Comment

by:us111
ID: 7005432
Thanks Stuart Johnson  for your explanation
Now I understood the real problem
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 7005663
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.


Stuart.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 7005680
It looks like your stored proc is re-pre-processing the data it receives.

The example you gave of ...

insert into usercomments
(USERKEY, COMMENTDATE, COMMENT)
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?

Richard.
0
 
LVL 6

Author Comment

by:Stuart_Johnson
ID: 7008301
This is my stored proc:

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

as

declare
  @USERKEY Int,
  @ACTIVE Int,
  @ADMINISTRATOR Int

set @ADMINISTRATOR=0
set @ACTIVE=0

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

insert into USERNAMES
  (USERKEY, FULLNAME, ABANUMBER, GRADINGKEY, TELEPHONE, MOBILE, ADDRESS1, ADDRESS2, POSTCODE, SUBURB, ACTIVE, ADMINISTRATOR, EMAILADDRESS, IDKEY)
  values (@USERKEY, @FULLNAME, @ABANUMBER, @GRADINGKEY,@TELEPHONE,  @MOBILE, @ADDRESS1, @ADDRESS2, @POSTCODE, @SUBURB, @ACTIVE, @ADMINISTRATOR, @EMAILADDRESS, @IDKEY)
GO


Stu
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question