Prepared statement in an array and bind() for X DevAPI

Question

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.

It looks like you're using MySQL and not Oracle, but maybe this would help anyway? oracle.github.io/node-oracledb/doc/… — Dan McGhan
– Dan McGhan, Commented Mar 14, 2020 at 16:46
You're right. I was referring to Oracle's MySql XDevAPI. "Oracle" tag was deleted. — Kevin
– Kevin, Commented Mar 14, 2020 at 19:31

ruiquelhas · Accepted Answer · 2020-03-16 11:11:28Z

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

Collectives™ on Stack Overflow

Prepared statement in an array and bind() for X DevAPI

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related