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

[Tobias Thierer <t_thierer(at)yahoo(dot)de>]

Kris Jurka wrote:

> >  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.

I dislike that this method expects me to tell it whether i have 
standard_conforming_strings set - this kinda defeats the "write once, run 
everywhere" principle.

If I replace \ with \\ and DO have standard_conforming_strings set, then 
this will actually create two \ characters in my string - right? So there is 
no way I can do this "safely".

> >  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.

I haven't found anything in the documentation about how this syntax works 
exactly. The documentation refers to "the E'...' syntax", but doesn't tell 
me what this syntax actually is (am I supposed to already know how this 
syntax works, so just need to be told to use it!?). Do I have to put the E 
in front of the beginning ', i.e.

   'foo'

becomes E'foo' ? (that can't be right, there must be some way I escape ' 
inside the string). So does 'foo' become

  'E'f'E'o'E'o'' ?

or what? How do I represent the literal string

  foo'bar\baz

?


Thanks in advance,

  Tobias