Re: Escaping strings for inclusion into SQL queries

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Patch removed at the request of the author.  Author will resubmit.

I've attached the fixed version of the patch below.  After the
discussion on pgsql-hackers (especially the frightening memory dump in
<12273.999562219@sss.pgh.pa.us>), we decided that it is best not to
use identifiers from an untrusted source at all.  Therefore, all
claims of the suitability of PQescapeString() for identifiers have
been removed.

--
Florian Weimer                       Florian.Weimer@RUS.Uni-Stuttgart.DE
University of Stuttgart           http://cert.uni-stuttgart.de/
RUS-CERT                          +49-711-685-5973/fax +49-711-685-5898

Index: doc/src/sgml/libpq.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/libpq.sgml,v
retrieving revision 1.68
diff -u -r1.68 libpq.sgml
--- doc/src/sgml/libpq.sgml    2001/09/04 00:18:18    1.68
+++ doc/src/sgml/libpq.sgml    2001/09/04 18:32:05
@@ -827,6 +827,42 @@
 </itemizedlist>
 </sect2>

+<sect2 id="libpq-exec-escape-string">
+  <title>Escaping strings for inclusion in SQL queries</title>
+<para>
+<function>PQescapeString</function>
+          Escapes a string for use within an SQL query.
+<synopsis>
+size_t PQescapeString (char *to, const char *from, size_t length);
+</synopsis>
+If you want to include strings which have been received
+from a source which is not trustworthy (for example, because they were
+transmitted across a network), you cannot directly include them in SQL
+queries for security reasons.  Instead, you have to quote special
+characters which are otherwise interpreted by the SQL parser.
+</para>
+<para>
+<function>PQescapeString</> performs this operation.  The
+<parameter>from</> points to the first character of the string which
+is to be escaped, and the <parameter>length</> parameter counts the
+number of characters in this string (a terminating NUL character is
+neither necessary nor counted).  <parameter>to</> shall point to a
+buffer which 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>PQescapeString</> 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 NUL character.  The single quotes which
+must surround PostgreSQL string literals are not part of the result
+string.
+</para>
+<para>
+<function>PQescapeString</> returns the number of characters written
+to <parameter>to</>, not including the terminating NUL character.
+Behavior is undefined when the <parameter>to</> and <parameter>from</>
+strings overlap.
+</para>
+
 <sect2 id="libpq-exec-select-info">
   <title>Retrieving SELECT Result Information</title>

Index: src/interfaces/libpq/fe-exec.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v
retrieving revision 1.108
diff -u -r1.108 fe-exec.c
--- src/interfaces/libpq/fe-exec.c    2001/08/21 20:39:53    1.108
+++ src/interfaces/libpq/fe-exec.c    2001/09/04 18:32:09
@@ -56,6 +56,62 @@
 static int    getNotify(PGconn *conn);
 static int    getNotice(PGconn *conn);

+/* ---------------
+ * Escaping arbitrary strings to get valid SQL strings/identifiers.
+ *
+ * Replaces "\\" with "\\\\", "\0" with "\\0", and "'" with "''".
+ * length is the length of the buffer pointed to by
+ * from.  The buffer at to must be at least 2*length + 1 characters
+ * long.  A terminating NUL character is written.
+ * ---------------
+ */
+
+size_t
+PQescapeString (char *to, const char *from, size_t length)
+{
+    const char *source = from;
+    char *target = to;
+    unsigned int remaining = length;
+
+    while (remaining > 0) {
+        switch (*source) {
+        case '\0':
+            *target = '\\';
+            target++;
+            *target = '0';
+            /* target and remaining are updated below. */
+            break;
+
+        case '\\':
+            *target = '\\';
+            target++;
+            *target = '\\';
+            /* target and remaining are updated below. */
+            break;
+
+        case '\'':
+            *target = '\'';
+            target++;
+            *target = '\'';
+            /* target and remaining are updated below. */
+            break;
+
+        default:
+            *target = *source;
+            /* target and remaining are updated below. */
+        }
+        source++;
+        target++;
+        remaining--;
+    }
+
+    /* Write the terminating NUL character. */
+    *target = '\0';
+
+    return target - to;
+}
+
+

 /* ----------------
  * Space management for PGresult.
Index: src/interfaces/libpq/libpq-fe.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/libpq/libpq-fe.h,v
retrieving revision 1.72
diff -u -r1.72 libpq-fe.h
--- src/interfaces/libpq/libpq-fe.h    2001/08/21 20:39:54    1.72
+++ src/interfaces/libpq/libpq-fe.h    2001/09/04 18:32:09
@@ -251,6 +251,9 @@

 /* === in fe-exec.c === */

+    /* Quoting strings before inclusion in queries. */
+    extern size_t PQescapeString (char *to, const char *from, size_t length);
+
     /* Simple synchronous query */
     extern PGresult *PQexec(PGconn *conn, const char *query);
     extern PGnotify *PQnotifies(PGconn *conn);