mysql query escape single quote

Question

i have a problem with this following db::insert command. I want to use array in a mysql query "in ()" section, but the commas has been escaped. How can i solve this problem?

$users = [435,1671,429];
$list = implode("','", $users);

DB::insert("insert into ".
    "rejections (calendar_id, user_id, note, status) ".
    "select id as calendar_id, ?, concat(?), concat(?) ".
    "from calendar ".
    "where DATE(_date) BETWEEN ? AND ? and user_id in (?)", [$admin, $note, $status, $start_date, $end_date, $list]
);

mysql_log: 2019-05-30T16:28:51.221007Z 1815 Execute insert into rejections (calendar_id, user_id, note, status) select id as calendar_id, 2416, concat('Comment'), concat(1) from calendar where DATE(_date) BETWEEN '2019-05-01' AND '2019-05-31' and user_id in ('435\',\'1671\',\'429')

@RiggsFolly That's not the case here. The problem is - trying to pass an array as a single parameter. — Paul Spiegel, May 30 '19 at 17:16
`implode` will only add `','` between each number, but won't add `'` before the first number and after the last number. Still, have you tried to use `$users` directly instead of imploding? — VitorMM, May 30 '19 at 17:23

score 0 · Accepted Answer · answered May 30 '19 at 17:41

A fast (and dirty) fix could be to remove the single quotes in implode() and use FIND_IN_SET() instead of IN():

$users = [435,1671,429];
$list = implode(",", $users);

DB::insert("insert into ".
    "rejections (calendar_id, user_id, note, status) ".
    "select id as calendar_id, ?, concat(?), concat(?) ".
    "from calendar ".
    "where DATE(_date) BETWEEN ? AND ? and find_in_set(user_id, ?)"
, [$admin, $note, $status, $start_date, $end_date, $list]);

Note that it might be slower, because an index on user_id can't be used.

If you want to use IN() in your query, you will need a placeholder for each user:

IN (?,?,?)

and also bind every user ID

[$admin, $note, $status, $start_date, $end_date, $list[0], $list[1], $list[2]]

Since you probably don't know the number uf users in advance, you can use something like this:

$users = [435,1671,429];

$usersPlaceholders = array_map(function($id) { return '?'; }, $users);
$usersPlaceholders = implode(',', $usersPlaceholders);
$params = array_merge([$admin, $note, $status, $start_date, $end_date], $users);

DB::insert("
    insert into rejections (calendar_id, user_id, note, status)
    select id as calendar_id, ?, concat(?), concat(?)
    from calendar
    where DATE(_date) BETWEEN ? AND ? and user_id in ($usersPlaceholders)
", $params);

mysql query escape single quote

1 Answers1