Re: users using &, ", ', and other chars in input fields

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1075442605.684654_at_yasure>...
> Chris O wrote:
>
> > "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> > news:1075422191.224621_at_yasure...
> >
> >>Michael Hill wrote:
> >>
> >>
> >>>I have a general question about how people generally tend to deal with
> >>>users data that they enter.
> >>>
> >>>As an example users enter double quotes in a text field surrounding a
> >>>specific piece of text they want to hi-lite and then it barfs during the
> >>>oracle insert step because the string is not properly delimited.
> >>>
> >>>Another example is where the ampersand causes trouble when used on an
> >>>xml page so provisions are made to insert it into the table using the
> >>>ascii equavalent &amp; . But the field is only 25 characters so when a
> >>>string with 25 characters that has an ampersand is being input and we
> >>>change the ampersand to the ascii equavalent we now have more then 25
> >>>characters and update fails beacuse we have too many characters. We
> >>>could truncate them before the insert, or we could write some code to
> >>>deal with them onthe client.
> >>>
> >>>Others copy and paste from word documents into a text field and in it
> >>>there are hidden formatting fields like bullets.
> >>>
> >>>The users barf and complain about the application, but what we have here
> >>>is bad data.
> >>>
> >>>How do most handle these?
> >>>
> >>>Mike
> >>
> >>The TRANSLATE function.
> >>
> >>--
> >>Daniel Morgan
> >>http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> >>http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> >>damorgan_at_x.washington.edu
> >>(replace 'x' with a 'u' to reply)
> >>
> >
> > Daniel. I agreed but I would go further to suggest that the preferred
> > approach [depending on the tool] would be to not build the SQL dynamically
> > but rather to use a bind variable. Only as a last resort should the dynamic
> > SQL be used.
> >
> > Michael you might want to check out Pete Finnigan's articles on SQL
> > Injection: http://www.petefinnigan.com/orasec.htm
> >
> > Cheers Chris
>
> I see no evidence of dynamic SQL having anything to do with the OP's
> question. Perhaps this is my error but what I saw was:
>
> SQL> CREATE TABLE test (
> 2 testcol VARCHAR2(20));
>
> SQL> insert into test values (TRANSLATE('ABC&DEF', 'A&', 'A'));
>
> 1 row created.
>
> SQL> select * from test;
>
> TESTCOL
> --------------------
> ABCDEF
>
> SQL>
>
> Ampersand stripped out.

But that IS dynamic SQL! If the value typed by the user is visible in the SQL statement, dynamic SQL is being used. Received on Fri Jan 30 2004 - 06:11:41 CST