Effectiveness of pg_escape_string at blocking SQL injection attacks
Effectiveness of pg_escape_string at blocking SQL injection attacks
От:
Ed Finkler <coj@cerias.purdue.edu>
Дата:
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
Re: Effectiveness of pg_escape_string at blocking SQL injection attacks
От:
Bruno Wolff III <bruno@wolff.to>
Дата:
On Fri, May 27, 2005 at 10:57:16 -0500, Ed Finkler 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.
Re: Effectiveness of pg_escape_string at blocking SQL injection attacks
От:
Ed Finkler <coj@cerias.purdue.edu>
Дата:
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
Re: Effectiveness of pg_escape_string at blocking SQL injection attacks
От:
Volkan YAZICI <volkan.yazici@gmail.com>
Дата:
Hi, On 5/27/05, Ed Finkler 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.
Re: Effectiveness of pg_escape_string at blocking SQL injection
От:
Ed Finkler <coj@cerias.purdue.edu>
Дата:
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
Re: Effectiveness of pg_escape_string at blocking SQL
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
Re: Effectiveness of pg_escape_string at blocking SQL injection attacks
От:
Volkan YAZICI <volkan.yazici@gmail.com>
Дата:
Hi,
On 5/27/05, Ed Finkler 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.