SQL Injection and the variable IN list

Pete Finnegan tweeted a link from a guy who discovered an SQL injection vulnerability on the CNN.com 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 9.2.0.4 - 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 ();

begin

  v_ind := instr(v_list,',');

  while v_ind > 0 loop

    t_list.extend;

    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.extend;

    t_list(t_list.count) := v_list;

  end if;

  return t_list; 

end;

/

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.