Re: Escaping strings for inclusion into SQL queries

Patch applied. Thanks.

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(dot)999562219(at)sss(dot)pgh(dot)pa(dot)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(dot)Weimer(at)RUS(dot)Uni-Stuttgart(dot)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(at)postgresql(dot)org

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