SQL Injection in HTML DB prevention

Hi all,

I just posted this over in the HTML DB discussion forum, but thought I'd
post over here as well, as many of you seem to be really up on this stuff.

I've searched all the forums and the asktom site, and I can't seem to find
the answer to THIS particular problem. It was a rough weekend though and I
got less than two hours of sleep last, so maybe I'm not comprehending
something fully. Hopefully I can explain this okay.

I'm creating pages for the users to enter (or select if applicable) filter
'keywords' from around 30 different tables, and I'm building a where
clause for the select statements.

Keeping it simple, and just using one of the free-form text fields, I'm
doing this:

:P400_NAME := regexp_replace(:P400_NAME, '[''''|"|;]');
-- strips out single and double-quotes and semi-colon
IF :P400_NAME is not null THEN
clause := clause||' and upper(name) LIKE '''||upper(:P400_NAME)||'''';
END IF;

then later in the same code:

IF substr(clause,1,8) = '1=1 and ' THEN
clause := substr(clause,9);
table_name = 'Deposits';
END IF;

:F121_DEPOSIT_WHERE_CLAUSE := clause;

Finally, to display the records, the code looks like

SELECT field1, field2, etc. from DEPOSITS
WHERE &F121_DEPOSIT_WHERE_CLAUSE.

Am I missing anything else that's obvious (or even not obvious)?

I 'think' that I just need to strip out punctuation characters that could
cause an error, but I do want to allow the '%' wildcard character.

Thanks guys,


------------------------------------------------------------
Bill Ferguson
U.S. Geological Survey - Minerals Information Team
PO Box 25046, MS-750
Denver, Colorado 80225
Voice (303)236-8747 ext. 321 Fax (303)236-4208
--
http://www.freelists.org/webpage/oracle-l

Other related posts: