apostrophe confusion

[middletree]

Ray recently answered my question about apostrophe replacement with advice
on how to use 2 functions for hwich he gave me some code, called SafeIn and
Safeout

I'm having a hard time seeing the difference. In fact, the results are the
same.

Here's my old code:

Replace(Trim(Request.Form("InternalDesc")),"'","''")


and here is what Ray suggests:
SafeIn(Request.Form("InternalDesc"))

and
Function SafeIn(theString)
SafeIn = Replace(theString, "'", "''")
End Function


The results are the same: when I type in:
won't

One the page where it is displayed, it looks like:
won''t

 

[Ray at]

Yes, they both do the same thing. But you do NOT pass that value through
the function when you are RETREIVING a value from the database. Is that
what you're doing? Are you doing something like:

Response.Write SafeIn(Recordset("item"))

Ray at work

 

[Foo Man Chew]

I think he was just suggesting using a function to encapsulate the logic
instead of having to write out that replace() crap everywhere. He wasn't
trying to change the effect.

 

[middletree]

I am doing it when I do a request.form, before it goes into the database

 

[middletree]

Well, my original question is, why am I getting that effect? So any help to
that end would be appreciated

 

[Foo Man Chew]

Well, my original question is, why am I getting that effect?

WHAT ARE YOU TALKING ABOUT?

Okay, here's the deal. Strings passed to SQL statements are delimited by
apostrophes ('). So, if you have an apostrophe in a name, you need to have
some way of telling the SQL statement that you do *not* want to end the
string there. So, the common term is called "escaping" - you double-up the
apostrophe so it is escaped before passing to the database. No idea why
you're concerned how an *ESCAPED* value prints to the screen... it's escaped
for the database, not the user. And if that's not your concern, maybe you
could be more specific.

 

[Ray at]

Unless your request.form value actually has two apostrophes in it, you
shouldn't see two apostophes when you pull the value back out of the
database or look at it directly in the database. You aren't actually
inserting two into the database. '' = ' when inserted. I think we need to
see a sample of how this is happening to you.

Ray at home

 

[Foo Man Chew]

I think he's doing this:

value = replace(value, "'", "''")

and then wondering why there are two apostrophes when he later does this:

response.write value

 

[middletree]

I am not clear on why you aren't clear on why I am concerned with how things
appear on the screen.

The user types in

won't

When another page loads some data, and they see the word now displayed as

won''t

then, that is the problem I am trying to overcome.

 

[middletree]

yes.

 

[Bob Barrows]

I am not clear on why you aren't clear on why I am concerned with how
things appear on the screen.

The user types in

won't

When another page loads some data, and they see the word now
displayed as

won''t

then, that is the problem I am trying to overcome.

The issue is that you have not shown us how you process the value that you
receive from the database before response.writing it.

 

[middletree]

OK, I see the problem now. I was doing the replace both giong in and coming
out.

Sheesh!

My apologies for taking up everyone's time

or should that be everyone''s time

 

[Ray at]

Think of it this way.

x = "q"
x = replace(x, "q", "aksdjf;adsf")
response.write x

What will you get? You won't get "q."

But what about:

sSQL = "insert into something (col) values ('" & replace(x, "q",
"kajsdflkjasdf") & "')"
oADO.Execute sSQL
Response.Write x

Since you never changed the value of x, it will still be the original value.
You shouldn't ever change the value of a user input for the sake of dealing
with '.

Ray at home

 

[middletree]

makes sense. thanks

Think of it this way.

x = "q"
x = replace(x, "q", "aksdjf;adsf")
response.write x

What will you get? You won't get "q."

But what about:

sSQL = "insert into something (col) values ('" & replace(x, "q",
"kajsdflkjasdf") & "')"
oADO.Execute sSQL
Response.Write x

Since you never changed the value of x, it will still be the original value.
You shouldn't ever change the value of a user input for the sake of dealing
with '.

Ray at home

 

[middletree]

but you have to change it, before submitting it into the database, right?
Because with those apostrophes there, it won't ever make it into the
database.

 

[Ray at]

You have to make sure that your apostophes are handled, but you don't have
to change any values. You do:

sSQL = "insert into theTable (column) values ('" & SafeIn(theVar) & "')"

The value of theVar never changes. Does it matter? Sometimes yes,
sometimes no. But it's a pain if you change any values in your code,
because someone will later say "can you have the information e-mailed to me
also?" Then you have to alter your code to get rid of the doubled
apostrophes or rewrite your code or something.

Ray at home