Re: Effectiveness of pg_escape_string at blocking SQL injection attacks

Hi,

On 5/27/05, Ed Finkler <coj(at)cerias(dot)purdue(dot)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.