[Dbix-class] binding variables to CASE WHEN

[Adam Witney]

Hi,

I am trying to generate some counts in order to draw some charts. The query I 
have is this

SELECT TO_CHAR( collection_date, 'Mon' ) AS month, 
       EXTRACT( year from collection_date ) AS year, 
           COUNT( CASE WHEN pathogen.name = 'Staphylococcus epidermidis' THEN 1 
ELSE NULL END ), 
           COUNT( CASE WHEN pathogen.name = 'Staphylococcus hominis' THEN 1 
ELSE NULL END ) 
   FROM 
       culture me LEFT JOIN culture__pathogen culture_pathogens ON 
culture_pathogens.culture_id = me.id 
                                  LEFT JOIN pathogen pathogen ON pathogen.id = 
culture_pathogens.pathogen_id 
   GROUP BY year, month 
   ORDER BY year, month

Which is generated from

$rs = $c->model('DB::Culture')->search( $search, {
        select => [
                { to_char => "collection_date, 'Mon'", -as => 'month' },
                { extract => "year from collection_date", -as => 'year' },
                { count => "CASE WHEN pathogen.name = '".$name1."' THEN 1 ELSE 
NULL END" },
                { count => "CASE WHEN pathogen.name = '".$name2."' THEN 1 ELSE 
NULL END" }
        ],
        join => { 'culture_pathogens' => 'pathogen'  },
        as => [ 'month','year', 'A', 'B' ],
        order_by => 'year, month',
        group_by => 'year, month'
});

But I need to bind values into the CASE WHEN statements. At the moment, 
presumably there is a possible SQL injection vulnerability? 

Is it possible to bind variables into this?

Thanks

Adam

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk