Обсуждение: Effectiveness of pg_escape_string at blocking SQL injection attacks

Folks,

The php mysql api has a function "mysql_real_escape_string" that seems
to be able to thwart known SQL injection attacks -- at least the ones of
which I and other people I've discussed this with know.  I am curious to
know if pg_escape_string is as effective.  If not, what would need to be
modified to make it more effective?

(there is a possibility that I may be able to get a grad student to work
  on this at the center, so detailed responses would be appreciated.)

Thanks!

--
Ed Finkler
Web and Security Archive Administrator
CERIAS - Purdue University
http://www.cerias.purdue.edu/
v: 765.496.6762  f: 764.496.3181

On Fri, May 27, 2005 at 10:57:16 -0500,
  Ed Finkler <coj@cerias.purdue.edu> wrote:
> Folks,
>
> The php mysql api has a function "mysql_real_escape_string" that seems
> to be able to thwart known SQL injection attacks -- at least the ones of
> which I and other people I've discussed this with know.  I am curious to
> know if pg_escape_string is as effective.  If not, what would need to be
> modified to make it more effective?
>
> (there is a possibility that I may be able to get a grad student to work
>  on this at the center, so detailed responses would be appreciated.)

The best advice is to use bind parameters rather than trying to build
SQL strings consisting partly of user input.

Bruno Wolff III wrote:

> The best advice is to use bind parameters rather than trying to build
> SQL strings consisting partly of user input.

That's good advice, but I suspect not everyone is going to know this,
and will have a tendency to use the escaping function to try and clean
intput.  Do you have any suggestions about improving the security of the
pg_escape_string function?

--
Ed Finkler
Web and Security Archive Administrator
CERIAS - Purdue University
http://www.cerias.purdue.edu/
v: 765.496.6762  f: 764.496.3181

Hi,

On 5/27/05, Ed Finkler <coj@cerias.purdue.edu> wrote:
> The php mysql api has a function "mysql_real_escape_string" that seems
> to be able to thwart known SQL injection attacks -- at least the ones of
> which I and other people I've discussed this with know. I am curious to
> know if pg_escape_string is as effective. If not, what would need to be
> modified to make it more effective?

Both of pg_escape_string() and pg_escape_bytea() is a interface to
their libpq equivalents (PQescapeString() and PQescapeBytea()). From
this point of view, above question turns into "Do PQescapeString() and
PQescapeBytea() functions have enough effectiveness to be able to
thwart known SQL injection attacks?" form.

I'm not an SQL expert, so folks will help you about above libpq
functions and their effectiveness. But if I'd summarize the PHP side
of it:

In the PHP side, they obeyed the rules mentioned in libpq
documentation [1] (like required minimum size to be allocated.) Thus,
I couldn't figure out any missed point in the pg_escape_string(),
pg_escape_bytea() [2] functions.

[1] http://www.postgresql.org/docs/8.0/interactive/libpq-exec.html
[2] http://cvs.php.net/co.php/php-src/ext/pgsql/pgsql.c?r=1.327

When I traced the related libpq source code for escape routines, I met
with following replacements: (I'm not sure if they're enough to thwart
known SQL injection attacks.)

PQescapeBytea()
  \0 -> \\000
  \' -> \'
  \\ -> \\\\
  Chars between 0x20 - 0x7E -> Their octal equivalents \\VYZ

PQescapeString()
  ' -> ''
  \ -> \\

If you think, they're not enough for SQL-Injection attacks, I'd advice
you to patch libpq code, not PHP.

Hope this helps.
Best regards.

Volkan YAZICI wrote:

[snip]

> If you think, they're not enough for SQL-Injection attacks, I'd advice
> you to patch libpq code, not PHP.

This is very helpful information.  My initial thinking is that this
wouldn't be effective at catching SQL injections, but I'll need to
bounce this off a few other folks.

Thanks!

--
Ed Finkler
Web and Security Archive Administrator
CERIAS - Purdue University
http://www.cerias.purdue.edu/
v: 765.496.6762  f: 764.496.3181

