Monday, June 20, 2011

SQL Injection and the variable IN list

Pete Finnegan tweeted a link from a guy who discovered an SQL injection vulnerability on the website. Actually he discovered it over a year ago, and reported it. And still no-one has got around to fixing it yet.

It is an Oracle database (apparently - so they're not keeping up with patches either). And not only is there an SQL injection vulnerability, but if the SQL is invalid, it throws the whole statement out to the web-page as part of the error 'handling'.

The vulnerability is in a requirement which gets regular questions on stackoverflow. Specifically, given a string with a list of ids (such as '1,2,3,4'), how do I put that in an 'IN' list. 

The problem is thinking in specifics not abstracts. You need to make the mental jump and realize that you are have IN (:variable) not IN (:var1,:var2,:v3). 

So here's my recommendation for this. Firstly a function that will turn that CSV value into a collection. Here I use the built-in SYS.DBMS_DEBUG_VC2COLL, though it is tidier to create your own collection type.

create or replace function csv_to_list (i_list in varchar2) return sys.dbms_debug_vc2coll is
  v_list varchar2(4000) := i_list;
  v_ind number;
  t_list sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll ();
  v_ind := instr(v_list,',');
  while v_ind > 0 loop
    t_list(t_list.count) := substr(v_list,1,v_ind-1);
    v_list := substr(v_list,v_ind + 1);
    v_ind := instr(v_list,',');
  end loop;
  if v_list is not null then
    t_list(t_list.count) := v_list;
  end if;
  return t_list; 

Next, you take that collection, and turn it into a dataset with the TABLE operator. Finally, you use that as the source for a single-column subquery.

select table_name from user_tables 
where blocks in 
  (select column_value from table(csv_to_list('1,2,3,4,5')));

Then you can BIND the variable rather than concatenating it into a dynamically executed statement.

1 comment:

Maxim said...

Just for the record - in 11g there is a syntactic sugar for such things:

SQL> select * from xmltable('1,2,3,4,10');


Best regards