Re: using separate parameters in psql query execution

2009/12/21 Robert Haas <robertmhaas@gmail.com>:
> On Mon, Dec 21, 2009 at 1:03 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> 2009/12/21 Robert Haas <robertmhaas@gmail.com>:
>>> On Mon, Nov 16, 2009 at 5:01 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>>> Hello
>>>>
>>>> now - complete patch
>>>>
>>>> ToDo:
>>>> * enhance a documentation (any volunteer?)
>>>> * check name for backslash command
>>>
>>> I read through this patch tonight and I don't understand what the
>>> point of this change is.  That's something that should probably be
>>> discussed and also incorporated into the documentation.
>>
>> Do you ask about ToDo points?
>>
>> I used "pexec" as switch. Probably better name is
>>
>> "parametrized-execution", "send-parameters-separately" or "parametrized-queries"
>>
>> general goal of this patch is removing issues with  variables quoting
>> - using psql variables should be more robust and more secure.
>
> My point is that I don't think someone new to psql (or even
> experienced in psql, such as myself) has a hope of reading the
> documentation for this option and understanding why they might or
> might not want to use it.  Even your description here, "removing
> issues with variable quoting" is pretty vague.  Maybe you need to
> provide some examples of the pros and cons of using this option.

ok

the problem:

postgres=#
postgres=# \set name 'Pavel Stehule'
postgres=# select :name;
ERROR:  column "pavel" does not exist
LINE 1: select Pavel Stehule;              ^
The content of variable "name" is simple. So I am able to explicit quting.

postgres=# \set name '\'Pavel Stehule\''
postgres=# select :name;  ?column?
---------------Pavel Stehule
(1 row)

But when content of variable goes from outside - I have a problem. I
can get error, or (In worst case), I can be SQL injected.

postgres=# \set name 'usename from pg_user'
postgres=# select :name;usename
----------postgrespavel
(2 rows)

with using parametrized queris these problems are out, because queries
and parameters are separated.

>> I checked second design based on enhanced syntax -
>> http://www.postgres.cz/index.php/Enhanced-psql#Variables_quoting . It
>> working too, but it needs one exec more.
>
> Hmm, the :[foo] and :{foo} syntax looks sort of cool.  But I don't
> understand why it would need any more server calls.

Actually I don't use local implementation of quoting on client side.
Quoting is processed on server side.

So SELECT :{foo} is processed

like
1. take value foo to var;
2. call exec_query_params("SELECT quote_literal($1)", var)
3 use result as content of foo

if we copy quote_literal and quote_ident to client, then this call
should be removed.

Pavel

>
> ...Robert
>