Re: proposal: doc: simplify examples of dynamic SQL

Hi

2014-10-03 5:16 GMT+02:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 10/2/14, 6:51 AM, Pavel Stehule wrote:
>
>> EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
>> colname, keyvalue)
>> or
>>
> -1, because of quoting issues
>

No it isn't. I is 100% safe

> EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
>> colname)
>> USING keyvalue;
>>
> Better, but I think it should really be quote_ident( colname )
>
>> A old examples are very instructive, but little bit less readable and
>> maybe too complex for beginners.
>>
>> Opinions?
>>
> Honestly, I'm not to fond of either. format() is a heck of a lot nicer
> than a forest of ||'s, but I think it still falls short of what we'd really
> want here which is some kind of variable substitution or even a templating
> language. IE:
>
> EXECUTE 'UDPATE tbl SET $colname = newvalue WHERE key = $keyvalue';
>

Your proposal significantly increase a work with string. Escaping and
quoting depends on context, and should be different in different context.
In PHP or Perl, this technique is the most simple backdoor for SQL
injection.

Pavel