Re: [JDBC] Re: Escape Processing problems
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 }', {t '12:12:12'})
insert into test values ( '2000-12-01', 'string of\\ \' {d }', {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
[EMAIL PROTECTED] - 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 [EMAIL PROTECTED])
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] Re: Escape Processing problems
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 }', {t '12:12:12'})
insert into test values ( '2000-12-01', 'string of\\ \' {d }', {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
[EMAIL PROTECTED] - 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 [EMAIL PROTECTED])
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [JDBC] Re: Escape Processing problems
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 }', {t '12:12:12'})
insert into test values ( '2000-12-01', 'string of\\ \' {d }', {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
[EMAIL PROTECTED] - 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 [EMAIL PROTECTED])
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [JDBC] Re: Escape Processing problems
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] Re: Escape Processing problems
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
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[JDBC] 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 }', {t '12:12:12'})
insert into test values ( '2000-12-01', 'string of\\ \' {d }', {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 '-mm-dd'} and a=1
should be changed to:
SELECT a, b from c where t='-mm-dd' and a=1
and something like
INSERT INTO test VALUES('don't change this {d '-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
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
public class esc
{
private static final short CODE = 0;
private static final short STRING = 1;
private static final short BACKSLASH =2;
private static final short ESC_DATE = 3;
public static String EscapeSQL(String sql)
{
// look through the sql statement for escape codes. Since escape
// codes can only appear in SQL CODE, we keep track of if we are
// inside or outside a string.
[JDBC] Re: Escape Processing problems
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 '-mm-dd'} and a=1
should be changed to:
SELECT a, b from c where t='-mm-dd' and a=1
and something like
INSERT INTO test VALUES('don't change this {d '-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
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] Re: Escape Processing problems
On Wed, Aug 29, 2001 at 09:18:26AM +0900, Thomas O'Dowd wrote:
INSERT INTO test VALUES('don't change this {d '-mm-dd'} as its correct.
Opps. I didn't backslash the single quotes here but you know what I mean...
Tom.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[JDBC] Re: Escape Processing problems
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 '-mm-dd'} and a=1
should be changed to:
SELECT a, b from c where t='-mm-dd' and a=1
and something like
INSERT INTO test VALUES('don't change this {d '-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.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
