On Thu, Jul 28, 2011 at 10:36 AM, Chris Travers <chris.travers@gmail.com> wrote:
> On Thu, Jul 28, 2011 at 8:23 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>
>> This function is an absolute no-go if the string literal is coming
>> from untrusted source, and any robust defenses would ruin the intended
>> effect of the function. There are a number of nasty ways you can (at
>> minimum) DOS your database by allowing arbitrary sql. For example,
>> using generate_series() and advisory_locks you can exhaust lock space.
>>
>
> This is specifically why I would feel extremely uncomfortable exposing
> the string literals as function arguments. I don't think you can
> generally trust the inputs of general-purpose stored procs.
>
> The thing is if stored proc that might call this is in pl/pgsql, all
> you need to do is:
>
> EXECUTE expr INTO myvar;
>
> Or in this case:
>
> DECLARE t_enddate;
> t_expr;
>
> BEGIN
> SELECT last_date INTO t_expr FROM date_ranges;
> EXECUTE $E$ SELECT ('$E$ || t_expr || $E$') $E$ INTO t_enddate;
> END;
>
> That's four lines of code extra needed. In PL/Perl or PL/Python, I
> think you'd have to create a query and run it. but you could do this
> with a module that wouldn't create a stored proc capable of taking
> this as the argument.
That's not the same thing though -- you are just copying a string to
another string essentially. The whole point regarding eval is to
evaluate sql expressions, not string literals...you can't have it both
ways.
You _can_ trust the string inputs for stored procs...static sql is
generally ok, as is dynamic sql via EXECUTE...USING. Certain things
are unsafe, but generally easily avoided. Sanitizing sql is IMO much
easier in a proc than on the client.
merlin