Solved

Saving quoted text to DB

Posted on 2002-05-02
14
228 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:
RQuadling 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
 
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:RQuadling
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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:RQuadling
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now