Trouble with parameters and IN query

[Greg McCann]

I want to run a query like the following, but can't get it to work using parameters. The sample query here has been simplified for clarity but is tested, working code.

This works fine, and returns two results as expected:

$sth = $dbh->prepare(qq( select * from ip where id in (2,3) ));
$sth->execute() or print $sth->errstr, "<br>\n";

I can do the following, but would like to avoid it if at all possible, since this leaves me open to SQL injection attacks:

$user_supplied_value = '2,3';
$sth = $dbh->prepare(qq( select * from ip where id in ($user_supplied_value) ));
$sth->execute() or print $sth->errstr, "<br>\n";

What I would like to do is parameterize the query, like this:

$user_supplied_value = '2,3';
$sth = $dbh->prepare(qq( select * from ip where id in (?) ));
$sth->execute($user_supplied_value) or print $sth->errstr, "<br>\n";

Unfortunately, what happens here is that the actual query that gets executed is:

select * from ip where id in ('2,3')

instead of...

select * from ip where id in (2,3)

... and it returns only one result.

So now we try to force DBI to see the parameter as an integer:

my $user_supplied_value = '2,3';
$sth = $dbh->prepare(qq( select * from ip where id in (?) ));
$sth->bind_param(1, $user_supplied_value, { TYPE => 'SQL_INTEGER' });
$sth->execute() or print $sth->errstr, "<br>\n";

In spite of this, it still sees the query as:

select * from ip where id in ('2,3')

and only returns one result.

I have run out of ideas. Can someone give me a clue how to do this?

Thank you.

Greg M.