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 <awit...@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
_______________________________________________
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
