[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