From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: info about patch: using parametrised query in psql |
Date: | 2009-12-25 08:55:17 |
Message-ID: | 162867790912250055q2ddc9b2bn20e66db7ce631e69@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2009-12-25 08:58:09 | keywords on .pgpass |
Previous Message | Bruce Momjian | 2009-12-25 06:17:35 | Re: Removing pg_migrator limitations |