[Catalyst] PostgreSQL quoting issues

[Carl Franks]

On 16/01/2008, Marius Kjeldahl <mariusauto-catalyst at kjeldahl.net> wrote:
> As my original post demonstrated, that was not the issue at all. But I
> found the reason for the bug. It seems IF in the following expression:
>
>      my $rsts = $c->model ('MintAppDB::TransSum')->find ({
>                        category => $c->req->param ('category'),
>                        sentto => $c->req->param ('sentto'),
>                        iso => $c->req->param ('iso')
>                      });

This is an example of insecure parameter handling which has been
discussed on this list before: if the user submitted multiple values
for 'category', it would cause your keys and values to be bumped, like
so...

    category => value1
    value2 => 'sentto',
    param('sentto') => 'iso',
    param('iso') => undef,

> if category and iso pointed to undefined values, the bug I struggled
> with was triggered. Making sure that they were defined took care of the
> problem. I guess the sql generating stuff didn't like being fed
> undefined values.

Does cat's param() return undef of an empty list in this situation?
If it returns an empty list, that would explain your quoted SQL

> STH: SELECT me.updated, me.category, me.sentto, me.iso, me.amt FROM
> mm_transsum me WHERE ( email:marius at kjeldahl.net = ? AND me.category = ? )

when 'category' and 'iso' were not submitted, and 'sentto' was
'email:marius at kjeldahl.net', your find() parameters might have ended
up as:

    category => 'sentto',
    'email:marius at kjeldahl.net' => 'iso',

...which would explain why there was only 2 parts to the WHERE clause,
and why the email address was on the lefthand-side of the '='.

Carl