Bug 192169

Description of problem:
Current PostgreSQL releases allow remote injection of SQL commands through clients that believe they 
are taking adequate steps to quote untrusted strings.

Version-Release number of selected component (if applicable):
postgresql-7.4.8

How reproducible:
100%

Additional info:

Below is a draft description of the problem.  Repaired releases will be available Monday 5/22.  Given 
that there are numerous other known bugs in PG 7.4.8, I think the appropriate response will be to 
update to 7.4.13 in toto, rather than back-patch just the security problem.  We are way overdue for a 
PG update anyway; I had been hoping to schedule a routine quarterly update, but the existence of a 
security issue forces doing something quicker.


Summary:

We have recently been made aware that sloppy handling of multibyte
character encodings offers several openings for SQL-injection attacks.
For example, there are encodings in which the ASCII code for backslash
is a legal ending byte for a multibyte character (an example is 0x95 0x5c
in SJIS).  Suppose that an exposed client is operating with
client_encoding SJIS, and that an attacker sends a string like
	0x95 0x5c ' SQL text
If the client escapes this with a non-encoding-aware escaping routine,
then puts quotes around it, the result will be
	' 0x95 0x5c \ ' ' SQL text '
because the escaping routine will double both the "backslash" and the
quote.  The backend will see this as 0x95 0x5c, escaped quote, quote,
and injected SQL text.

Variants of this attack can succeed even in ASCII-safe encodings such as
UTF8, if the client and server are sloppy about encoding considerations.
Since UTF8 is a popular operating encoding for web servers, this makes the
problem quite urgent.

The Common Vulnerabilities and Exposures (CVE) project has assigned the
name CVE-2006-2313 for the variant that affects all multibyte encodings,
and CVE-2006-2314 for the case that affects only non-ASCII-safe encodings
(which includes SJIS and possibly some other Far Eastern encodings).

A proper fix requires the client-side escaping routine to be aware of the
encoding in use, so that it can avoid escaping "characters" that are just
trailing bytes of multibyte characters.  Unfortunately, not only is our
recommended escaping subroutine PQescapeString not encoding-aware, but
neither are any of the commonly used substitutes (such as PHP's addslashes
function).  This means there are a lot of vulnerable clients out there and
a short-term fix on the client side is likely to be impractical.  We have
therefore adopted a two-pronged response: not only provide a repaired
version of PQescapeString, but also modify the PostgreSQL server so that
it will detect and reject queries in which SQL injection could have
occurred through non-encoding-aware escaping.

Server changes:

It turns out that in ASCII-safe encodings, it is sufficient for the server
to reject strings containing any invalidly encoded multibyte characters.
In non-ASCII-safe encodings, it is necessary to both reject invalidly
encoded characters, and to reject uses of "\'" to represent a single quote
mark in a SQL string literal.  (The example above shows why: by every
other test that the server could use, the string is legal.)  "\'" has been
deprecated for some time in favor of the SQL-standard equivalent "''"
(that is, two single-quote marks), but we are aware that many applications
will still generate "\'".  We have therefore added a server parameter that
can be set to control how strict the server is about "\'":

backslash_quote = on		Allow \' always (old behavior; INSECURE)
backslash_quote = off		Reject \' always
backslash_quote = safe_encoding	Allow \' if client_encoding is ASCII-safe

"safe_encoding" is the new default, because many practical applications
operate with ASCII-safe encodings and therefore need not be forced to
change immediately.  "on" can be used when absolutely necessary for
application compatibility, but it is unsafe to use with clients exposed
to untrusted input.

The changes to check encoding validity do not have any comparable "off
switch"; the server will now unconditionally reject text that is not
correctly encoded according to the specified client_encoding.  If you need
to store data that is not in any recognized encoding, we recommend using
SQL_ASCII encoding on both ends of the connection.

libpq changes:

Since PQescapeString doesn't have any parameter for client encoding, there
isn't any clean way to get it to act correctly.  We have added a new
function PQescapeStringConn that has an API similar to PQescapeString but
also takes a PGconn parameter for the connection that the string will be
sent to.  This allows it to obtain the correct encoding from the
connection data structure.  We have also taken this opportunity to
future-proof PQescapeStringConn by teaching it to escape correctly for
either setting of standard_conforming_strings, which it can also find out
from the connection structure.  This will prevent security issues with
future PostgreSQL servers that default to SQL-spec-compliant backslash
handling.  (There is also a new function PQescapeByteaConn, which likewise
is recommended over PQescapeBytea.  There are no encoding issues for
escaping bytea values, but standard_conforming_strings does affect it, so
this is a good time to fix both functions.)

To maintain some semblance of correct functionality for PQescapeString and
PQescapeBytea, libpq has been changed to store the client encoding and
standard_conforming_strings settings into static variables whenever they
are received from the server (ie, at connection start or when changed
on-the-fly via SET).  The two older functions use these static variables
instead of a connection structure to determine what to do.  Therefore,
they will work completely correctly in an application that has only one
active connection at a time, or whose connections all use the same
encoding and standard_conforming_strings settings.  However, in a program
that has multiple connections with different parameters, PQescapeString
and PQescapeBytea must be considered to be security hazards --- they
should be replaced by PQescapeStringConn and PQescapeByteaConn as soon as
possible.

Required client changes:

Aside from adopting PQescapeStringConn and PQescapeByteaConn for quoting
text strings, applications should check to see if they are using "\'" to
represent quote marks, and change to the SQL-standard spelling "''"
instead.  This is not urgent but will make the application portable for
use with non-ASCII-safe encodings.