Re: Escape Processing problems - Mailing list pgsql-jdbc

Hi Barry,

I found the part in the spec that talks about escape processing for
date and time. Thanks for pointing that out. I believe the drivers
implementation is wrong as it is a) changing random text data instead
of data of a defined format to its escape sequence and b) it can throw
a out of bounds exception if there is no closing }.

Perhaps, I'll write a patch later in the day to fix this for at least
the date escape as that is the only one that is implemented.

So just to clarify my understanding of what should happen...

"SELECT a, b from c where t={d 'yyyy-mm-dd'} and a=1"

should be changed to:

"SELECT a, b from c where t='yyyy-mm-dd' and a=1"

and something like

"INSERT INTO test VALUES('don't change this {d 'yyyy-mm-dd'} as its correct. "

should be left alone. ie, if we're in a string escape processing should
not be done. Right now it looks for anything with {d in the query and
starts changing it.

Cheers,

Tom.

On Tue, Aug 28, 2001 at 12:55:19PM -0700, Barry Lind wrote:
> Thomas,
>
> This is doing exactly what it is supposed to according to the JDBC Spec.
>   In fact there are a bunch of other '{X  }'  things that the Spec
> defines that it should also be handling.
>
> thanks,
> --Barry
>
> Thomas O'Dowd wrote:
> > Hi all,
> >
> > The Connection.EscapeSQL() routine is broken IMHO . Actually, I'm not
> > sure why it is trying to fix strings starting with "{d" in the first place?
> >
> > Anyway, currently I've turned it off in the statement with
> > setEscapeProcessing(false)
> >
> > The problem I'm having is that "{d" appears in the data that I'm trying
> > to store and its not a date. So data like the following...
> >
> > .....blahhh}; {blahhh }; {docs=""};
> >
> > is turning into...
> >
> > .....blahhh}; {blahhh };   ocs="" ;
> >                          ^^      ^
> >
> > What's more is if I have something like "{d....." and there is no ending
> > brace, it will throw a StringIndexOutOfBoundsException as the return
> > value of the indexOf() looking for the closing brace will not find one
> > and thus setCharAt() will use an illegal index of -1 :(
> >
> > The routine is below for reference... Can anyone explain why it is trying
> > to do this on me in the first place. I would think escape processing would
> > do something a little different like watching my single quotes etc.
> >
> >     public String EscapeSQL(String sql) {
> >       //if (DEBUG) { System.out.println ("parseSQLEscapes called"); }
> >
> >       // If we find a "{d", assume we have a date escape.
> >       //
> >       // Since the date escape syntax is very close to the
> >       // native Postgres date format, we just remove the escape
> >       // delimiters.
> >       //
> >       // This implementation could use some optimization, but it has
> >       // worked in practice for two years of solid use.
> >       int index = sql.indexOf("{d");
> >       while (index != -1) {
> >         //System.out.println ("escape found at index: " + index);
> >         StringBuffer buf = new StringBuffer(sql);
> >         buf.setCharAt(index, ' ');
> >         buf.setCharAt(index + 1, ' ');
> >         buf.setCharAt(sql.indexOf('}', index), ' ');
> >         sql = new String(buf);
> >         index = sql.indexOf("{d");
> >       }
> >       //System.out.println ("modified SQL: " + sql);
> >       return sql;
> >     }
> >
> > Cheers,
> >
> > Tom.
> >
>
>

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs