Re: info about patch: using parametrised query in psql - Mailing list pgsql-hackers

>
> This makes sense now that you've explained it.  Personally, I would
> not choose to use psql as a scripting language, and I think there has

The scripting are not realised directly in psql - psql missing some
basic features still. Usually is used in combination with bash (any
shell) - like starter stored procedures or source of data.

for x in `echo "sql" | psql params db
do
...
done

this combination is relative very strong.

> been some controversy on that point in the past, though I don't
> remember the details.  In spite of that, though, it seems to me that
> it does make some sense to provide a mechanism for escaping the value
> stored in a psql variable, since - if nothing else - someone might
> easily want to do the sort of thing you're describing here in an
> interactive session.
>
> However, I think the approach you've taken in this patch is a
> non-starter.  You've basically added a global flag that will cause ALL
> variables to be passed in a way that removes the need for them to be
> escaped.  That seems pretty inconvenient and awkward.  What happens if
> someone wants to do "INSERT INTO :foo VALUES (:bar)"?  They're out of

Using a global flags is typical for psql. There are nothing else. I am
thinking about stacked states for epsql, but it isn't some for psql.
psql uses global flags, it uses global variables. I aware of
disadvantages - but I thing so it is in agreement with psql design "do
things simple".

If somebody use variable on wrong place, then result will be a syntax
error. But better fail then be not secure. For full functionality it
needs some explicit syntax for quote_ident - so correct and secure
statement will be:

INSERT INTO :[foo] VALUES (:bar)

There are two ways (three) - both are possible and well, and probably
it is +/- personal preferences who prefer one or second:

a) using parametrised queries - it simple way - bulletproof with limit
- cannot use variable as identifier
b) using some quoting mechanism - it little bit more complex -
PostgreSQL uses two different quoting styles, for somebody isn't
bulletproof, but it could be used everywhere. There are big advantage
- no new global flag - so using should be simpler for beginners.

c) combination

a) INSERT INTO :foo VALUES(:bar) -- isn't possible
b) INSERT INTO :[foo] VALUES(:{bar}) -- I used syntax from epsql fpr
this moment - could be different
c) INSERT INTO :[foo] VALUES(:bar)

I didn't need to (b) or (c), personally I prefer (a), maybe (b). It is
only my personal preference - and I have a good knowledge of
parametrised queries. Typical user can thing different. I am not
strong in it. I'll be satisfied if any form will be supported. I
tested all variants.

> luck.  Futhermore, if a psql script that expects the pexec flag to be
> set one way is run with it set the other way, it may either work fine,
> work OK but with a potential security hole, or fail spectacularly.  I
> think maybe what we need here is a piece of syntax to indicate that a
> specific parameter should be substituted after first being passed
> through PQescapeStringConn.

PQescapeStringConn is good, but it isn't helper for INSERT INTO :foo.
It is analogy for quote_literal function, not for quote_ident. So we
need enhance PQ function sets. Escaping is little bit slower, but it
isn't important in this case. I agree, potential escaping needs
explicit syntax.

?
Regards
Pavel

>
> Other thoughts?
>
> ...Robert
>