Node-oracledb: Cannot bind string list or array to in parameter

[tanepiper]

I have the following SQL code:

SELECT * FROM USER.EVENTS_VIEW WHERE EVENT_ID IN (:ids)

When I try do a query passing either a list of string IDs or as an array of strings, it just returns an empty results set. e.g.

{ids: '"12345", "12346", "12347"'} or {ids: ['12345', '12346', '12347']}

However if I do the following, it works fine:

var ids = params.ids.split(',').join('\',\''); var query = 'SELECT * FROM USER.EVENTS_VIEW WHERE EVENT_ID IN (\'' + ids + '\')';```

[cjbj]

Two things going on here, the first is that a bind in an IN will be treated as a single value by Oracle DB - this stops SQL injection. Second is that node-oracledb doesn't support binding arrays like that, not that this would help because of the previous point. You can use multiple bind vars, or insert the values into a lookup table. You might find this useful "Binding Multiple Values in an IN Clause" p169 in http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html

[tanepiper]

@cjbj :+1: thanks for the tip, will look into it

[dmcghan]

@tanepiper @cjbj's post got me thinking about a way you could do this "in memory". I may work for smaller things...

First, use a tool like SQL Developer or SQLPlus to create these 2 objects (type and function):

create or replace type vc2_ntt as table of varchar2(100);
/

create or replace function string_to_table(
   p_string    in varchar2,
   p_separator in varchar2 := ','
)

   return vc2_ntt

is

   l_elements apex_application_global.vc_arr2;
   l_retval   vc2_ntt := vc2_ntt();

begin

   l_elements := apex_util.string_to_table(p_string, p_separator);

   for x in 1 .. l_elements.count
   loop
      l_retval.extend();
      l_retval(l_retval.count) := l_elements(x);
   end loop;

   return l_retval;

end string_to_table;
/

Then you can do the following:

var oracledb = require('oracledb');

oracledb.getConnection(
    {
        user: 'hr',
        password: 'welcome',
        connectString: 'localhost/XE'
    }, 
    function(err, connection) {
        if (err) return next(err);

        connection.execute(
            'select employee_id, ' +
            '   first_name, ' +
            '   last_name, ' +
            '   phone_number, ' +
            '   hire_date ' +
            'from employees ' +
            'where department_id in ( ' +
            '   select column_value ' +
            '   from table(string_to_table(:depts)) ' +
            ')',
            {
               depts: '30,60,90'
            },
            {
                outFormat: oracledb.OBJECT
            },
            function(err, results) {
                if (err) {
                    console.log(err);
                    return;
                }

                connection.release(function(err) {
                    if (err) {
                       console.log(err);
                    }

                    console.log(results.rows);
                });
            }
        );
    }
);

[panuhorsmalahti]

If anyone is wondering how to bind to a non-fixed number of IN operands you can do it this way (which also prevents SQL injections):

        const names = ["name1", "name2", "name3"];
        connection.execute(`WHERE TABLE.NAME IN (${names.map((name, index) => `:${index}`).join(", ")})`,
        names)

[cjbj]

@panuhorsmalahti Thanks for sharing.

If performance is important (and if do you want to build up the statement dynamically), check if a simple loop is faster. Here's one variant:

binds = ['Christopher', 'Hazel', 'Samuel'];
sql = "select first_name, last_name from employees where first_name in (";
for (var i=0; i < binds.length; i++) sql += (i > 0) ? ", :" + i : ":" + i;
sql += ")";
console.log(sql);

[jpollard-cs]

@dmcghan I like @panuhorsmalahti's solution because it doesn't mean creating that string_to_table function, but can we assume your solution will be more performant because the query would not need to be re-parsed every time (if you're mapping bind parameters dynamically this means bind parameters could be different each time). Also I assume your solution preserves the security you get with binding (e.g. SQL injection), but would you mind verifying this (I'm assuming, although it's unescaping the list, putting it into a table as a varchar2 and selecting against that should have built-in protections against any arbitrary code execution?)

[dmcghan]

@jpollard-cs How many elements do you have in the array? Is there some upper limit?

One of our colleagues recommends a hard coded list of binds. Say the array generally has between 2 to 8 values, but never more than 10. In that case, you can use @panuhorsmalahti's or @cjbj's recommendation but use a hard-coded number of binds: 10. Then you have less than 10 elements in the array, you'd bind null for the other values and they'd be ignored. That's probably the most performant option.

The string_to_table option is better when you have a larger array of values. You can also combine the solutions: if array.length < 11 then "use in with fixed number of binds" else "use nested table".

Our colleague also said that if the list is huge, then you might consider a global temporary table over the nested table.

As to security, the values from the nested table approach are treated as binds so there's no risk of SQL injection.

[jpollard-cs]

@dmcghan my current use is for multi-dimensional filtering so sounds like I'll want to go with the string_to_table option, thanks!

[cjbj]

The same colleague later clarified the recommendation about 10. I asked if 10 was a magic number to the DB optimizer. The answer was:

No. It's simply a sensible upper bound for the task at hand. (For example, if you had a 6 item multi-select list, then 6 becomes the obvious cap etc)

[cjbj]

Here's some draft doc that may make it to the next version:

A common use case for a query IN clause is when a web user selects
multiple check-box options and the query should match all values.

Application data for a bind variable is always treated as pure data
and never as part of the SQL statement. Because of this, binding a
string containing a comma separated list of items to a single bind
variable will result in Oracle seeing only a single value: it
doesn't parse the string and won't extract the multiple values.

To use a fixed, small number of values in an IN bind clause, the SQL
query should have individual bind variables, for example:

sql = 'select * from foo where id in (:v1, :v2, :v3, :v4)';
binds = [30, 60, 90, 150];
connection.execute(sql, binds, function(...));

If you sometimes execute the query with a smaller number of items, a
null can be bound for the 'missing' values:

binds = [30, 60, 90, null];
connection.execute(sql, binds, function(...));

When the exact same statement text is re-executed many times
regardless of the number of user supplied values, you get
performance and scaling benefits from not having multiple, unique
statements being run.

Another solution when the number of data items is only known at
runtime is to build up an exact SQL string like:

binds = ['Christopher', 'Hazel', 'Samuel'];
sql = "select first_name, last_name from employees where first_name in (";
for (var i=0; i < binds.length; i++) sql += (i > 0) ? ", :" + i : ":" + i;
sql += ")";  // select first_name, last_name from employees where first_name in (:0, :1, :2)

This still uses binds for security. But, depending how often this
query is executed, and how changeable the number of bind values is,
you can end up with lots of 'unique' query strings being executed.
So you might not get statement caching benefits that executing a
fixed SQL statement would give.

An alternative is to construct a statement like:

SELECT ... WHERE col IN ( <something that returns a list of rows> )

The easiest way to do the
will depend on how the data is initially represented and the number
of items. You might look at using 'CONNECT BY' or nested tables.
Or, for really large numbers of items, you might prefer to use a
global temp table. Some solutions are given in
http://www.oracle.com/technetwork/issue-archive/2007/07-mar/o27asktom-084983.html
and
http://stackoverflow.com/questions/43327137/how-to-add-in-list-to-oracle-db-node-js

[d668]

what if I have commas in stings?

binds = ['Chri,stopher', 'Hazel', 'Samuel'];

[cjbj]

@intergleam check the links and choose a solution that works. The 'problem' is not specific to node-oracledb, so other Oracle forums will also have useful tips.