sql - In MySQL how to pass a list of integers to a prepared statement in a stored procedure? -

mysql 5.0

create table `locationcodes` (   `id` int,   `customer` varchar(100),   `locationcode` varchar(50),   `parentid` int );  insert locationcodes values (1, 'test, inc.', 'california', null); insert locationcodes values (2, 'test, inc.', 'los angeles', 1); insert locationcodes values (3, 'test, inc.', 'san francisco', 1); insert locationcodes values (4, 'test, inc.', 'sacramento', 1); 

stored procedure:

create procedure test (_location varchar(100)) begin  set @location = _location;  set @select='   select id, locationcode   locationcodes     customer="test, inc."   , id in (?)   ';  prepare stmt @select; execute stmt using @location; deallocate prepare stmt;  end 

call procedure:

call test('2, 3') 

result:

2, los angeles 

the result 1 row, 2 rows. challenge prepared statement sees argument varchar, , accordingly puts quotes around value. prefer no quotes can build in statement, how can done? ideally mysql have list of ints datatype not have use varchar, not exist.

i required keep prepared statement prevent sql injection attacks.

it seems it's not possible use prepared statement in() , bind 1 single value.

for reason mysql's api replaces in() = (equal) when there 1 single value bind in corresponding comparison.

i run tests our procedure, that's got.

with original procedure that's mysql generates (test default database):

((`test`.`locationcodes`.`customer` = 'test, inc.') ,  (`test`.`locationcodes`.`id` = '2,3')) 

on case field id integer , mysql converts '2,3' 2 in comparison, that's why returns los angeles, '3,2' return 'san francisco' obviously.

just make sure nothing related field type (id being integer), tried compare value locationcode (varchar), result same.

i changed procedure bind 2 values instead of one.

... set @select='   select id, locationcode   locationcodes     customer="test, inc."   , id in (?,?)   ';  prepare stmt @select; execute stmt using @location,@location2; ... 

in case creates statement properly.

((`test`.`locationcodes`.`customer` = 'test, inc.') ,  (`test`.`locationcodes`.`id` in ('2','3'))) 

that being said, can tell you'll have use approach accomplish prepared statement.

after tests found bug report: http://bugs.mysql.com/bug.php?id=16564

according valeriy, create temporary table values , use join. should work.