SQL Injection in HTML DB prevention

  • From: William B Ferguson <wbfergus@xxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 18 Apr 2005 11:30:12 -0600

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)||'''';

then later in the same code:

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


Finally, to display the records, the code looks like

SELECT field1, field2, etc. from DEPOSITS

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

Other related posts: