数组中的准备语句和用于 X DevAPI 的 bind() [英] Prepared statement in an array and bind() for X DevAPI

问题描述

我希望语句搜索多个 ID.像这样.

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()

但是如果我使用 bind 方法，它只捕获字符串的第一个实例，其余的值将被忽略.

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()

我想根据当前支持的API做prepared statement，避免SQL注入.

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

推荐答案

这是 API(和 X 插件本身)的限制，也是 CRUD 表达式支持替代语法(例如 IN)的副产品[41, 42, 43].现在，做你想做的唯一方法是让 SQL 语句本身包含所有这些 id 的占位符:

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.

与此同时，也许您可​​以在 https://bugs.mysql.com/上打开错误报告a> 使用 Connector for Node.js 类别.

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

免责声明:我是 Node.js 的 MySQL X DevAPI 连接器的首席开发人员

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

这篇关于数组中的准备语句和用于 X DevAPI 的 bind()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！