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

[Kris Jurka <books(at)ejurka(dot)com>]

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