Skip site navigation (1) Skip section navigation (2)

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

From: Tobias Thierer <t_thierer(at)yahoo(dot)de>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: escape string for pgsql (using jdbc/java)?
Date: 2007-01-28 01:25:40
Message-ID: epgu2j$2jrk$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2007-01-28 02:18:31
Subject: Re: escape string for pgsql (using jdbc/java)?
Previous:From: Kris JurkaDate: 2007-01-27 04:57:59
Subject: Re: Synthesize support for Statement.getGeneratedKeys()?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group