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…