Re: Escape Processing problems

Hi all,

I found some time this morning to write and test a new EscapeSQL() method.
I didn't make a patch for the driver yet as I'd like to hear some
comments. It's a tad longer than the original code as it only replaces
escape codes which appear in the SQL code and not inside strings.

It's attached as a separate java program which you can run to test with
various strings. Let me know if you think it is okay. It seems to work
with what I've tested it with.

Example:
$ /usr/local/java/jdk1.3/bin/java esc "insert into test values ({d '2000-12-01'}, 'string of\\ \' {d mmmm}', {t '12:12:12'})"
insert into test values ( '2000-12-01', 'string of\\ \' {d mmmm}', {t '12:12:12'})

Do you think we should expand it to handle the other codes like {t and {ts ?
The old routine only handles {d.

Tom.

On Tue, Aug 28, 2001 at 06:24:06PM -0700, Barry Lind wrote:
> Thomas,
>
> I can see where there might be bugs in the implementation of this
> escaping stuff. I don't think it is used very often. I believe your
> understanding of how this is supposed to work is correct.
>
> thanks,
> --Barry
>
>
> Thomas O'Dowd wrote:
> > 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(at)nooper(dot)com - Testing - http://nooper.co.jp/labs