1

I want the statement to search a number of Ids. Like so.

const idsStr = "41, 42, 43";
const sqlStr = `SELECT * FROM table where id IN (${idsStr})`;
session.sql(sqlStr).execute()

But if I use bind method, it only captures the first instance of the string, the remaining values are ignored.

const idsStr = "41, 42, 43";
const sqlStr = `SELECT * FROM table where id IN (?)`;
session.sql(sqlStr).bind(idsStr).execute()

I want to make prepared statement according to the API currently support so as to avoid SQL injection.

Kevin
  • 828
  • 11
  • 14
  • It looks like you're using MySQL and not Oracle, but maybe this would help anyway? https://oracle.github.io/node-oracledb/doc/api.html#-206-binding-multiple-values-to-a-sql-where-in-clause – Dan McGhan Mar 14 '20 at 16:46
  • You're right. I was referring to Oracle's MySql XDevAPI. "Oracle" tag was deleted. – Kevin Mar 14 '20 at 19:31

1 Answers1

4

This is a limitation of the API (and the X Plugin itself) and a byproduct of the fact that CRUD expressions support an alternative syntax such as IN [41, 42, 43]. Right now, the only way to do what you want is for the SQL statement itself to contain placeholders for all those ids:

const sqlStr = `SELECT * FROM table where id IN (?, ?, ?)
await session.sql(sqlStr).bind(41, 42, 43).execute()

Of course this does not work if you need a dynamic number of elements in the filtering criteria. In that case, you can resort to something like:

const ids = [41, 42, 43]
const sqlStr = `SELECT * FROM table where id IN (${ids.map(() => '?').join(',')})`
await session.sql(sqlStr).bind(ids).execute()

This is probably a bit convoluted but it's the smartest workaround I can think of at the moment.

In the meantime, maybe you can open a bug report at https://bugs.mysql.com/ using the Connector for Node.js category.

Disclaimer: I'm the lead dev of the MySQL X DevAPI Connector for Node.js

ruiquelhas
  • 1,905
  • 1
  • 17
  • 17