Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Saving quoted text to DB

Posted on 2002-05-02
14
Medium Priority
?
242 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
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 400 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Suggested Courses

772 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