SQL "scrubber" tool in Java

[JL Smith]

Hello,

I'm trying to find an sql tool in java that "scrubs" my SQL for single
quotes and replaces it with two single quotes. For example, if a
field name in the SQL statement is "O'Conor", I need to be able to
scrub that entire SQL statement and replace that appostrophe with two
appostrophe's, since that is the correct syntax for adding an
appostrophe. Thanks for any help!

Lee

 

[Thomas Kellerer]

Hello,

I'm trying to find an sql tool in java that "scrubs" my SQL for single
quotes and replaces it with two single quotes. For example, if a
field name in the SQL statement is "O'Conor", I need to be able to
scrub that entire SQL statement and replace that appostrophe with two
appostrophe's, since that is the correct syntax for adding an
appostrophe. Thanks for any help!

Lee

Use a prepared statement instead. It will take care of dates as well.

Thomas

 

[Dave Glasser]

(e-mail address removed) (JL Smith) wrote on 7 Jul 2003 07:52:03 -0700 in
comp.lang.java.programmer:

Hello,

I'm trying to find an sql tool in java that "scrubs" my SQL for single
quotes and replaces it with two single quotes. For example, if a
field name in the SQL statement is "O'Conor", I need to be able to
scrub that entire SQL statement and replace that appostrophe with two
appostrophe's, since that is the correct syntax for adding an
appostrophe. Thanks for any help!

public static String escape(String s) {
if(s.indexOf('\'') < 0) return s;
StringBuffer buffer = new StringBuffer(s.length() + 10);
char[] characters = s.toCharArray();
for(int j=0; j<characters.length; j++) {
if(characters[j] == '\'') buffer.append('\'');
buffer.append(characters[j]);
}
return buffer.toString();
}

 

[Dave Glasser]

(e-mail address removed) (JL Smith) wrote on 7 Jul 2003 07:52:03 -0700 in
comp.lang.java.programmer:

Hello,

I'm trying to find an sql tool in java that "scrubs" my SQL for single
quotes and replaces it with two single quotes. For example, if a
field name in the SQL statement is "O'Conor", I need to be able to
scrub that entire SQL statement and replace that appostrophe with two
appostrophe's, since that is the correct syntax for adding an
appostrophe. Thanks for any help!

public static String escape(String s) {
if(s.indexOf('\'') < 0) return s;
StringBuffer buffer = new StringBuffer(s.length() + 10);
char[] characters = s.toCharArray();
for(int j=0; j<characters.length; j++) {
if(characters[j] == '\'') buffer.append('\'');
buffer.append(characters[j]);
}
return buffer.toString();
}

Re-reading your post, I see you want something that will process an
entire SQL statement, while the method above will only do a single
field. (You'd have to scrub each field value as you assemble the
statement.)

Anyway, I don't think you're going to find what you're looking for,
because in order to do that, you'd have to parse the SQL statement
into its tokens, and that would be very difficult (if not impossible)
to do if there were unescaped embedded apostrophes in any field
values.

Even an ISQL tool like Oracle's SQL*Plus will choke if you type in a
statement that contains something like "last_name = 'O'connor'". If
you're trying to implement a design that includes the requirement you
describe above, I would suggest you change your design.

 

[JL Smith]

Thanks for the help Dave...I've decided that making one big parse utility
would be outrageous because of all the different patterns in an SQL
statement. So...I'm basically going to do something similar to what you
first described and just pass those fields to my scrubber individually. I
really only need it for text fields where I know the user enters a string
from the html pages, like a name or whatever...at least for now anyway.
Thanks again for the help.

Lee

(e-mail address removed) (JL Smith) wrote on 7 Jul 2003 07:52:03 -0700 in
comp.lang.java.programmer:

Hello,

I'm trying to find an sql tool in java that "scrubs" my SQL for single
quotes and replaces it with two single quotes. For example, if a
field name in the SQL statement is "O'Conor", I need to be able to
scrub that entire SQL statement and replace that appostrophe with two
appostrophe's, since that is the correct syntax for adding an
appostrophe. Thanks for any help!

public static String escape(String s) {
if(s.indexOf('\'') < 0) return s;
StringBuffer buffer = new StringBuffer(s.length() + 10);
char[] characters = s.toCharArray();
for(int j=0; j<characters.length; j++) {
if(characters[j] == '\'') buffer.append('\'');
buffer.append(characters[j]);
}
return buffer.toString();
}

Re-reading your post, I see you want something that will process an
entire SQL statement, while the method above will only do a single
field. (You'd have to scrub each field value as you assemble the
statement.)

Anyway, I don't think you're going to find what you're looking for,
because in order to do that, you'd have to parse the SQL statement
into its tokens, and that would be very difficult (if not impossible)
to do if there were unescaped embedded apostrophes in any field
values.

Even an ISQL tool like Oracle's SQL*Plus will choke if you type in a
statement that contains something like "last_name = 'O'connor'". If
you're trying to implement a design that includes the requirement you
describe above, I would suggest you change your design.


----
Check out QueryForm, a free, open source, Java/Swing-based
front end for relational databases.

http://qform.sourceforge.net