SQL "scrubber" tool in Java

Discussion in 'Java' started by JL Smith, Jul 7, 2003.

  1. JL Smith

    JL Smith Guest

    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
    JL Smith, Jul 7, 2003
    #1
    1. Advertising

  2. JL Smith schrieb:
    > 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
    Thomas Kellerer, Jul 7, 2003
    #2
    1. Advertising

  3. JL Smith

    Dave Glasser Guest

    (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();
    }



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

    http://qform.sourceforge.net
    Dave Glasser, Jul 8, 2003
    #3
  4. JL Smith

    Dave Glasser Guest

    Dave Glasser <> wrote on Mon, 07 Jul 2003 22:29:25
    -0400 in comp.lang.java.programmer:

    > (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
    Dave Glasser, Jul 8, 2003
    #4
  5. JL Smith

    JL Smith Guest

    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


    "Dave Glasser" <> wrote in message
    news:...
    > Dave Glasser <> wrote on Mon, 07 Jul 2003 22:29:25
    > -0400 in comp.lang.java.programmer:
    >
    > > (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
    JL Smith, Jul 8, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mathias Conradt
    Replies:
    3
    Views:
    3,574
    Andrew Thompson
    Sep 8, 2004
  2. HalcyonWild
    Replies:
    2
    Views:
    912
    HalcyonWild
    Dec 19, 2005
  3. mahesh
    Replies:
    3
    Views:
    4,664
    cb_1987
    Apr 6, 2010
  4. ecoolone
    Replies:
    0
    Views:
    742
    ecoolone
    Jan 3, 2008
  5. Royan
    Replies:
    8
    Views:
    736
    Patricia Shanahan
    Feb 15, 2008
Loading...

Share This Page