how to prevent inputting quotation mark (") in a form?

Hi,

I made a form where visitors can introduce data.
I use this:
strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" &
dat & ...)"

My problem is that when someone introduces a quotation mark, i get a error
and the insert fails (e.g. nam'e).

How can i prevent that? Controling each entered character seems me to be a
very big work ...

Thanks
Kevin

Re: how to prevent inputting quotation mark (") in a form?

Kevin wrote:
> Hi,
>
> I made a form where visitors can introduce data.
> I use this:
> strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol &
> "',#" & dat & ...)"
>
> My problem is that when someone introduces a quotation mark, i get a
> error and the insert fails (e.g. nam'e).

Please don't ask a database-related question without telling us what
database you are using. :-)

This is yet another delimiter problem resulting from the use of dynamic sql.
See this for an explanation of how to handle embedded delimiters:
http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8

That post also goes into using stored procedures/saved queries. If you wish
to avoid using that efficient method, then you should use a Command object
to pass parameter values to a string containing parameter markers. See:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Neither technique relieves you of the responsibility of validating user
inputs in your server-side code, if only to prevent errors caused by
incorrectly entered data.

>
> How can i prevent that?

Don't try to prevent the entry of what may be valid data (it can only be
done with client-side code which is outside the topic of this newsgroup -
see microsoft.public.scripting.jscript if you wish to persist with this).
Use parameters so embedded delimiters do not matter. Another benefit of
using parameters is it makes your application sql injection-proof:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf

> Controling each entered character seems me to
> be a very big work ...

Yes.

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to prevent inputting quotation mark (") in a form?

thanks


"Bob Barrows [MVP]" wrote in message
news:OYyekKbIGHA.3816@TK2MSFTNGP12.phx.gbl...
> Kevin wrote:
> > Hi,
> >
> > I made a form where visitors can introduce data.
> > I use this:
> > strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol &
> > "',#" & dat & ...)"
> >
> > My problem is that when someone introduces a quotation mark, i get a
> > error and the insert fails (e.g. nam'e).
>
> Please don't ask a database-related question without telling us what
> database you are using. :-)
>
> This is yet another delimiter problem resulting from the use of dynamic
sql.
> See this for an explanation of how to handle embedded delimiters:
>
http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
>
> That post also goes into using stored procedures/saved queries. If you
wish
> to avoid using that efficient method, then you should use a Command object
> to pass parameter values to a string containing parameter markers. See:
>
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> Neither technique relieves you of the responsibility of validating user
> inputs in your server-side code, if only to prevent errors caused by
> incorrectly entered data.
>
> >
> > How can i prevent that?
>
> Don't try to prevent the entry of what may be valid data (it can only be
> done with client-side code which is outside the topic of this newsgroup -
> see microsoft.public.scripting.jscript if you wish to persist with this).
> Use parameters so embedded delimiters do not matter. Another benefit of
> using parameters is it makes your application sql injection-proof:
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
>
> > Controling each entered character seems me to
> > be a very big work ...
>
> Yes.
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>