Re: Willing to fix a PQexec() in libpq module

On 2019-03-19 10:02:33 -0700, Andres Freund wrote:
> Hi,
>
> On 2019-03-19 13:59:34 -0300, Alvaro Herrera wrote:
> > On 2019-Mar-19, Andres Freund wrote:
> > > On 2019-03-19 12:51:39 -0400, Tom Lane wrote:
> > > > David Fetter <david(at)fetter(dot)org> writes:
> > > > > I think the answer is "no," and we should deprecate this misfeature.
> > > > > It's bad enough that we'll be supporting it for five years after
> > > > > deprecating it, but it's worse to leave it hanging around our necks
> > > > > forever. https://en.wikipedia.org/wiki/Albatross_(metaphor)
> > > >
> > > > The problem with that approach is that not everybody agrees that
> > > > it's a misfeature.
> > >
> > > Yea, it's extremely useful to just be able to send a whole script to the
> > > server. Otherwise every application wanting to do so needs to be able to
> > > split SQL statements, not exactly a trivial task. And the result will be
> > > slower, due to increased rountrips.
> >
> > I suppose it can be argued that for the cases where they want that, it
> > is not entirely ridiculous to have it be done with a different API call,
> > say PQexecMultiple.
>
> Sure, but what'd the gain be? Using PQexecParams() already enforces that
> there's only a single command. Sure, explicit is better than implicit
> and all that, but is that justification for breaking a significant
> number of applications?

In short: I think we should just remove this todo entry. If somebody
feels like we should do something, I guess making the dangers of
PQexec() vs PQexecPrepared() even clearer would be the best thing to
do. Although I actually find it easy enough, it's not like we're holding
back:

https://www.postgresql.org/docs/devel/libpq-exec.html

PQexec():

The command string can include multiple SQL commands (separated by semicolons). Multiple queries sent in a single PQexec call are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions. (See Section 52.2.2.1 for more details about how the server handles multi-query strings.) Note however that the returned PGresult structure describes only the result of the last command executed from the string. Should one of the commands fail, processing of the string stops with it and the returned PGresult describes the error condition.

PQexecParams():
Unlike PQexec, PQexecParams allows at most one SQL command in the given string. (There can be semicolons in it, but not more than one nonempty command.) This is a limitation of the underlying protocol, but has some usefulness as an extra defense against SQL-injection attacks.