Re: JDBC Support for standard_conforming_strings - Mailing list pgsql-jdbc

Michael Paesold wrote:
>
> I am sending a work-in-progress patch now, as I have some more questions
> on how to proceed.
>
> Progress so far:

Looks good so far.  So two typos in comments.  Incremental diff attached.

> Question 1:
> -----------
> The V2 code as well as the query-parsing code in
> AbstractJdbc2DatabaseMetaData is either mostly static or has otherwise
> no reference to the connection object. There are two ways to tackle
> this: either pass around standardConformingStrings (the variable name
> could be stdStrings for brevity), or give the code access to the current
> connection.
>

Doesn't really matter how you pass this around.  The patch's usage of an
additional boolean parameter looks fine to me.

> Question 2:
> -----------
> The problem with the V2 protocol code is, that tracking the state of
> standard_conforming_strings is not really possible, AFAICS. Currently I
> just read standard_conforming_strings at startup, and that's it. So as
> soon as someone changes the variable, quoting will be incorrect, which
> is a security issue. Therefore my new suggestion is to used the E''
> string syntax in the V2 protocol path if the server version is >= 8.2.
> The version of the server cannot change during the connection, so we are
> save here.

I'm not super worried about this.  If an administrator changes the value
in postgresql.conf and HUPs the postmaster they really can't expect
every client to pick this up immediately.  Even when we are monitoring
the state (V3) we won't know it has changed until after we execute the
next query (with the wrong escaping setting).  If an interface allows
the user to enter arbitrary SQL such that they can change this setting
then it's not really a security issue because they don't need to break
escaping to sneak a query in because they can already execute any query
they want.

> For question 1, I would then suggest to add an argument to the V2Query
> constructor that tells whether E'' should be used instead of '', or not.

This is a good idea.

> For parsing inside the JDBC driver, we would still rely on the initially
> reported value of standard_conforming_strings. We could also add an URL
> parameter to specify what should be set initially.
>

I'd like to see a generic way of setting any GUC parameter at startup,
instead of just this one.  The existing methods of setting this per user
or per database should be OK.
diff -rc origstandard/org/postgresql/core/Parser.java standard/org/postgresql/core/Parser.java
*** origstandard/org/postgresql/core/Parser.java    Sun Nov 12 22:18:04 2006
--- standard/org/postgresql/core/Parser.java    Sun Nov 12 15:52:19 2006
***************
*** 28,34 ****
                                          boolean standardConformingStrings) {
          if (standardConformingStrings)
          {
!             // don NOT treat backslashes as escape characters
              while (++offset < query.length)
              {
                  switch (query[offset])
--- 28,34 ----
                                          boolean standardConformingStrings) {
          if (standardConformingStrings)
          {
!             // do NOT treat backslashes as escape characters
              while (++offset < query.length)
              {
                  switch (query[offset])
diff -rc origstandard/org/postgresql/jdbc2/AbstractJdbc2Connection.java
standard/org/postgresql/jdbc2/AbstractJdbc2Connection.java
*** origstandard/org/postgresql/jdbc2/AbstractJdbc2Connection.java    Sun Nov 12 22:18:04 2006
--- standard/org/postgresql/jdbc2/AbstractJdbc2Connection.java    Sun Nov 12 16:07:29 2006
***************
*** 969,975 ****
          {
              // With standard_conforming_string off, escape backslashes and
              // single-quotes, but still escape single-quotes by doubling, to
!             // avoid a security hazord if the reported value of
              // standard_conforming_strings is incorrect.
              for (int i = 0; i < str.length(); ++i)
              {
--- 969,975 ----
          {
              // With standard_conforming_string off, escape backslashes and
              // single-quotes, but still escape single-quotes by doubling, to
!             // avoid a security hazard if the reported value of
              // standard_conforming_strings is incorrect.
              for (int i = 0; i < str.length(); ++i)
              {