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

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