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.
>
> --
> 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)
>

Hi Daniel.

As I read it, there were two examples given.

The first was this:
> >>>
> >>>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.
> >>>

which implies to me that their application is constructing the SQL insert statement dynamically. While I agree that you can translate or escape the data to ensure it does not break the SQL syntax rules I would recommend using a bind value in most cases. Hence my recommendation that the original poster look at Pete Finnigan's paper on SQL Injection.

The second example was inserting pieces of XML into a column. Yes, you are correct. Binding is not going to help here. However, as I read it, using TRANSLATE() is not going to work either as [I think that] what the original poster requires is to store the ampersand character in the column and then to translate it to the appropriate XML character entity (i.e. "&amp;") when creating the XML document.

So although I believe you solved the problem where the application generates an overflow error, I believe that they need to change the application to escape the text as valid XML at the point where it creates the XML document. This is something that I believe it is not currently doing. .

Cheers Chris Received on Fri Jan 30 2004 - 01:52:00 CST