Re: possible design bug with PQescapeString()

> > > But actually I'd argue that
> > > letting the client programmer supply the encoding is still a pretty
> > > dangerous practice.  Your example demonstrates that if the encoding
> > > PQescapeString is told is different from the encoding the backend parser
> > > thinks is in use, problems result.  Perhaps we should pass the PGconn
> > > to new-PQescapeString and let it dig the client encoding out of that.
> > 
> > Sound good to pass PGconn to new-PQescapeString. Here is the proposed
> > calling sequence for the new function:
> > 
> > size_t PQescapeStringWithConn (const PGconn *conn, char *to, const char *from, size_t length)
> > 
> > If this is ok, I will implement for 8.2.

Here is the promised patches for 8.2. If there's no objection, I will
commit tomorrow.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
cvs diff: Diffing src/interfaces/libpq
Index: src/interfaces/libpq/fe-exec.c
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v
retrieving revision 1.179
diff -c -r1.179 fe-exec.c
*** src/interfaces/libpq/fe-exec.c    25 Jan 2006 20:44:32 -0000    1.179
--- src/interfaces/libpq/fe-exec.c    26 Feb 2006 14:16:30 -0000
***************
*** 2373,2378 ****
--- 2373,2435 ---- }  /*
+  * Escaping arbitrary strings to get valid SQL literal strings.
+  * mostly same as PQescapeString() except that this function is
+  * multibyte aware. The encoding info is retrieved from conn. So you
+  * should set proper client encoding before using this.
+  *
+  * Replaces "\\" with "\\\\" and "'" with "''".
+  *
+  * length is the length of the source string.  (Note: if a terminating NUL
+  * is encountered sooner, PQescapeString stops short of "length"; the behavior
+  * is thus rather like strncpy.)
+  *
+  * For safety the buffer at "to" must be at least 2*length + 1 bytes long.
+  * A terminating NUL character is added to the output string, whether the
+  * input is NUL-terminated or not.
+  *
+  * Returns the actual length of the output (not counting the terminating NUL).
+  */
+ size_t
+ PQescapeStringWithConn(PGconn *conn, char *to, const char *from, size_t length)
+ {
+     const char *source = from;
+     char       *target = to;
+     size_t        remaining = length;
+     int len;
+ 
+     if (!conn)
+     {
+         *target = '\0';
+         return 0;
+     }
+ 
+     while (remaining > 0 && *source != '\0')
+     {
+         if (SQL_STR_DOUBLE(*source))
+         {
+             *target++ = *source;
+             *target++ = *source++;
+         }
+         else
+         {
+             len = PQmblen(source, conn->client_encoding);
+             while (*source != '\0' && remaining > 0 && len > 0)
+             {
+                 *target++ = *source++;
+                 remaining--;
+                 len--;
+             }
+         }
+     }
+ 
+     /* Write the terminating NUL character. */
+     *target = '\0';
+ 
+     return target - to;
+ }
+ 
+ /*  *        PQescapeBytea    - converts from binary string to the  *        minimal encoding necessary to include
thestring in an SQL  *        INSERT statement with a bytea type column as the target.
 
Index: src/interfaces/libpq/libpq-fe.h
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/libpq/libpq-fe.h,v
retrieving revision 1.124
diff -c -r1.124 libpq-fe.h
*** src/interfaces/libpq/libpq-fe.h    26 Dec 2005 14:58:06 -0000    1.124
--- src/interfaces/libpq/libpq-fe.h    26 Feb 2006 14:16:30 -0000
***************
*** 436,441 ****
--- 436,442 ----  /* Quoting strings before inclusion in queries. */ extern size_t PQescapeString(char *to, const char
*from,size_t length);
 
+ extern size_t PQescapeStringWithConn(PGconn *conn, char *to, const char *from, size_t length); extern unsigned char
*PQescapeBytea(constunsigned char *bintext, size_t binlen,               size_t *bytealen); extern unsigned char
*PQunescapeBytea(constunsigned char *strtext,
 
cvs diff: Diffing src/interfaces/libpq/po
cvs diff: Diffing doc/src/sgml
Index: doc/src/sgml/libpq.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v
retrieving revision 1.201
diff -c -r1.201 libpq.sgml
*** doc/src/sgml/libpq.sgml    26 Dec 2005 14:58:04 -0000    1.201
--- doc/src/sgml/libpq.sgml    26 Feb 2006 14:16:34 -0000
***************
*** 2253,2258 ****
--- 2253,2323 ---- </para> </sect2> 
+ <sect2 id="libpq-exec-escape-string-with-conn">
+   <title>Escaping Strings for Inclusion in SQL Commands</title>
+ 
+    <indexterm zone="libpq-exec-escape-string-with-conn"><primary>PQescapeStringWithConn</></>
+    <indexterm zone="libpq-exec-escape-string-with-conn"><primary>escaping strings</></>
+ 
+ <para>
+ <function>PQescapeStringWithConn</function> escapes a string for use within an SQL
+ command.  This is useful when inserting data values as literal constants
+ in SQL commands.  Certain characters (such as quotes and backslashes) must
+ be escaped to prevent them from being interpreted specially by the SQL parser.
+ <function>PQescapeStringWithConn</> performs this operation.
+ </para>
+ 
+ <tip>
+ <para>
+ It is especially important to do proper escaping when handling strings that
+ were received from an untrustworthy source.  Otherwise there is a security
+ risk: you are vulnerable to <quote>SQL injection</> attacks wherein unwanted
+ SQL commands are fed to your database.
+ </para>
+ </tip>
+ 
+ <para>
+ Note that it is not necessary nor correct to do escaping when a data
+ value is passed as a separate parameter in <function>PQexecParams</> or
+ its sibling routines.
+ 
+ <synopsis>
+ size_t PQescapeStringWithConn (PGconn *conn, char *to, const char *from, size_t length);
+ </synopsis>
+ </para>
+ 
+ <para>
+ The parameter <parameter>conn</> is the existing DB connection handle.
+ Before using this function it is important to set the client encoding
+ by using <literal>SET client_encoding TO ...</literal> if neccessary.
+ The parameter <parameter>from</> points to the first character of the string
+ that is to be escaped, and the <parameter>length</> parameter gives the
+ number of characters in this string.  A terminating zero byte is not
+ required, and should not be counted in <parameter>length</>.  (If
+ a terminating zero byte is found before <parameter>length</> bytes are
+ processed, <function>PQescapeStringWithConn</> stops at the zero; the behavior
+ is thus rather like <function>strncpy</>.)
+ <parameter>to</> shall point to a
+ buffer that is able to hold at least one more character than twice
+ the value of <parameter>length</>, otherwise the behavior is
+ undefined.  A call to <function>PQescapeStringWithConn</> writes an escaped
+ version of the <parameter>from</> string to the <parameter>to</>
+ buffer, replacing special characters so that they cannot cause any
+ harm, and adding a terminating zero byte.  The single quotes that
+ must surround <productname>PostgreSQL</> string literals are not
+ included in the result string; they should be provided in the SQL
+ command that the result is inserted into.
+ </para>
+ <para>
+ <function>PQescapeStringWithConn</> returns the number of characters written
+ to <parameter>to</>, not including the terminating zero byte.
+ </para>
+ <para>
+ Behavior is undefined if the <parameter>to</> and <parameter>from</>
+ strings overlap.
+ </para>
+ </sect2>
+    <sect2 id="libpq-exec-escape-bytea">   <title>Escaping Binary Strings for Inclusion in SQL Commands</title>