prepareTreshold=0 vs. transaction control commands - Mailing list pgsql-jdbc

From Bernd Helmle
Subject prepareTreshold=0 vs. transaction control commands
Date
Msg-id A3F31011DB93D443CAA72041@apophis.local
Whole thread Raw
List pgsql-jdbc
During debugging database problems at a customer site with JDBC i discovered
the following behavior when passing prepareThreshold=0 to the connection DSN:

The SQL workflow is supposed as follows:

BEGIN;
SELECT 1;
COMMIT;

Stripped sample program attached. What i expected was the complete absence of
named prepared statements, however, examining the debug output i've seen the
following:

16:02:23.777 (1)  FE=> Parse(stmt=S_1,query="BEGIN",oids={})
16:02:23.777 (1)  FE=> Bind(stmt=S_1,portal=null)
16:02:23.777 (1)  FE=> Execute(portal=null,limit=0)
16:02:23.777 (1)  FE=> Parse(stmt=null,query="SELECT $1::int",oids={23})
16:02:23.778 (1)  FE=> Bind(stmt=null,portal=null,$1=<'[B@7a982589'>)
16:02:23.778 (1)  FE=> Describe(portal=null)
16:02:23.778 (1)  FE=> Execute(portal=null,limit=0)
16:02:23.778 (1)  FE=> Sync
16:02:23.779 (1)  <=BE ParseComplete [S_1]
16:02:23.779 (1)  <=BE BindComplete [null]
16:02:23.780 (1)  <=BE CommandStatus(BEGIN)
16:02:23.780 (1)  <=BE ParseComplete [null]
16:02:23.780 (1)  <=BE BindComplete [null]
16:02:23.780 (1)  <=BE RowDescription(1)
16:02:23.780 (1)         Field(,INT4,4,T)
16:02:23.780 (1)  <=BE DataRow(len=3)
16:02:23.781 (1)  <=BE CommandStatus(SELECT 1)
16:02:23.789 (1)  <=BE ReadyForQuery(T)
16:02:23.789 (1) simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Connection$TransactionCommandHandler@7c4c51,
maxRows=0, fetchSize=0, flags=22
16:02:23.789 (1)  FE=> Parse(stmt=S_2,query="COMMIT",oids={})
16:02:23.790 (1)  FE=> Bind(stmt=S_2,portal=null)
16:02:23.790 (1)  FE=> Execute(portal=null,limit=1)
16:02:23.790 (1)  FE=> Sync
16:02:23.790 (1)  <=BE ParseComplete [S_2]
16:02:23.790 (1)  <=BE BindComplete [null]
16:02:23.790 (1)  <=BE CommandStatus(COMMIT)

Please note the named statement for BEGIN and COMMIT. The reason for this seems
to be that those transaction control commands aren't flagged with QUERY_ONESHOT
in case someone uses prepareThreshold=0 and always gets named. I couldn't get
an explanation for this from the code (AFAIU), so i wonder what the technical
reason behind this behavior is...anyone able to shed a light on this?

--
Thanks

    Bernd

Attachment

pgsql-jdbc by date:

Previous
From: Kurt Seifried
Date:
Subject: Re: [oss-security] CVE DISPUTE notification: postgresql-jdbc: SQL injection due improper escaping of JDBC statement parameters
Next
From: Sez Sez
Date:
Subject: ...