[Dbix-class] binding variables to CASE WHEN

Augustus Saunders asaunders at solfo.com
Tue Apr 7 17:36:45 GMT 2015


You can use literal SQL with bound parameters in most places. It's a reference to an array ref: \[SQL, bind1, bind2,...]. You can also put ? into your sql and pass a bind array explicitly in the attributes. It may take some experimentation to figure out what works, as there are bugs in the bind parameter handling. If all else fails, you can ask your database handle to quote your values to avoid SQL injection.

Augustus

On Apr 7, 2015, at 10:24 AM, Adam Witney <awitney at sgul.ac.uk> wrote:

> 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




More information about the DBIx-Class mailing list