Re: Problem with character ' (single quote) in text fields

Einar Karttunen wrote:
>
> On 21.01.02 11:42 +0100(+0000), Jordi wrote:
> > Is there any way to change the start/end of Text field character or do you
> > know a better workaround to avoid parsing/changing all the text fields just
> > in case they contain the single quote ( ') character??.
> >
>
> In case the data comes from outside you *must* escape it. Libpq defines a
> function for it called PQescapeString. Consider what would happen if you
> had code like this:
>
> char buf[BUF_SIZE];
> snprintf(buf,BUF_SIZE,"SELECT * FROM mytable WHERE field='%s';",string_from_user);
> PQexec(con,buf);
>
> now lets say the user would enter a value like
> 0';DELETE FROM mytable;SELECT '1
> and the db would see
> SELECT * FROM mytable WHERE field='0';DELETE FROM mytable;SELECT '1';
>
> - Einar Karttunen

If I recall correctly, this was a security bug in older versions of the
contributed Apache module, mod_auth_pgsql. It would be nice to have a
configuration option (GUC) to disallow the submission of multiple
statements in a single Query cycle in the FE/BE protocol, just as an
extra security option. I'm not sure if any of the standard tools (i.e.
pg_dump, psql) use multiple statements per query, though. And, of
course, its no substitute for fixing the actual problem. But imagine if
there was standard protection against buffer overflows...

Just a thought,

Mike Mascari
mascarm(at)mascari(dot)com