CODEWITHSUNDEEP
perldbi
mark
mark

Reputation: 1809

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

I have a query to calculate objects that are within a certain radius of a point based on a document here: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

It works very nicely however I want to only search for those objects that are of a particular type, and this is causing a problem;

The code looks like this:

my $sql = "SELECT *
 FROM (
 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)))) AS distance
  FROM bubbles AS b, bubble_prizes AS pr, bubble_types AS bt
  JOIN (   /* these are the query parameters */
        SELECT  ?  AS latpoint, ? AS longpoint,
                ? AS radius,      ? AS distance_unit
    ) AS p
  WHERE b.lat
     BETWEEN p.latpoint  - (p.radius / p.distance_unit)
         AND p.latpoint  + (p.radius / p.distance_unit)
    AND b.lng
     BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
         AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
    AND pr.bubble = b.id
    AND b.type IN ?
    AND b.type = bt.type
 ) AS d
 WHERE distance <= radius
 ORDER BY distance";

I then do

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

where '(type1, type2)' should be passed to 

b.type IN ?

(which is near the bottom of the SQL).

I've tried every way that I can think of to escape this string so that it works (including lots of ways that are clearly insane but I'm getting desperate) inc

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

etc (I've tried so many things I can't even remember them all.)

No matter what I try I get an SQL error of the form

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(type1, type2)'
    AND b.type = bt.type
 ) AS d
 WHERE distance <= radius'

Depending on how I've tried to escape the string, the error message is slightly different but always relating to the same part of the sql.

I'm now thinking that escaping isn't my problem and I'm missing something about execute. If I run the code in the DB it works fine with a normal IN statement i.e. b.type IN ('type1', 'type2') works fine.

Can someone enlighten me? How am I supposed to do this?

Thanks

Upvotes: 3

Views: 1647

Answers (1)

AKHolland
AKHolland

Reputation: 4445

You will need to use placeholders within the IN (...) statement. The entire point of execute() is to avoid SQL injection, and you're basically attempting to inject SQL there. You can make a dynamic list of placeholders like so:

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

Upvotes: 7

Related Questions