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

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

On Sun, 28 Jan 2007, Tobias Thierer wrote:

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

Right, again this is really something just for the driver (which does know 
the setting of standard_conforming_strings.

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

I think the documentation I pointed you to earlier describers this 
(4.1.2.1 here 
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html).

You write WHERE x = E'foo' or x = E'foo''bar\\baz'.  The preceding E 
simply states that you want backslash to mean something special regardless 
of the setting of standard conforming strings.

Kris Jurka