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

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 29 Jan 2004 22:04:27 -0800
Message-ID: <1075442605.684654@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 & . 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)

Received on Fri Jan 30 2004 - 00:04:27 CST