SQL Injection in HTML DB prevention

From: William B Ferguson <wbfergus_at_usgs.gov>
Date: Mon, 18 Apr 2005 11:30:12 -0600
Message-ID: <OF350349D1.08345C47-ON06256FE7.005FEFC4@usgs.gov>

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

Received on Mon Apr 18 2005 - 13:33:28 CDT