Error when inserting an '

[Michael Ralph]

I have a comment block on a form on my page. I want to
store the value of that form in a field in my database.
Everything works fine until the user types an apostrophe
in their comments. Such as "don't do this". This
appostrophe messes up my sql statement to insert the the
value in the database and causes an error. I am using a
stored procedure for the insert. Here is the script to
call the stored procedure:

Dim SurveyResults
SurveyResults = "AddSuperSurvey '"&Request("Name")
&"', '"&Request("Rank")&"', '"&_
Request("AFSC")&"', '"&Request("Phone")
&"', '"&Request("PASCODE")&"', '"&_
Request("Q1")&"', '"&Request("Q2")&"', '"&Request
("Q3")&"', '"&_
Request("Q4")&"', '"&Request("Q5")&"', '"&Request
("Q6")&"', '"&_
Request("Q6Other")&"', '"&Request("Q2Comment")
&"', '"&_
Request("Q3Comment")&"', '"&Request("Q4Comment")
&"'"
SurveyConnection.Execute(SurveyResults)

[Ray at]

Ah, the faq of faqs. [url]www.aspfaq.com[/url]
Search for "apostrophe"

Ray at work

"Michael Ralph" <ralph@141.com> wrote in message
news:01c401c38cf2$0d2be2a0$a301280a@phx.gbl...

> I have a comment block on a form on my page. I want to
> store the value of that form in a field in my database.
> Everything works fine until the user types an apostrophe
> in their comments. Such as "don't do this". This
> appostrophe messes up my sql statement to insert the the
> value in the database and causes an error. I am using a
> stored procedure for the insert. Here is the script to
> call the stored procedure:
>
> Dim SurveyResults
> SurveyResults = "AddSuperSurvey '"&Request("Name")
> &"', '"&Request("Rank")&"', '"&_
> Request("AFSC")&"', '"&Request("Phone")
> &"', '"&Request("PASCODE")&"', '"&_
> Request("Q1")&"', '"&Request("Q2")&"', '"&Request
> ("Q3")&"', '"&_
> Request("Q4")&"', '"&Request("Q5")&"', '"&Request
> ("Q6")&"', '"&_
> Request("Q6Other")&"', '"&Request("Q2Comment")
> &"', '"&_
> Request("Q3Comment")&"', '"&Request("Q4Comment")
> &"'"
> SurveyConnection.Execute(SurveyResults)
>

[Aaron Bertrand - MVP]

VFAQ.
[url]http://www.aspfaq.com/2035[/url]




"Michael Ralph" <ralph@141.com> wrote in message
news:01c401c38cf2$0d2be2a0$a301280a@phx.gbl...

> I have a comment block on a form on my page. I want to
> store the value of that form in a field in my database.
> Everything works fine until the user types an apostrophe
> in their comments. Such as "don't do this". This
> appostrophe messes up my sql statement to insert the the
> value in the database and causes an error. I am using a
> stored procedure for the insert. Here is the script to
> call the stored procedure:
>
> Dim SurveyResults
> SurveyResults = "AddSuperSurvey '"&Request("Name")
> &"', '"&Request("Rank")&"', '"&_
> Request("AFSC")&"', '"&Request("Phone")
> &"', '"&Request("PASCODE")&"', '"&_
> Request("Q1")&"', '"&Request("Q2")&"', '"&Request
> ("Q3")&"', '"&_
> Request("Q4")&"', '"&Request("Q5")&"', '"&Request
> ("Q6")&"', '"&_
> Request("Q6Other")&"', '"&Request("Q2Comment")
> &"', '"&_
> Request("Q3Comment")&"', '"&Request("Q4Comment")
> &"'"
> SurveyConnection.Execute(SurveyResults)
>

[Hannibal]

this has been my solution for a year now:

Basically I created a function to replace the request object


function getStr(val)
tmpStr = Trim(Request("val"))
tmpStr = Replace(tmpStr, "'", "`")
getStr = tmpStr
end function

You can obviously do more cleaning there if you want, like the Trimming i
did etc.


to call it:

Just replace "Request" with "getStr" wherever you use it on ur page...

Dim SurveyResults
SurveyResults = "AddSuperSurvey '"&getStr("Name")
&"', '"&getStr("Rank")&"', '"&_
getStr("AFSC")&"', '"&getStr("Phone")
&"', '"&getStr("PASCODE")&"', '"&_
getStr("Q1")&"', '"&getStr("Q2")&"', '"&getStr
("Q3")&"', '"&_
getStr("Q4")&"', '"&getStr("Q5")&"', '"&getStr
("Q6")&"', '"&_
getStr("Q6Other")&"', '"&getStr("Q2Comment")
&"', '"&_
getStr("Q3Comment")&"', '"&getStr("Q4Comment")
&"'"
SurveyConnection.Execute(SurveyResults)




"Michael Ralph" <ralph@141.com> wrote in message
news:01c401c38cf2$0d2be2a0$a301280a@phx.gbl...

> I have a comment block on a form on my page. I want to
> store the value of that form in a field in my database.
> Everything works fine until the user types an apostrophe
> in their comments. Such as "don't do this". This
> appostrophe messes up my sql statement to insert the the
> value in the database and causes an error. I am using a
> stored procedure for the insert. Here is the script to
> call the stored procedure:
>
> Dim SurveyResults
> SurveyResults = "AddSuperSurvey '"&Request("Name")
> &"', '"&Request("Rank")&"', '"&_
> Request("AFSC")&"', '"&Request("Phone")
> &"', '"&Request("PASCODE")&"', '"&_
> Request("Q1")&"', '"&Request("Q2")&"', '"&Request
> ("Q3")&"', '"&_
> Request("Q4")&"', '"&Request("Q5")&"', '"&Request
> ("Q6")&"', '"&_
> Request("Q6Other")&"', '"&Request("Q2Comment")
> &"', '"&_
> Request("Q3Comment")&"', '"&Request("Q4Comment")
> &"'"
> SurveyConnection.Execute(SurveyResults)
>

[Aaron Bertrand - MVP]

> tmpStr = Replace(tmpStr, "'", "`")

Well, this is an interesting idea, but some people might think that when
their data is sent back to them, funny, I didn't put a backwards apostrophe
in there. And if you just globally replace ` with ' on the way back out to
avoid this problem, you might inadvertently replace *intentional*
back-apostrophes.

Using some token character to represent a "problem" character is going to
lead to confusion somewhere down the line. Better to escape it, IMHO, then
the data goes in and comes out *as intended*...

A

[Hannibal]

it all also depends on how badly you want that apostrophe in there...

you could always just UN-replace( "`" to "'") it when you output the data
again... :o)

"Hannibal" <Dominique@webadstudio.com> wrote in message
news:OLYlXPPjDHA.1696@TK2MSFTNGP12.phx.gbl...

> this has been my solution for a year now:
>
> Basically I created a function to replace the request object
>
>
> function getStr(val)
> tmpStr = Trim(Request("val"))
> tmpStr = Replace(tmpStr, "'", "`")
> getStr = tmpStr
> end function
>
> You can obviously do more cleaning there if you want, like the Trimming i
> did etc.
>
>
> to call it:
>
> Just replace "Request" with "getStr" wherever you use it on ur page...
>
> Dim SurveyResults
> SurveyResults = "AddSuperSurvey '"&getStr("Name")
> &"', '"&getStr("Rank")&"', '"&_
> getStr("AFSC")&"', '"&getStr("Phone")
> &"', '"&getStr("PASCODE")&"', '"&_
> getStr("Q1")&"', '"&getStr("Q2")&"', '"&getStr
> ("Q3")&"', '"&_
> getStr("Q4")&"', '"&getStr("Q5")&"', '"&getStr
> ("Q6")&"', '"&_
> getStr("Q6Other")&"', '"&getStr("Q2Comment")
> &"', '"&_
> getStr("Q3Comment")&"', '"&getStr("Q4Comment")
> &"'"
> SurveyConnection.Execute(SurveyResults)
>
>
>
>
> "Michael Ralph" <ralph@141.com> wrote in message
> news:01c401c38cf2$0d2be2a0$a301280a@phx.gbl...

