How do I solve this error: "SQL error: too many arguments"

Question

I have an API through Firebase Functions. When sending an request to the API with the body {"name":"1234"} with postman. I get the error 401 "SQL : Error: Too many arguments"

This is my function: index.js

const functions = require("firebase-functions");
const mysqlx = require("@mysql/xdevapi");

exports.getUser = functions.https.onRequest(async (req, res) => {
  const name = req.body.name;
  functions.logger.info("name: ", name);
  functions.logger.info("name type: ", typeof name);

  try {
    const session = await mysqlx.getSession({
      user: "user",
      password: "secretpassword",
      host: "hostname",
      schema: "database",
      port: 33060,
    });

    session.sql("USE database").execute().then(() => {
      const sql = "SELECT * FROM database.users WHERE user_name = ?";
      session.sql(sql).bind(1, name).execute()
          .then((result) => {
            res.status(200).send("User gathered: " + result);
          }).catch((err) => {
            functions.logger.info("sql statement: ", sql);
            functions.logger.error("err: ", err);
            res.status(401).send("SQL : " + err);
          });
    }).catch((err) => {
      functions.logger.error("err: ", err);
      res.status(402).send("Session Error:" + err);
    });
  } catch (error) {
    functions.logger.error("error: ", error);
    res.status(500)
        .send("An error occurred while fetching data from the database: " + error);
  }
});

I have tried binding the variable by setting user_name to both user_name = ? and user_name = :name. Setting user_name to ? when trying to bind resulted in a timeout in all cases except the one in my included codeblock. Setting user_name to :name resulted in all cases in the error specified in my question. I've tried binding using all these methods:

1. .bind("name": name)
2. .bind(name)
3. .bind([name])

My expected result is a response of the row where the user_name column matches the input name. From there I plan to extract data from the user row, of which code I have not included.

I have multiple loggers throughout the code, these are the logged values:

1. functions.logger.info("name: ", name); logs: name: 1234
2. functions.logger.info("name type: ", typeof name); logs: name type: string
3. functions.logger.info("sql statement: ", sql); logs: sql statement: SELECT * FROM database.users WHERE user_name = ?
4. functions.logger.error("err: ", err); logs:

err:  Error: Too many arguments
    at SqlResultHandler.BaseHandler.<computed> (/workspace/node_modules/@mysql/xdevapi/lib/Protocol/InboundHandlers/BaseHandler.js:121:17)
    at Array.entry (/workspace/node_modules/@mysql/xdevapi/lib/Protocol/InboundHandlers/BaseHandler.js:92:29)
    at WorkQueue.process (/workspace/node_modules/@mysql/xdevapi/lib/WorkQueue.js:77:19)
    at Client.handleServerMessage (/workspace/node_modules/@mysql/xdevapi/lib/Protocol/Client.js:169:21)
    at Client.handleNetworkFragment (/workspace/node_modules/@mysql/xdevapi/lib/Protocol/Client.js:213:14)
    at TLSSocket.<anonymous> (/workspace/node_modules/@mysql/xdevapi/lib/DevAPI/Connection.js:800:34)
    at TLSSocket.emit (node:events:513:28)
    at TLSSocket.emit (node:domain:552:15)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9) {
  info: {
    severity: 0,
    code: 5015,
    sqlState: 'HY000',
    msg: 'Too many arguments'
  }
}

Answer 1

The number of arguments exceeds the number of placeholders in the SQL statement. In your case, the statement specifies one placeholder and you provide two arguments via bind(1, name).

As described in the X DevAPI user guide, named placeholders are supported only for CRUD statements, whereas SQL statements only support ordinary placeholders, so, in this case, both bind(name) and bind([name]) should work.

Disclaimer: I'm the lead developer of the MySQL X DevAPI connector for Node.js