A bug in parameters for "IN"

[tgmazay]

version 2.2.5

sql : UPDATE table1 SET type = ? WHERE id IN (?) params: ["some_type","1,2,3"]

use connection.execute

type will be changed only for id=1

[sidorares]

this is how prepared statements work, in your example you probably want to use .query() or .execute("UPDATE table1 SET type = ? WHERE id IN (?, ?, ?)", ["some_type", 1, 2, 3])

[sidorares]

you can test this in standard command line client using PREPARE sql syntax, see examples at https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html#prepared-statement-types

[yohanrw]

Unfortunately, this issue is still there.

const sql = "SELECT * FROM seller_portfolio_item_images WHERE idseller_portfolio_item IN (?)";
      con.query(sql, [portfolioid_list], function (err, result) {
      console.log(sql);
        if (err) {
          console.log(err);
          var response = errorCodes.internal_server_error;
          callback(null, response);
        }
        else {
          var response = {
            "statusCode": 200,
            "headers": {
              "Content-Type": "application/json"
            },
            "body": JSON.stringify(result),
            "isBase64Encoded": false
          };
          callback(null, response)
        }
      });

If I do the following GET Request, i will get only get records that matched the very first value, in this example, 69.

http://127.0.0.1:3000/sportfolioimages/getbyportfoliolist?portfolioid_list=69,70,71

[sidorares]

@yohanrw there is a difference in how .execute() prepared statement parameters are used vs how query() builds sql on the client side. The issue is not unique to this driver, it's general difficulty of using variable length arguments operators/functions with prepared statements. PS need exact match parameter <-> value, and as a result you need variable number of ? parameters in the statement itself.

Good overview of possible solutions: https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives/10240302