Re: Escaping strings for inclusion into SQL queries

Patch applied.  Thanks.

> 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);
> 

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026