ER_BAD_FIELD_ERROR: Unknown column 'john' in 'where clause'

[tgbv]

Version: 2.18.1

I receive the titled error while attempting to perform a prepared query containing WHERE ... IN (...)

let users = [ "john", "doe", "becky"]

await MysqlConnection.query(`
    SELECT *
    FROM users
    WHERE users.username IN (${  users.fill(0, users.length, '?').join(',')  });
`, users)

The exact SQL from exception is: \n\t\n\tSELECT *\n\tFROM users\n\tWHERE users.username IN (john,doe,becky);\n\t\t\t\t

... which leads me into thinking the escaping is not done correctly. It should be: \n\t\n\tSELECT *\n\tFROM users\n\tWHERE users.username IN ('john','doe','becky');\n\t\t\t\t

Is there a workaround for this?

[dougwilson]

Hi @tgbv I'm assuming you think users.fill(0, users.length, '?').join(',') returns the string ?,?,?, but it does not:

In other words, your statement

    SELECT *
    FROM users
    WHERE users.username IN (${  users.fill(0, users.length, '?').join(',')  });

Is actually

    SELECT *
    FROM users
    WHERE users.username IN (john,doe,becky);

and you have no ? placeholders in there at all. You can see this if you add in your code the following:

let users = [ "john", "doe", "becky"]

console.log(`
    SELECT *
    FROM users
    WHERE users.username IN (${  users.fill(0, users.length, '?').join(',')  });
`)

But you don't even need to form a list of placeholders for an array in this module. I'm not sure if you are using this module, though, as await is not usable here, so I don't want to provide an example that may not actually work for you. But you can probably see above, though, at least that the invalid string is actually being created by your code before the .query method gets it.

[tgbv]

This is embarrassing! 😂 Thank you for explanation

[dougwilson]

No problem at all. And don't feel embarrassed; we all have to start somewhere and if you hesitate to ask questions because you think it may be embarrassing, it may be harder to progress in your pursuit for knowledge.