> > I have a comment block on a form on my page. I want to
> > store the value of that form in a field in my database.
> > Everything works fine until the user types an apostrophe
> > in their comments. Such as "don't do this". This
> > appostrophe messes up my sql statement to insert the the
> > value in the database and causes an error. I am using a
> > stored procedure for the insert. Here is the script to
> > call the stored procedure:
> >
> > Dim SurveyResults
> > SurveyResults = "AddSuperSurvey '"&Request("Name")
> > &"', '"&Request("Rank")&"', '"&_
> > Request("AFSC")&"', '"&Request("Phone")
> > &"', '"&Request("PASCODE")&"', '"&_
> > Request("Q1")&"', '"&Request("Q2")&"', '"&Request
> > ("Q3")&"', '"&_
> > Request("Q4")&"', '"&Request("Q5")&"', '"&Request
> > ("Q6")&"', '"&_
> > Request("Q6Other")&"', '"&Request("Q2Comment")
> > &"', '"&_
> > Request("Q3Comment")&"', '"&Request("Q4Comment")
> > &"'"
> > SurveyConnection.Execute(SurveyResults)
> >

>
>

[Ray at]

I personally wouldn't alter my users' data, well, except of course when
people enter something like "<javascript...." and try to be wise-asses.

Ray at work

"Hannibal" <Dominique@webadstudio.com> wrote in message
news:eTD3%23SPjDHA.3340@tk2msftngp13.phx.gbl...

> it all also depends on how badly you want that apostrophe in there...
>
> you could always just UN-replace( "`" to "'") it when you output the data
> again... :o)
>

[Aaron Bertrand - MVP]

> you could always just UN-replace( "`" to "'") it when you output the data

> again... :o)

Except if I wanted the data to look like this:

"hello `nuff said"

Now it looks like this:

"hello 'nuff said"

[Jeff Cochran]

On Tue, 7 Oct 2003 18:55:48 +0200, "Hannibal"
<Dominique@webadstudio.com> wrote:

>this has been my solution for a year now:
>
>Basically I created a function to replace the request object

>function getStr(val)
> tmpStr = Trim(Request("val"))
> tmpStr = Replace(tmpStr, "'", "`")
> getStr = tmpStr
>end function
>
>You can obviously do more cleaning there if you want, like the Trimming i
>did etc.

Thanks for the indirect help. :)

I do the same as a matter of course, also some checking for SQL
injection, that sort of thing. But I haven't ever TRIMmed the string,
and that's a good idea as well.

Jeff

[Jeff Cochran]

On Tue, 7 Oct 2003 13:00:44 -0400, "Aaron Bertrand - MVP"
<aaron@TRASHaspfaq.com> wrote:

>> tmpStr = Replace(tmpStr, "'", "`")

>
>Well, this is an interesting idea, but some people might think that when
>their data is sent back to them, funny, I didn't put a backwards apostrophe
>in there. And if you just globally replace ` with ' on the way back out to
>avoid this problem, you might inadvertently replace *intentional*
>back-apostrophes.
>
>Using some token character to represent a "problem" character is going to
>lead to confusion somewhere down the line. Better to escape it, IMHO, then
>the data goes in and comes out *as intended*...

I didn't see that in the original (didn't actually pay attention).
I've always been doing:

strTemp = replace(strTemp,"'","''")

It's hard to read, but it replaces a single quote with two single
quotes. So far I haven't had issues with that method, but I'm always
open to suggestions on cleaning data before it hits the database.

Jeff

[Aaron Bertrand - MVP]

Hannibal's suggestion was to replace ' with `

Personally, I don't think token values are very "safe"... either on
conversion or translation back, there's the potential to muck up the
content. Escaping is a much safer technique, and I always double up
single-quotes, like you show here...

> I didn't see that in the original (didn't actually pay attention).
> I've always been doing:
>
> strTemp = replace(strTemp,"'","''")
>
> It's hard to read, but it replaces a single quote with two single
> quotes. So far I haven't had issues with that method, but I'm always
> open to suggestions on cleaning data before it hits the database.