Creating SQL-Injection proof dynamic where-clause from collection in PL/SQL

Question

I need to execute a query where the where-clause is generated based on user input. The input consists of 0 or more pairs of varchar2s.

For example:

[('firstname','John')
,('lastname','Smith')
,('street','somestreetname')]

This would translate into:

where (attrib = 'firstname' and value = 'John') 
and (attrib = 'lastname' and value = 'Smith')
and (attrib = 'street' and value = 'somestreetname')

This is not the actual data structure as there are several tables but for this example lets keep it simple and say the values are in 1 table. Also I know the parentheses are not necessary in this case but I put them there to make things clear.

What I do now is loop over them and concatinate them to the SQL string. I made a stored proc to generate this where-clause which might also not be very secure since I just concat to the original query.

Something like the following, where I try to get the ID's of the nodes that correspond with the requested parameters:

l_query := select DISTINCT n.id from node n, attribute_values av
where av.node_id = n.id ' || getWhereClause(p_params)

open l_rc
for l_query;
fetch l_rc bulk collect into l_Ids;
close l_rc;

But this is not secure so I'm looking for a way that can guaranty security and prevent SQL-Injection attacks from happening.

Does anyone have any idea on how this is done in a secure way? I would like to use bindings but I don't see how I can do this when you dont know the number of parameters.

DB: v12.1.0.2 (i think)

Answer 1

It's still a bit unclear and generalised, but assuming you have a schema-level collection type, something like:

create type t_attr_value_pair as object (attrib varchar2(30), value varchar2(30))
/
create type t_attr_value_pairs as table of t_attr_value_pair
/

then you can use the attribute/value pairs in the collection for the bind:

declare
  l_query varchar2(4000);
  l_rc sys_refcursor;
  type t_ids is table of number;
  l_ids t_ids;
  l_attr_value_pairs t_attr_value_pairs;

  -- this is as shown in the question; sounds like it isn't exactly how you have it
  p_params varchar2(4000) := q'^[('firstname','John')
,('lastname','Smith')
,('street','somestreetname')]^';

begin
  -- whatever mechanism you want to get the value pairs into a collection;
  -- this is just a quick hack to translate your example string
  select t_attr_value_pair(rtrim(ltrim(
     regexp_substr(replace(p_params, chr(10)), '(.*?)(,|$)', 1, (2 * level) - 1, null, 1),
     '[('''), ''''),
    rtrim(ltrim(
      regexp_substr(replace(p_params, chr(10)), '(.*?)(,|$)', 1, 2 * level, null, 1),
      ''''), ''')]'))
  bulk collect into l_attr_value_pairs
  from dual
  connect by level <= regexp_count(p_params, ',') / 2 + 1;

  l_query := 'select DISTINCT id from attribute_values
    where (attrib, value) in ((select attrib, value from table(:a)))';

  open l_rc for l_query using l_attr_value_pairs;
  fetch l_rc bulk collect into l_ids;
  close l_rc;

  for i in 1..l_ids.count loop
    dbms_output.put_line('id ' || l_ids(i));
  end loop;
end;
/

although it doesn't need to be dynamic with this approach:

...
begin
  -- whatever mechamism you want to get the value pairs into a collection
  ...

  select DISTINCT id
  bulk collect into l_ids
  from attribute_values
  where (attrib, value) in ((select attrib, value from table(l_attr_value_pairs)));

  for i in 1..l_ids.count loop
    dbms_output.put_line('id ' || l_ids(i));
  end loop;
end;
/

or with a join to the table collection expression:

  select DISTINCT av.id
  bulk collect into l_ids
  from table(l_attr_value_pairs) t
  join attribute_values av on av.attrib = t.attrib and av.value = t.value;

Other collection types will need different approaches.

---

Alternatively, you could still build up your where clause with one condition per attribute/value pair, while still making them bind variables - but you would need two levels of dynamic SQL, similar to this.