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)

Have a look at [`DBMS_ASSERT`](https://oracle-base.com/articles/10g/dbms_assert_10gR2). — William Robertson, Aug 17 '18 at 09:50
You can use collections, but exactly how depends on your real query - your example doesn't make sense as no row will match all the contradictory conditions. I realise it's made-up, but still... are you looking for any ID that matches any of the attr/value pairs? Also how are you receiving the parameters - is your example supposed to be JSON? — Alex Poole, Aug 17 '18 at 11:49
Basically, I have nodes (of which I collect the id's) which are linked to attributes and values. So I collect all ID's which have those parameters. My parameters are passed by Soap so XML. I parse them and put them in a collection before creating the query. Hope this clarifies things. — Kenny Steegmans, Aug 17 '18 at 11:55
I was commenting more on the 'would translate into' part, which `and`s mutually exclusive conditions. I assume you really want to `or` those, so finding IDs that match any attr/value pair? — Alex Poole, Aug 17 '18 at 13:11

Alex Poole · Accepted Answer · 2018-08-17T14:58:04.660

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.

Wow, thanks man. Didn't know half these things were even possible. I've been searching the net for solutions but this is my first time seeing solutions like these. I will research them all and see if they are usable in my special case. — Kenny Steegmans, Aug 20 '18 at 12:00

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

1 Answers1