Re: RfD: more powerful "any" types - Mailing list pgsql-hackers

2009/9/15 decibel <decibel@decibel.org>:
>
> On Sep 14, 2009, at 1:02 PM, Pavel Stehule wrote:
>
>> 2009/9/14 Merlin Moncure <mmoncure@gmail.com>:
>>>
>>> On Mon, Sep 14, 2009 at 1:42 PM, Pavel Stehule <pavel.stehule@gmail.com>
>>> wrote:
>>>>>
>>>>> How is it any worse than what people can already do? Anyone who isn't
>>>>> aware
>>>>> of the dangers of SQL injection has already screwed themselves. You're
>>>>> basically arguing that they would put a variable inside of quotes, but
>>>>> they
>>>>> would never use ||.
>>>>
>>>> simply - people use functions quote_literal or quote_ident.
>>>
>>> you still have use of those functions:
>>> execute sprintf('select * from %s', quote_ident($1));
>>>
>>> sprintf is no more or less dangerous than || operator.
>>
>> sure. I commented different feature
>>
>> some := 'select * from $1'
>>
>> regards
>> Pavel
>>
>> p.s. In this case, I am not sure what is more readable:
>>
>> execute 'select * from ' || quote_ident($1)
>>
>> is readable well too.
>
>
> Ahh... the problem is one of fixating on an example instead of the overall
> use case.
>
> More examples...
>
> RETURN 'Your account is now $days_overdue days overdue. Please contact your
> account manager ($manager_name) to ...';
>
> And an example of how readability would certainly be improved...
>
> sql := $$INSERT INTO cnu_stats.$$ || v_field_name || $$( $$ || v_field_name
> || $$ )
>    SELECT DISTINCT $$ || v_field_name || $$
>        FROM chunk t
>        WHERE NOT EXISTS( SELECT * FROM cnu_stats.$$ || v_field_name || $$ s
> WHERE s.$$
>            || v_field_name || $$ = t.$$ || v_field_name || $$ )$$
>

it isn't fair :) why you use $$ without single quote? And still this
case should be vulnerable on SQL injection. Maybe you or me knows,
what SQL injection means, but beginners knows nothing and this people
use following bad code:

sql := $$SELECT * FROM '${table_name}'$$} and are happy. But this code
is wrong!

> becomes
>
> sql := $$INSERT INTO cnu_stats.${v_field_name} ( ${v_field_name} )
>    SELECT DISTINCT $v_field_name
>        FROM chunk t
>        WHERE NOT EXISTS( SELECT * FROM cnu_stats.${v_field_name} s
>                              WHERE s.${v_field_name} = t.${v_field_name} )$$
>
> Granted, that example wouldn't be too bad with sprintf, but only because
> everything is referencing the same field.

Really I dislike bash like syntax in SQL. What I know - SQL is
language for normal people - it is reason why it's verbose and English
like. Bash is sw for UNIX hackers. If we cut some features from others
languages, then bash, c, perl should be last (I knows these languages
well and I using it well). I thing, so there are better languages like
ADA, SQL/PSM, Python.

regards
Pavel Stehule

> --
> Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>