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

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