Re: Re: Escape Processing problems

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

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

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

On Wed, Aug 29, 2001 at 09:18:26AM +0900, Thomas O'Dowd wrote:
> "INSERT INTO test VALUES('don't change this {d 'yyyy-mm-dd'} as its correct. "

Opps. I didn't backslash the single quotes here but you know what I mean...

Tom.

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

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

Hmmm, after a little more testing, I fixed a problem with backslashes.
New code attached.

Tom.

On Thu, Aug 30, 2001 at 11:46:16AM +0900, Thomas O'Dowd wrote:
> 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.
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

On Thu, 30 Aug 2001 11:46:16 +0900, Thomas O'Dowd wrote:
>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.

To what extent is this implementation JDBC compliant?

The spec is in section 40.1.5 of the "JDBC API Tutorial and
Reference", 2nd edition. Its also documented on
http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec/jdbc-spec.frame11.html#7105.

Regards,
René Pijlman <rene(at)lab(dot)applinet(dot)nl>

On Sat, Sep 01, 2001 at 11:02:47PM +0200, Rene Pijlman wrote:
> On Thu, 30 Aug 2001 11:46:16 +0900, Thomas O'Dowd wrote:
> >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.
>
> To what extent is this implementation JDBC compliant?
>
> The spec is in section 40.1.5 of the "JDBC API Tutorial and
> Reference", 2nd edition. Its also documented on
> http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec/jdbc-spec.frame11.html#7105.

