how to select rows where a specific string column matches at least one value inside a json array?-CodePudding

SELECT name FROM  accounts WHERE Name in ("name1","name2");

the values are being sent inside a json array

["name1","name2"]

currently i just convert the array into json string and remove the first and last characters

"name1","name2"

but could i just keep the array intact? i tried json_contains

SELECT name FROM  accounts WHERE JSON_CONTAINS(name,'["name1","name2"]');

my understanding as to which why that didn't work is because name column isn't json string array

CodePudding user response：

Core issue is you can't "prepare" flex statements without some sort of preprocessing.

Generally you'll want to do input validation etc, on the application side regardless, and then use some sort of pre-constructor if you're not using an ORM.

ie:

$values = ["name1", "name2"];

// Validation should happen here

$inputs = substr(str_repeat("?,", count($values)), 0, -1);
$bind   = str_repeat("s", count($values));
$sqli   = "SELECT name FROM  accounts WHERE Name in ($inputs);";
...
$stmt->bind_param($bind, ...$values);
...

You can use the same principal for PDO as well, but regaredless you're gunna want to handle the validation layer on the application side, and there is no "easy" way to inject "IN" statements into prepared SQL.