Re: Escape Processing problems - Mailing list pgsql-jdbc

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.
>>>
>>>
>>
>