Re: [GENERAL] Gripe: bytea_output default => data corruption

ljb wrote:
> bruce@momjian.us wrote:
> >...
> >> Based on this report, I have created the attached documentation patch
> >> which clarifies the libpq behavior for escaping bytea.  I am planning to
> >> backpatch this to 9.0 as well.
>
> This change says PQescapeBytea is unable to adjust its behavior based on
> bytea_output, implying that PQescapeByteaConn does adjust its behavior
> based on byte_output. Wrong! Neither one knows or cares about the bytea_output
> parameter, which is solely for the backend to tell it how to present bytea
> data to the front-end. Not for the front-ends at all.

Good point, sorry.

> (See why we need this behavior documented?)

Yep!

> Non-use of 'standard_conforming_strings' is also wrong, in the current
> (pre-patched) docs. PQescapeBytea does adjust its behavior for
> standard_conforming_strings, but only for the single-connection case. See
> similar text for PQescapeString.

Yep.

> Regarding the first paragraph changed, it seems off to me.  "PQescapeByteConn
> escapes such bytes using either hex encoding..." tells me that when hex
> encoding is used, PQescapeByte encodes only bytes that need escaping. Not
> true - hex encoding encodes (not escapes) every byte.

OK, great feedback.  I made the adjustments you suggested and tried to
pattern it after PQescapeStringConn, with adjustments where appropriate.

Here is an updated patch, again to be backpatched to 9.0.  There were
obviously big mistakes in libpq docs and I am embarrassed I waited so
long to fix this;  my apologies.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index e78d708..56b72a7 100644
*** a/doc/src/sgml/libpq.sgml
--- b/doc/src/sgml/libpq.sgml
*************** size_t PQescapeStringConn(PGconn *conn,
*** 3371,3385 ****

       <listitem>
       <para>
  <synopsis>
  size_t PQescapeString (char *to, const char *from, size_t length);
  </synopsis>
       </para>

       <para>
!       <function>PQescapeString</> is an older, deprecated version of
!       <function>PQescapeStringConn</>; the difference is that it does
!       not take <parameter>conn</> or <parameter>error</> parameters.
        Because of this, it cannot adjust its behavior depending on the
        connection properties (such as character encoding) and therefore
        <emphasis>it might give the wrong results</>.  Also, it has no way
--- 3371,3387 ----

       <listitem>
       <para>
+        <function>PQescapeString</> is an older, deprecated version of
+        <function>PQescapeStringConn</>.
  <synopsis>
  size_t PQescapeString (char *to, const char *from, size_t length);
  </synopsis>
       </para>

       <para>
!       The only difference from <function>PQescapeStringConn</> is that
!       <function>PQescapeString</> does not take a <structname>PGconn</>
!       or <parameter>error</> parameters.
        Because of this, it cannot adjust its behavior depending on the
        connection properties (such as character encoding) and therefore
        <emphasis>it might give the wrong results</>.  Also, it has no way
*************** size_t PQescapeString (char *to, const c
*** 3387,3393 ****
       </para>

       <para>
!       <function>PQescapeString</> can be used safely in single-threaded
        client programs that work with only one <productname>PostgreSQL</>
        connection at a time (in this case it can find out what it needs to
        know <quote>behind the scenes</>).  In other contexts it is a security
--- 3389,3395 ----
       </para>

       <para>
!       <function>PQescapeString</> can be used safely in
        client programs that work with only one <productname>PostgreSQL</>
        connection at a time (in this case it can find out what it needs to
        know <quote>behind the scenes</>).  In other contexts it is a security
*************** unsigned char *PQescapeByteaConn(PGconn
*** 3419,3434 ****
        </para>

        <para>
!        Certain byte values <emphasis>must</emphasis> be escaped (but all
!        byte values <emphasis>can</emphasis> be escaped) when used as part
!        of a <type>bytea</type> literal in an <acronym>SQL</acronym>
!        statement. In general, to escape a byte, it is converted into the
!        three digit octal number equal to the octet value, and preceded by
!        usually two backslashes. The single quote (<literal>'</>) and backslash
!        (<literal>\</>) characters have special alternative escape
!        sequences. See <xref linkend="datatype-binary"> for more
!        information. <function>PQescapeByteaConn</function> performs this
!        operation, escaping only the minimally required bytes.
        </para>

        <para>
--- 3421,3431 ----
        </para>

        <para>
!        Certain byte values must be escaped when used as part of a
!        <type>bytea</type> literal in an <acronym>SQL</acronym> statement.
!        <function>PQescapeByteaConn</function> escapes bytes using
!        either hex encoding or backslash escaping.  See <xref
!        linkend="datatype-binary"> for more information.
        </para>

        <para>
*************** unsigned char *PQescapeBytea(const unsig
*** 3485,3504 ****
        <para>
         The only difference from <function>PQescapeByteaConn</> is that
         <function>PQescapeBytea</> does not take a <structname>PGconn</>
!        parameter.  Because of this, it cannot adjust its behavior
!        depending on the connection properties (in particular, whether
!        standard-conforming strings are enabled) and therefore
!        <emphasis>it might give the wrong results</>.  Also, it has no
!        way to return an error message on failure.
!       </para>
!
!       <para>
!        <function>PQescapeBytea</> can be used safely in single-threaded
!        client programs that work with only one <productname>PostgreSQL</>
!        connection at a time (in this case it can find out what it needs
!        to know <quote>behind the scenes</>).  In other contexts it is
!        a security hazard and should be avoided in favor of
!        <function>PQescapeByteaConn</>.
        </para>
       </listitem>
      </varlistentry>
--- 3482,3495 ----
        <para>
         The only difference from <function>PQescapeByteaConn</> is that
         <function>PQescapeBytea</> does not take a <structname>PGconn</>
!        or <parameter>error</> parameters.  Because of this,
!        <function>PQescapeBytea</> can only be used safely in client
!        programs that use a single <productname>PostgreSQL</> connection
!        at a time (in this case it can find out what it needs to know
!        <quote>behind the scenes</>).  It <emphasis>might give the
!        wrong results</> if used in programs that use multiple database
!        connections (use <function>PQescapeByteaConn</> in such cases).
!        Also, it has no way to report error conditions.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/release-9.0.sgml b/doc/src/sgml/release-9.0.sgml
index 2288f1b..c43f142 100644
*** a/doc/src/sgml/release-9.0.sgml
--- b/doc/src/sgml/release-9.0.sgml
***************
*** 2342,2348 ****
        whether hex or traditional format is used for <type>bytea</>
        output.  Libpq's <function>PQescapeByteaConn()</> function automatically
        uses the hex format when connected to <productname>PostgreSQL</> 9.0
!       or newer servers.
       </para>

       <para>
--- 2342,2349 ----
        whether hex or traditional format is used for <type>bytea</>
        output.  Libpq's <function>PQescapeByteaConn()</> function automatically
        uses the hex format when connected to <productname>PostgreSQL</> 9.0
!       or newer servers.  However, pre-9.0 libpq versions will not
!       correctly process hex format from newer servers.
       </para>

       <para>