Re: escape string for pgsql (using jdbc/java)?

On Fri, 26 Jan 2007, Tobias Thierer wrote:

> I was previously using MySQL and escaped strings following the document at:
>
> http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
>
> But I couldn't find a corresponding specification for pgsql.

http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html

See 4.1.2.1 String constants.

> 1.) Is there a built-in method somewhere in the jdbc driver that escapes
> strings and makes them safe to use in an SQL statement (inside a
> string)?

There is org.postgresql.core.Utils#appendEscapedString, but it's not
something we support or advertise. It's really for internal use only.

> 2.) Which characters do I need to escape for pgsql? Is ' the only one,
> and I need to escape it as '' ? Do I need to escape \ ? Will I need to
> escape all the characters that I escaped for MySQL? Where can I find
> out more?

You need to escape ' and \ if you standard_conforming_strings is on.
Monitoring this setting can be tough, so the safest thing to do is
probably to always use the E'string' escape syntax and escape both
characters.

Kris Jurka