I think it is as compliant as what's in the current driver. It just
solves the corruption problem with some strings containing {d. If you
want to support {t and {ts then that should also be easily added. As
for supporting the other escapes sequences mentioned in section 11, I'm
not familar with the usage enough to comment on how it should be implemented.

My interest in patching this method is to solve the current data
corruption problems that I'm getting with the current implementation.

Tom.
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

> Hmmm, after a little more testing, I fixed a problem with backslashes.
> New code attached.
>
> Tom.
>
> On Thu, Aug 30, 2001 at 11:46:16AM +0900, Thomas O'Dowd wrote:
> > 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.
> --
> Thomas O'Dowd. - Nooping - http://nooper.com
> tom(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Hi Bruce,

I guess that wasn't exactly a patch that I sent in but rather a test
program with the new routine embeded so that other people could try
it out easily.

I'll mail in the patch later. I'm a little distracted right now with
something else. For the moment just delete the patch that you added
to the unapplied list.

Cheers,

Tom.

On Tue, Sep 04, 2001 at 10:57:10AM -0400, Bruce Momjian wrote:
>
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
> http://candle.pha.pa.us/cgi-bin/pgpatches
>
> I will try to apply it within the next 48 hours.
>
> > Hmmm, after a little more testing, I fixed a problem with backslashes.
> > New code attached.
> >
> > Tom.
> >
> > On Thu, Aug 30, 2001 at 11:46:16AM +0900, Thomas O'Dowd wrote:
> > > 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.
> > --
> > Thomas O'Dowd. - Nooping - http://nooper.com
> > tom(at)nooper(dot)com - Testing - http://nooper.co.jp/labs
>
> [ Attachment, skipping... ]
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

Patch removed at request of author. Will resubmit.

> Hmmm, after a little more testing, I fixed a problem with backslashes.
> New code attached.
>
> Tom.
>
> On Thu, Aug 30, 2001 at 11:46:16AM +0900, Thomas O'Dowd wrote:
> > 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.
> --
> Thomas O'Dowd. - Nooping - http://nooper.com
> tom(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Hi all,

I'm attaching a patch which fixes the corruption in strings caused
by escape processing in the SQL statement. I've tested this for a
while now and it appears to work well. Previously string data
with {d was getting corrupt as the {d was being stripped regardless
of whether it was an escape code or not.

I also added checking for time and timestamp escape processing strings
as per 11.3 in the specification. The patch is against the latest
CVS.

Cheers,

Tom.

Hi all,

Wonder if anyone had a look at this patch yet? I've been using my locally
patched version for a while now and it works fine. Again just to make it
clear what it does...

- It fixes an Exception which will be thrown with the following insert.

insert into bbbb values ('xxxx{d');

- The current statement implementation also corrupts string data with '{d'
characters by removing them. This patch fixes this.

- The patch also adds support for {t and {ts escapes which are covered
in section 11.3 of the specification. Does anyone on the list use
these types of escapes???

Right now I'm mostly after fixing the corruption of strings as a lot of
my data contains the "{d" character combination and its quite annoying.

For those of you who don't know, the escapeSQL() method is called
everytime you execute a Statement or PreparedStatement if setEscapeProcessing
is set true (which is the default).

Tom.

On Mon, Sep 10, 2001 at 05:27:17PM +0900, Thomas O'Dowd wrote:
> Hi all,
>
> I'm attaching a patch which fixes the corruption in strings caused
> by escape processing in the SQL statement. I've tested this for a
> while now and it appears to work well. Previously string data
> with {d was getting corrupt as the {d was being stripped regardless
> of whether it was an escape code or not.
>
> I also added checking for time and timestamp escape processing strings
> as per 11.3 in the specification. The patch is against the latest
> CVS.
>
> Cheers,
>
> Tom.

> Index: org/postgresql/Statement.java
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Statement.java,v
> retrieving revision 1.2
> diff -c -r1.2 Statement.java
> *** org/postgresql/Statement.java 2001/08/10 14:42:07 1.2
> --- org/postgresql/Statement.java 2001/09/10 08:16:48
> ***************
> *** 39,44 ****
> --- 39,49 ----
>
> protected boolean escapeProcessing = true;
>
> + // Static variables for parsing SQL when escapeProcessing is true.
> + private static final short IN_SQLCODE = 0;
> + private static final short IN_STRING = 1;
> + private static final short BACKSLASH =2;
> + private static final short ESC_TIMEDATE = 3;
>
> public Statement() {
> }
> ***************
> *** 226,251 ****
> }
>
> /**
> ! * This is an attempt to implement SQL Escape clauses
> ! */
> ! protected static String escapeSQL(String sql) {
> ! // 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) {
> ! 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");
> ! }
> ! return sql;
> }
> }
> --- 231,306 ----
> }
>
> /**
> ! * Filter the SQL string of Java SQL Escape clauses.
> ! *
> ! * Currently implemented Escape clauses are those mentioned in 11.3
> ! * in the specification. Basically we look through the sql string for
> ! * {d xxx}, {t xxx} or {ts xxx} in non-string sql code. When we find
> ! * them, we just strip the escape part leaving only the xxx part.
> ! * So, something like "select * from x where d={d '2001-10-09'}" would
> ! * return "select * from x where d= '2001-10-09'".
> ! */
> ! protected static String escapeSQL(String sql)
> ! {
> ! // Since escape codes can only appear in SQL CODE, we keep track
> ! // of if we enter a string or not.
> ! StringBuffer newsql = new StringBuffer();
> ! short state = IN_SQLCODE;
> !
> ! int i = -1;
> ! int len = sql.length();
> ! while(++i < len)
> ! {
> ! char c = sql.charAt(i);
> ! switch(state)
> ! {
> ! case IN_SQLCODE:
> ! if(c == '\'') // start of a string?
> ! state = IN_STRING;
> ! else if(c == '{') // start of an escape code?
> ! if(i+1 < len)
> ! {
> ! char next = sql.charAt(i+1);
> ! if(next == 'd')
> ! {
> ! state = ESC_TIMEDATE;
> ! i++;
> ! break;
> ! }
> ! else if(next == 't')
> ! {
> ! state = ESC_TIMEDATE;
> ! i += (i+2 < len && sql.charAt(i+2) == 's') ? 2 : 1;
> ! break;
> ! }
> ! }
> ! newsql.append(c);
> ! break;
> !
> ! case IN_STRING:
> ! if(c == '\'') // end of string?
> ! state = IN_SQLCODE;
> ! else if(c == '\\') // a backslash?
> ! state = BACKSLASH;
> !
> ! newsql.append(c);
> ! break;
> !
> ! case BACKSLASH:
> ! state = IN_STRING;
> !
> ! newsql.append(c);
> ! break;
> !
> ! case ESC_TIMEDATE:
> ! if(c == '}')
> ! state = IN_SQLCODE; // end of escape code.
> ! else
> ! newsql.append(c);
> ! break;
> ! } // end switch
> ! }
> !
> ! return newsql.toString();
> }
> }

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom(at)nooper(dot)com - Testing - http://nooper.co.jp/labs

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

> Hi all,
>
> I'm attaching a patch which fixes the corruption in strings caused
> by escape processing in the SQL statement. I've tested this for a
> while now and it appears to work well. Previously string data
> with {d was getting corrupt as the {d was being stripped regardless
> of whether it was an escape code or not.
>
> I also added checking for time and timestamp escape processing strings
> as per 11.3 in the specification. The patch is against the latest
> CVS.
>
> Cheers,
>
> Tom.

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Patch applied. Thanks.

> Hi all,
>
> I'm attaching a patch which fixes the corruption in strings caused
> by escape processing in the SQL statement. I've tested this for a
> while now and it appears to work well. Previously string data
> with {d was getting corrupt as the {d was being stripped regardless
> of whether it was an escape code or not.
>
> I also added checking for time and timestamp escape processing strings
> as per 11.3 in the specification. The patch is against the latest
> CVS.
>
> Cheers,
>
> Tom.

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026