Support of binding IN parameters for SQL queries

[ckitt]

Would like to ask if support for binding IN parameters for SQL queries be added to upcoming release for node-oracledb or not?

It's seem that support for binding array parameter in PL/SQL is added already, not sure if there's any particular reason (i.e. security reason) for not extending support on normal queries as well?

In my opinion constructing SQL statement in runtime is not a very good approach, it reduces the expressiveness of the application codes.

Thank you very much :)

Cheers!

[cjbj]

Dup of https://github.com/oracle/node-oracledb/issues/147. It's definitely on the wish list. Node-oracledb v2 is a step in the right direction since its new, underlying ODPI-C layer will help.

[ckitt]

thanks @cjbj , looking forward to have it support natively in node-oracledb, however, I would also want to know if there's any workaround for this? I feel like concatenating SQL string in runtime quite a bad development practices, might bring the problem of SQL injection. Thanks a lot~

[dmcghan]

@ckitt What are you trying to do? Maybe have a look at this for record types: https://jsao.io/2017/01/plsql-record-types-and-the-node-js-driver/

Or this for arrays in SQL, maybe look here: https://stackoverflow.com/questions/43327137/how-to-add-in-list-to-oracle-db-node-js The best solution for this will depend on a number of factors.

[cjbj]

Oh, do you mean the IN clause, not IN parameters? Next gen doc on that is here

[ckitt]

Thanks @cjbj , we have adopted the method you mentioned here.

It seem that we still got the security protection from SQL injection with bind parameter.

Our way for adopting the solution is creating a wrapper for connection.execute(), and expand the array parameters, modify the SQL statement as the same time.

• From

  SQL STATEMENT: SELECT * FROM STUDENTS WHERE STUDENTS_ID IN (:sId)
  Bind Parameters: { sId: [1, 3, 5] }

• Transform to

  SQL STATEMENT: SELECT * FROM STUDENTS WHERE STUDENTS_ID IN (:sId_0, :sId_1, :sId_2)
  Bind Parameters: { sId_0: 1, sId_1: 3, sId_2: 5] }

It is acceptable to me since the connection.execute interface have nothing change in the view of programmer, but I wonder if this will be supported natively by the library as well.

And at the same time, it seem that we cannot enjoy the benefit of statement caching by using this method.

Once again, thank you very much :+1: