Re: using separate parameters in psql query execution

Hello

now - complete patch

ToDo:
* enhance a documentation (any volunteer?)
* check name for backslash command

Regards
Pavel Stehule

On Mon, Nov 16, 2009 at 5:01 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.

...Robert

2009/12/21 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Mon, Nov 16, 2009 at 5:01 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.

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.

Regards
Pavel

>
> ...Robert
>

On Mon, Dec 21, 2009 at 1:03 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2009/12/21 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> On Mon, Nov 16, 2009 at 5:01 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.

> 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.

...Robert

2009/12/21 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Mon, Dec 21, 2009 at 1:03 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2009/12/21 Robert Haas <robertmhaas(at)gmail(dot)com>:
>>> On Mon, Nov 16, 2009 at 5:01 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
----------
postgres
pavel
(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
>