perl error when passing DBI->execute values for IN clause -

perl error when passing DBI->execute values for IN clause -

i have query calculate objects within radius of point based on document here: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

it works nicely want search objects of particular type, , causing problem;

the code looks this:

my $sql = "select * ( select b.*, pr.postcode, pr.prize, pr.title, pr.collection, pr.redeemed, pr.delivery, pr.archived, bt.category, p.radius, p.distance_unit * degrees(acos(cos(radians(p.latpoint)) * cos(radians(b.lat)) * cos(radians(p.longpoint - b.lng)) + sin(radians(p.latpoint)) * sin(radians(b.lat)))) distance bubbles b, bubble_prizes pr, bubble_types bt bring together ( /* these query parameters */ select ? latpoint, ? longpoint, ? radius, ? distance_unit ) p b.lat between p.latpoint - (p.radius / p.distance_unit) , p.latpoint + (p.radius / p.distance_unit) , b.lng between p.longpoint - (p.radius / (p.distance_unit * cos(radians(p.latpoint)))) , p.longpoint + (p.radius / (p.distance_unit * cos(radians(p.latpoint)))) , pr.bubble = b.id , b.type in ? , b.type = bt.type ) d distance <= radius order distance";

i do

my $points = $y->dbh->prepare($sql); $results = $points->execute($lat, $lng, $rad, $units, '(type1, type2)');

where '(type1, type2)' should passed

b.type in ?

(which near bottom of sql).

i've tried every way can think of escape string works (including lots of ways insane i'm getting desperate) inc

'(type1, type2)' '\(\'type1\', \'type2\'\)' '(\'type1\', \'type2\')' "('type1', 'type2')"

etc (i've tried many things can't remember them all.)

no matter seek sql error of form

dbd::mysql::st execute failed: have error in sql syntax; check manual corresponds mysql server version right syntax utilize near ''(type1, type2)' , b.type = bt.type ) d distance <= radius'

depending on how i've tried escape string, error message different relating same part of sql.

i'm thinking escaping isn't problem , i'm missing execute. if run code in db works fine normal in statement i.e. b.type in ('type1', 'type2') works fine.

can enlighten me? how supposed this?

thanks

you need utilize placeholders within in (...) statement. entire point of execute() avoid sql injection, , you're attempting inject sql there. can create dynamic list of placeholders so:

my @types = qw(type1 type2); $placeholders = bring together ", ", ("?") x @types; $sql = "... b.typeid in ($placeholders) ..."; $points = $y->dbh->prepare($sql); $results = $points->execute($lat, $lng, $rad, $units, @types);

perl dbi