On Fri, 2005-05-27 at 11:33 -0500, Ed Finkler wrote:
> Volkan YAZICI wrote:
>
> [snip]
>
> > If you think, they're not enough for SQL-Injection attacks, I'd advice
> > you to patch libpq code, not PHP.
>
> This is very helpful information.  My initial thinking is that this
> wouldn't be effective at catching SQL injections, but I'll need to
> bounce this off a few other folks.

Given the modus operandi of an SQL inject attack, this should be
perfectly effective at stopping them.

As Bruno said, however, the "bind parameters" approach is a better
approach in general.

Cheers,
                    Andrew McMillan.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267

Hi,

On 5/27/05, Ed Finkler <coj@cerias.purdue.edu> wrote:
> The php mysql api has a function "mysql_real_escape_string" that seems
> to be able to thwart known SQL injection attacks -- at least the ones of
> which I and other people I've discussed this with know.  I am curious to
> know if pg_escape_string is as effective.  If not, what would need to be
> modified to make it more effective?

I didn't hang around and began to trace mysql.com for a CVS
repository. After a five minutes of search, I found that MySQL uses
BitKeeper [1]. Now next thing to do is to find where
mysql_real_escape_string() [2] is defined in. And the result is:
mysys/charset.c [3]

[1] http://mysql.bkbits.net:8080/mysql-5.0/
[2] There're two functions defined in PHP for escaping MySQL statements:
    mysql_escape_string() and mysql_real_escape_string(). When I looked at the
    MySQL source code, I found that, both of 'em refers to
    escape_string_for_mysql() function. (As I saw, there's not any
escape routine
    specialized for binary data.)
[3] http://mysql.bkbits.net:8080/mysql-5.0/anno/mysys/charset.c@1.137

I found below replacements in escape_string_for_mysql() function:

/* {{{ Code snippet */

#ifdef USE_MB
/*
 If the next character appears to begin a multi-byte character, we
 escape that first byte of that apparent multi-byte character. (The
 character just looks like a multi-byte character -- if it were actually
 a multi-byte character, it would have been passed through in the test
 above.)
 Without this check, we can create a problem by converting an invalid
 multi-byte character into a valid one. For example, 0xbf27 is not
 a valid GBK character, but 0xbf5c is. (0x27 = ', 0x5c = \)
*/
if (use_mb_flag && (l= my_mbcharlen(charset_info, *from)) > 1)
{
  *to++= '\\';
  *to++= *from;
  continue;
}

/*
 [GBK: Encoding standard for Simplified Chinese, used in the People's
 Republic of China and in Singapore.]
*/
#endif

switch (*from) {
case 0: /* Must be escaped for 'mysql' */
 *to++= '\\';
 *to++= '0';
 break;
case '\n': /* Must be escaped for logs */
 *to++= '\\';
 *to++= 'n';
 break;
case '\r':
 *to++= '\\';
 *to++= 'r';
 break;
case '\\':
 *to++= '\\';
 *to++= '\\';
 break;
case '\'':
 *to++= '\\';
 *to++= '\'';
 break;
case '"': /* Better safe than sorry */
 *to++= '\\';
 *to++= '"';
 break;
case '\032': /* This gives problems on Win32 */
 *to++= '\\';
 *to++= 'Z';
 break;
default:
 *to++= *from;
}

/* }}} */

As I saw, MySQL follows different methods to escape input data. But
AFAIC, the method followed depends a little bit on the software
architecture, like handling literals from pointers. Thus, it shouldn't
be an objective comparision when we just look at "which one escapes
which char".

Anyway, I hope above escaping differences helps folks.

I also want to add something about using parameters instead of
escaping. There're lots of advantages of using parameters when
compared to escaping:
o. You just pass the value and don't need to worry about any
SQL-Injection attack.
o. When you try to escape a data, CPU usage increases with linear
proportional to data length. (Try escaping an MP3 file.)
o. As far as I understand from the length of this post's thread, you
can never be sure about the safety of any escaping method.

But as Ed Finkler underlined, most of the programmers prefer escaping
while sending SQL statements. So "parameter usage is more secure"
shouldn't be an answer for "are our escaping routines enough"
question.

Regards.