0

I am trying to select id's from a table by passing a string with multiple id's comma separated.

how would i query the below to allow passing multiple id's and using the prepare statement

set @string = ('1,2,3,4,5,6,7,10'); <- the string passed into the procedure

PREPARE stmt2 FROM 'SELECT id from customers where id in (?)';
SET @a = @string; 
EXECUTE stmt2 USING @a;
DEALLOCATE PREPARE stmt2;

currently returns only 1 row, which is the first value of @string, is it possible to either loop through the @string values and query my stmnt with them?

Lloyd_07
  • 55
  • 8
  • That will be bound as a singular string parameter. You need `?,?,?,?,...` and a number of arguments. Why is this as a stored procedure? Could it be done as application code? – tadman Jun 29 '20 at 22:41
  • I marked this as a duplicate of a very old question. That question was for DB2, not MySQL, but the same behavior exists in every SQL database. – Bill Karwin Jun 29 '20 at 22:44
  • thanks @tadman an unrelated question, preventing sql attacks to this, how would one go about running one, I'm trying to secure the input string before the query runs, would a regex allowing only digits and ',' be enough? – Lloyd_07 Jun 29 '20 at 23:05
  • If you're trying to prevent SQL injections with regular expressions you're already on the wrong track. The solution is placeholder values and a clear separation between query and data. If you do that no matter what junk people give you nothing bad will happen. You want a general-purpose pattern you can use in almost every situation, not specialized "fixes" for narrow use-cases. – tadman Jun 29 '20 at 23:06
  • ok so technically id have to use the following: SET @x = CONCAT('SELECT id FROM customers where id in ', '(', @string, ')'); – Lloyd_07 Jun 29 '20 at 23:28

0 Answers0