Re: [INTERFACES] escaping characters ...

Vince and I are working on the UdmSearch engine for the web site, and keep
coming across minor "nits"...basically, it was originally designed under
MySQL and they've done preliminary porting to PostgreSQL, but its missing
some things :(

Comment from one of the developers:

===================
The question for PostgreSQL users.
MySQl has 'mysql_escape_string' in it's client library. Does Pg have
something like that? Sorry, we are not enough expirienced with
PostgreSQL.
===================

I've looked through our docs, and find nothing that appears similar ... do
we have something like this in our client library tht I'm not seeing?

if not, what should be escaped? The only thing that comes to mind is '
...

thanks...

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org

The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> MySQl has 'mysql_escape_string' in it's client library. Does Pg have
> something like that?

libpq doesn't provide any such function --- perhaps it should, but
on the other hand the amount of code involved is pretty tiny, and
issues like memory allocation/freeing would complicate the definition
of the function.

> if not, what should be escaped?

When generating quoted strings for use in SQL commands, you should
prefix single-quote (') and backslash (\) characters with a backslash.
I think that's all.

COPY IN/OUT data has a different set of rules. There, you can but
don't have to backslash single quotes. You do need to convert returns
and tabs into \n and \t, and of course backslash itself must be doubled.
(If you are using some other character than tab as the field delimiter,
then it'd need backslashing instead.)

Data returned by libpq after a SELECT is not quoted at all.

regards, tom lane

What about: % and ? for like clauses... I think the function in question
would be a handy option though. I almost always implement one of these
methods in the application code.

Regards,
Joe Shevland

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "The Hermit Hacker" <scrappy(at)hub(dot)org>
Cc: <pgsql-interfaces(at)postgreSQL(dot)org>
Sent: Tuesday, January 04, 2000 10:12 AM
Subject: Re: [INTERFACES] escaping characters ...

> The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> > MySQl has 'mysql_escape_string' in it's client library. Does Pg have
> > something like that?
>
> libpq doesn't provide any such function --- perhaps it should, but
> on the other hand the amount of code involved is pretty tiny, and
> issues like memory allocation/freeing would complicate the definition
> of the function.
>
> > if not, what should be escaped?
>
> When generating quoted strings for use in SQL commands, you should
> prefix single-quote (') and backslash (\) characters with a backslash.
> I think that's all.
>
> COPY IN/OUT data has a different set of rules. There, you can but
> don't have to backslash single quotes. You do need to convert returns
> and tabs into \n and \t, and of course backslash itself must be doubled.
> (If you are using some other character than tab as the field delimiter,
> then it'd need backslashing instead.)
>
> Data returned by libpq after a SELECT is not quoted at all.
>
> regards, tom lane
>
> ************
>