0

I am stuck with a problem and have been searching for a solution since 6 hours.

I am calling a stored procedure using PHP to fetch tasks of different users by passing a bunch of user IDs. I have the user IDs separated with ,.

$user_id_string = "59,49";  // The user IDs have been converted as string using implode function on an array.
$d = DB::select(DB::raw('call fetch_tasks("'.$user_id_string.'")'));

And the stored procedure has only one simple query like:

select firstname from users where id in (uids)

Here I have declared uids as varchar(255) as IN parameter.

PROBLEM

When I call the function, I get firstname of user id 59. If I hard code the values in SP, like (59,49) I am getting firstname of both the users.

EXPECTED

It should return firstname for both.

Please, please help my guys.

UPDATE 1:

Hard coded:

CREATE DEFINER=`root`@`localhost` PROCEDURE `fetch_tasks`(IN `cur_lat` VARCHAR(255), IN `cur_lng` VARCHAR(255), IN `uids` VARCHAR(255)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER SELECT id, firstname from users where id in (59,49)

By passing variable:

CREATE DEFINER=`root`@`localhost` PROCEDURE `fetch_tasks`(IN `cur_lat` VARCHAR(255), IN `cur_lng` VARCHAR(255), IN `uids` VARCHAR(255)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER SELECT id, firstname from users where id in (uids)
Himanshu Upadhyay
  • 6,558
  • 1
  • 20
  • 33
  • Suggest you output your SQL statements to text, both when you harcode and when you don't, they must be different, If that doesn't help, put them in your question for us please. – JeffUK Jul 12 '18 at 13:23
  • i advise you to do this in PHP and drop MySQL's stored procedure which makes it possible to protect against SQL injections.. using `PREPARE s FROM 'select firstname from users where id in (uids)'` to allow dynamic SQL in the stored procedure is hard to protect against SQL injections. – Raymond Nijland Jul 12 '18 at 13:32
  • Can you show body of procedure fetch_tasks() ? – RainDev Jul 12 '18 at 13:32
  • If the uids is declared as varchar, then each user id in $user_id_string should be quoted. $user_id_string = "'59','49'". Without quotes it will compare integers instead of strings – Fantastisk Jul 12 '18 at 13:35
  • Ok, @JeffUK, I will export them and will compare them. And also will post here. Give me few minutes. – Himanshu Upadhyay Jul 12 '18 at 13:43
  • Hey @JeffUK, I have posted two text in my updated question, which represents SPs, one with hardcoded values and one with variable. – Himanshu Upadhyay Jul 12 '18 at 13:57
  • The problem is that `...id in (59,49)` is not the same as `...id in (uids)` even if `uids` parameter contains `'59,49'`. The latter is translated as `... id in ('59,49')` – Shadow Jul 12 '18 at 15:45
  • Thank you @Shadow, I followed the answer with which you linked the question as duplicate – Himanshu Upadhyay Jul 13 '18 at 09:10
  • Glad to be of help :) – Shadow Jul 13 '18 at 10:09

1 Answers1

-1

You can store the ids in list then use foreach to get their firstnames like this : 

foreach (id in myIdList)
{
  firstname_user = GetFirstNameByQuery("SELECT firstname FROM users WHERE id = " + id);
  myStringList.Add(firstname_user);
}

take a look at this link : SQL WHERE ID IN (id1, id2, ..., idn)

Hamza Torjmen
  • 250
  • 1
  • 2
  • 10