|
|
|
Escaping strings in SQL queries
A code insertion vulnerability has been discovered in many applications
using PostgreSQL. This page describes the problem and provides a patch
for libpq which provides a function for safely escaping
strings in PostgreSQL queries.
The Problem
Many programms which use PostgreSQL were found to have design flaws
that made them vulnerable to string insertion attacks. Developers do
not check whether certain characters are
allowed or not. Usually they recieve a string entered by an user (which
is e.g. used as the parameter for an SQL WHERE clause),
and pass it to
snprintf
to create a query which fits their needs. Then this query is
executed. This handling has a major problem: Since the user has access
to the string which is passed directly to PostgreSQL, he can take
modifications on the database or make the original SELECT
statement appear to return other data.
(See RUS-CERT Advisory 2001-08:01
for a description of typical security-related consequences.)
The Solution
Our proposed solution is providing a function which substitutes such
special characters by proper escape sequences. As a database developer
one might question the reasons for providing this funcion with the
database. We think that many developers do not want to take care of this
themselves because this would make them responsible for finding out
which characters are special. Another point is that porting to another
databse would be more difficult without such a function offered by the
library. In our experience programs, designed for databases offering
such a function, are usually not vulnerable.
The patch below includes a new function, PQescapeString,
together with the following documentation snippet:
Escaping strings for inclusion in SQL queries
size_t PQescapeString (char *to, const char *from, size_t length);
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.
PQescapeString performs this operation. The
from points to the first character of the string which
is to be escaped, and the length parameter counts the
number of characters in this string (a terminating NUL character is
neither necessary nor counted). to shall point to a
buffer which is able to hold at least one more character than twice
the value of length, otherwise the behavior is
undefined. A call to PQescapeString writes an escaped
version of the from string to the 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.
PQescapeString returns the number of characters written
to to, not including the terminating NUL character.
Behavior is undefined when the to and from
strings overlap.
The Patch
The patch is relative to the PostgreSQL CVS trunk of 2001-09-04 and
covers the client library libpq and its documentation.
You can use the function contained in this patch in your own application
until it is included in the official PostgreSQL distribution. In this
case, don't forget to rename the function PQescapeString,
so that no problems arise if someone tries to compile your application
with a later version of the PostgreSQL client library.
Please note that a previous version of the patch claimed that
PQescapeString worked for identifiers, too. This is not
true. Since the PostgreSQL parser does not deal correctly with quoted
double quotes at the moment, and there are concerns that PostgreSQL
will cope well with such identifiers, we suggest to avoid such
identifiers in the first place (and use only more-or-less hard-coded
identifiers from trustworthy sources such as configuration files).
|
|
