Postgres Pro
Downloads
Home   >   mailing lists

Thread: String escaping?

String escaping?

From
David Nedrow
Date:
Are there any build-in JDK or JDBC (Sun or Postgresql driver)
functions for escaping strings before inserting/updating in the DB?

Eg., I'm using the following PreparedStatement...

addNickname = con.prepareStatement("insert into infobot.nicknames
(nickname, firstseen) values (?, to_timestamp(?))");

When providing the value via addNickname.setString(1, this.getString
()), is there some way for me to be sure that the contents of the
string I'm feeding to setString() are properly formed/escaped.

I'm basically looking for the Java equivalent of PHP's
pg_escape_string() function...

http://www.php.net/manual/en/function.pg-escape-string.php

I was hoping to avoid writing my own escape method, but may have to
as I have yet to find anything "off the shelf".

Any suggestions would be appreciated,

-David

Re: String escaping?

From
Heikki Linnakangas
Date:
David Nedrow wrote:
> Are there any build-in JDK or JDBC (Sun or Postgresql driver) functions
> for escaping strings before inserting/updating in the DB?
>
> Eg., I'm using the following PreparedStatement...

You don't generally need to escape your strings if you're using
PreparedStatements.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: String escaping?

From
Mark Lewis
Date:
The only exception to this rule is backslashes and (when using LIKE) the
'%' and '_' characters.  Although if you're running 8.2 and turn the
standard_conforming_strings setting ON then you don't need to worry
about backslashes.

-- Mark Lewis

On Wed, 2006-12-13 at 17:21 +0000, Heikki Linnakangas wrote:
> David Nedrow wrote:
> > Are there any build-in JDK or JDBC (Sun or Postgresql driver) functions
> > for escaping strings before inserting/updating in the DB?
> >
> > Eg., I'm using the following PreparedStatement...
>
> You don't generally need to escape your strings if you're using
> PreparedStatements.
>

Re: String escaping?

From
Markus Schaber
Date:
Hi, Mark,

Mark Lewis <mark.lewis@mir3.com> wrote:

> > You don't generally need to escape your strings if you're using
> > PreparedStatements.
> >
> The only exception to this rule is backslashes and (when using LIKE) the
> '%' and '_' characters.  Although if you're running 8.2 and turn the
> standard_conforming_strings setting ON then you don't need to worry
> about backslashes.

That sounds confusing.

I always thought that the Strings that I set with setString() don't
have to be escaped at all, the Driver will handle it transparently (by
either escaping for V2 protocol, or using BIND with the appropriate
encoding).

But, of course, when I have a String Literal in the source, I need to
add a layer of Java escaping for ", \, and some others.



Regards,
Markus


--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Re: String escaping?

From
Vit Timchishin
Date:
Markus Schaber wrote:
> Hi, Mark,
>
> Mark Lewis <mark.lewis@mir3.com> wrote:
>
>
>>> You don't generally need to escape your strings if you're using
>>> PreparedStatements.
>>>
>>>
>> The only exception to this rule is backslashes and (when using LIKE) the
>> '%' and '_' characters.  Although if you're running 8.2 and turn the
>> standard_conforming_strings setting ON then you don't need to worry
>> about backslashes.
>>
>
> That sounds confusing.
>
> I always thought that the Strings that I set with setString() don't
> have to be escaped at all, the Driver will handle it transparently (by
> either escaping for V2 protocol, or using BIND with the appropriate
> encoding).
>
> But, of course, when I have a String Literal in the source, I need to
> add a layer of Java escaping for ", \, and some others.
>
>
I suppose you've missed the main: "you need to escape only when you are
using LIKE".

Re: String escaping?

From
Markus Schaber
Date:
Hi, Vit,

Vit Timchishin <tivvpgsqljdbc@gtech-ua.com> wrote:

> > I always thought that the Strings that I set with setString() don't
> > have to be escaped at all, the Driver will handle it transparently (by
> > either escaping for V2 protocol, or using BIND with the appropriate
> > encoding).
> >
> > But, of course, when I have a String Literal in the source, I need to
> > add a layer of Java escaping for ", \, and some others.
> >
> >
> I suppose you've missed the main: "you need to escape only when you are
> using LIKE".

Yes, the LIKE specific escaping will stay there, but that layer is
independent of statement-level escaping.

What I wanted to show was: When you create your queries via String
concatenation, you have to implement the statement-level escaping
yourself, with prepared statements, the driver should completely handle
it.

That's independent of source-level escaping for String literals in
Java, and function-specific escaping inside the text for LIKE or
strings in function definitions.



Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org