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

Hi, thanks for all replies.
According to all your discussions, Maybe the problems is that
1) keep modifications just in client side;
2) modifications VS client current applications

Maybe we could create a new function(May called PQexecSafe() ) just likes PQexec() but with additional input argument(May called issafe) to switch whether allowing at most one SQL command.
In that way, clients who want the safe feature just use the new function PQexecSafe() with issafe set true,
The others can choose:
1) just use the old version PQexec(),
2) using PQexecSafe() with issafe set false

Then, we strongly recommended using PQexecSafe(),and PQexec() keep in use but labeled deprecated in documents. In other word, give client the time to choose and modify their applications if then want use the safe feature.

Of course, we should admit that it is not just a coding problem.

-----Original Message-----
From: Daniel Verite [mailto:daniel(at)manitou-mail(dot)org]
Sent: Wednesday, March 20, 2019 3:08 AM
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>; Wu, Fei/吴 非 <wufei(dot)fnst(at)cn(dot)fujitsu(dot)com>; pgsql-hackers(at)postgresql(dot)org
Subject: Re: Willing to fix a PQexec() in libpq module

Tom Lane wrote:

> Unfortunately, if the default behavior doesn't change, then there's
> little argument for doing this at all. The security reasoning behind
> doing anything in this area would be to provide an extra measure of
> protection against SQL-injection attacks on carelessly-written
> clients, and of course it's unlikely that a carelessly-written client
> would get changed to make use of a non-default feature.

A patch introducing an "allow_multiple_queries" GUC to control this was proposed and eventually rejected for lack of consensus some time ago (also there were some concerns about the implementation that might have played against it too):

https://www.postgresql.org/message-id/CALAY4q_eHUx%3D3p1QUOvabibwBvxEWGm-bzORrHA-itB7MBtd5Q%40mail.gmail.com

About the effectiveness of this feature, there's a valid use case in which it's not the developers who decide to set this GUC, but the DBA or the organization deploying the application. That applies to applications that of course do not intentionally use multiple queries per command.
That would provide a certain level a protection against SQL injections, without changing the application or libpq or breaking backward compatibility, being optional.

But both in this thread and the other thread, the reasoning about the GUC seems to make the premise that applications would need to be updated or developpers need to be aware of it, as if they _had_ to issue SET allow_multiple_queries TO off/on, rather than being submitted to it, as imposed upon them by postgresql.conf or the database settings.

If we compare this to, say, lo_compat_privileges. An application typically doesn't get to decide whether it's "on". It's for a superuser to decide which databases or which users must operate with this setting to "on".
Why wouldn't that model work for disallowing multiple queries per command?

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite