Monthly Archives: May 2008

Oracle Wiki Experiment Failed

As an experiment, I placed some of the description and the pro/con discussion from my upcoming paper comparing Oracle development tools on the Oracle Wiki. And just like when I posted something not unambiguously positive about Oracle WebCenter on the Wiki, I was immediately flamed by an Oracle product manager, and any trace of negativity edited out of one of my pages.

I was hoping that the Oracle Wiki could be the place where the collective wisdom of the whole Oracle community could crystalize. It would be of great value to have a place where everyone could easily find well-argued pro and con for each tool, helping everyone choose the right tool. But the sensitity of Oracle product management to anything faintly negative on the pages for “their” product makes this hard to achieve.

SQL Injection Attack

I’ve read of SQL Injection as a way to hack a database, but had considered it fairly theoretical. However, during a code review the other day I found a real-life vulnerability.

The system contained a table with a column SEGMENT containing VARCHAR2 values like ‘SEGMENT1’, ‘SEGMENT2’, ‘SEGMENT7’, etc. The code built a cursor and looped through this table in order to build up a select string for EXECUTE IMMEDIATE:
FOR seg_rec IN seg_cur LOOP
lv_select := lv_select || seg_rec.SEGMENT || '||''-''||';
END LOOP;
lv_select := RTRIM (lv_select, '||''-''||');

This would build up a string like this:
SEGMENT2||'-'||SEGMENT4||'-'||SEGMENT7

It would then be executed like this:
lv_sql := 'select '
|| lv_select
|| ' from … where …';


EXECUTE IMMEDIATE lv_sql
INTO v_flex_value;

Looks fine. But what if a knowledgeable and malicious user managed
to insert something other than a column name into the configuration
table? Something like
''x'' from dual;drop database;select ''''

That would cause the EXECUTE IMMEDIATE to execute
select 'x' from dual;
drop database;
select ''||'-'||SEGMENT2||'-'||SEGMENT4||'-'||SEGMENT7 from …

Ooops!

Normally, you can prevent SQL Injection by simply using bind variables in
your SQL statement, but in this case, the variable part was the SELECT clause,
so this is not possible. But at least you should check that the variable part
does not contain semicolons…