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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Effectiveness of pg_escape_string at blocking SQL injection attacks

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.ht ml
[2] http://cvs.php.net/co.php/php-src/ext/pgsql/pgsql.c?r=3D1.32 7

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: Effectiveness of pg_escape_string at blocking SQL injection

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Effectiveness of pg_escape_string at blocking SQL injection

--- 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.
>
> Thanks!

do let us all know what you find out.

bruno and all... what are bind parameters? how can i
avoid building sql from user input when my sql depends
on user input?

tia...



__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new Resources site
http://smallbusiness.yahoo.com/resources/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Effectiveness of pg_escape_string at blocking SQL injection

On Fri, May 27, 2005 at 10:25:52 -0700,
operationsengineer1@yahoo.com wrote:
>
> bruno and all... what are bind parameters? how can i
> avoid building sql from user input when my sql depends
> on user input?

You leave place holders in the SQL string to be replaced by parameters
passed separately. You don't need to do any escaping of the parameters
when passed this way.

Here is a snipet of perl code that does this:
$rows = $dbh->do(<<'EOF',
INSERT INTO detail (day, amount, comment, category, cat_type)
SELECT ?, ?, ?, id, cat_type FROM category WHERE id = ?
EOF
{}, param('day'), param('amount'), param('comment'), param('category'));

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Effectiveness of pg_escape_string at blocking SQL

--=-es6fBjKLI9gVHxZomDsp
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

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


--=-es6fBjKLI9gVHxZomDsp
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQBCl/sgjJA0f48GgBIRAk9eAJ9Q61dLhroPhXxaYErpn5sz4+xL0ACf Uwgc
rNtmcKgtTmvMtkY44MRCVow=
=BlLi
-----END PGP SIGNATURE-----

--=-es6fBjKLI9gVHxZomDsp--

Re: Effectiveness of pg_escape_string at blocking SQL injection attacks

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 =3D ', 0x5c =3D \)
*/
if (use_mb_flag && (l=3D my_mbcharlen(charset_info, *from)) > 1)
{=20
*to++=3D '\\';
*to++=3D *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++=3D '\\';
*to++=3D '0';
break;
case '\n': /* Must be escaped for logs */
*to++=3D '\\';
*to++=3D 'n';
break;
case '\r':
*to++=3D '\\';
*to++=3D 'r';
break;
case '\\':
*to++=3D '\\';
*to++=3D '\\';
break;
case '\'':
*to++=3D '\\';
*to++=3D '\'';
break;
case '"': /* Better safe than sorry */
*to++=3D '\\';
*to++=3D '"';
break;
case '\032': /* This gives problems on Win32 */
*to++=3D '\\';
*to++=3D 'Z';
break;
default:
*to++=3D *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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match