Replacing apostrophes for an sql statements

Discussion in 'Java' started by MS, Feb 21, 2005.

  1. MS

    MS Guest

    Hi,

    When doing SQL statements apostrophes mark the start and end of a text
    field, accordingly any apostrophe in the text must be handled.

    So here's a code snippet showing how I handle this:

    int isApostrophe = title.indexOf("'");
    if (isApostrophe != -1)
    {
    title = title.replaceAll("'", "\\'");
    }

    I search to see if the text has an apostrophe, if there is a match in the
    text (String title) replace all the apostrophes with "\\'" so that the
    following conversion will take place:

    "Matthew's test" should become "Matthew\'s test" and the sql, when run,
    should be happy as it knows the apostrophe in the middle of the text is
    not the end of the text.

    BUT this is not happening "Matthew's test" is remaining the same. I'm
    probably being stupid but I can't work out what's wrong with my code.

    Please help,

    MS
     
    MS, Feb 21, 2005
    #1
    1. Advertising

  2. MS

    Tilman Bohn Guest

    [Followup-To set to clj.help]

    In message <MkoSd.20005$>,
    MS wrote on Mon, 21 Feb 2005 16:51:56 GMT:

    [...]
    > int isApostrophe = title.indexOf("'");
    > if (isApostrophe != -1)
    > {
    > title = title.replaceAll("'", "\\'");
    > }


    Three points:

    First of all, look at the API documentation for Matcher's replaceAll(),
    which is what the above really ends up being delegated to:

    `Note that backslashes (\) and dollar signs ($) in the replacement
    string may cause the results to be different than if it were being
    treated as a literal replacement string. Dollar signs may be treated
    as references to captured subsequences as described above, and
    backslashes are used to escape literal characters in the replacement
    string.'

    What this means is that you want _two_ backslashes to appear in your
    replacement String, which means putting four of them in the literal. So
    summing up,

    title = title.replaceAll("'", "\\\\'");

    will do what you want.

    Secondly, the indexOf() test is extraneous. It's safe to run replaceAll
    on input that doesn't match, and both indexOf and replaceAll must scan the
    whole input anyway so you're not buying anything by the extra test.

    Finally, you should really look into PreparedStatement, which will
    transparently escape any special characters (not just apostrophes) for
    you.

    --
    Cheers, Tilman

    `Boy, life takes a long time to live...' -- Steven Wright
     
    Tilman Bohn, Feb 21, 2005
    #2
    1. Advertising

  3. MS

    MS Guest

    Many thanks.

    > What this means is that you want _two_ backslashes to appear in your
    > replacement String, which means putting four of them in the literal. So
    > summing up,
    >
    > title = title.replaceAll("'", "\\\\'");
    >
    > will do what you want.


    I got confused but get it now.


    > Secondly, the indexOf() test is extraneous. It's safe to run replaceAll
    > on input that doesn't match, and both indexOf and replaceAll must scan the
    > whole input anyway so you're not buying anything by the extra test.


    I've changed things accordingly, I thought it would save some processing,
    instead of doubling it. --oops.


    > Finally, you should really look into PreparedStatement, which will
    > transparently escape any special characters (not just apostrophes) for
    > you.


    Okay, I'll take a look.

    Thanks,

    MS
     
    MS, Feb 21, 2005
    #3
  4. MS wrote:

    > Hi,
    >
    > When doing SQL statements apostrophes mark the start and end of a text
    > field, accordingly any apostrophe in the text must be handled.
    >
    > So here's a code snippet showing how I handle this:
    >
    > int isApostrophe = title.indexOf("'");
    > if (isApostrophe != -1)
    > {
    > title = title.replaceAll("'", "\\'");
    > }
    >
    > I search to see if the text has an apostrophe, if there is a match in the
    > text (String title) replace all the apostrophes with "\\'" so that the
    > following conversion will take place:
    >
    > "Matthew's test" should become "Matthew\'s test" and the sql, when run,
    > should be happy as it knows the apostrophe in the middle of the text is
    > not the end of the text.
    >
    > BUT this is not happening "Matthew's test" is remaining the same. I'm
    > probably being stupid but I can't work out what's wrong with my code.
    >
    > Please help,
    >
    > MS


    Use binding.
    Example from PreparedStatement JavaDoc:
    PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET
    SALARY= ? WHERE ID = ?");
    pstmt.setBigDecimal(1, 153833.00)
    pstmt.setInt(2, 110592)

    Don't include title value into SQL query, just insert '?' and use
    pstmt.setString(1, title);

    --
    Cheers
    grundig
     
    Marcin Grunwald, Feb 22, 2005
    #4
    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. Chris Huddle

    SQL and apostrophes

    Chris Huddle, Dec 10, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    763
    Steve C. Orr [MVP, MCSD]
    Dec 10, 2003
  2. mister-Ed

    Escaping apostrophes inserting into sql

    mister-Ed, Oct 5, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    402
    David Wier
    Oct 5, 2007
  3. middletree

    replacing apostrophes

    middletree, Dec 22, 2003, in forum: ASP General
    Replies:
    3
    Views:
    153
    Ray at
    Dec 22, 2003
  4. Rob Meade

    apostrophes in SQL statement

    Rob Meade, Apr 21, 2004, in forum: ASP General
    Replies:
    22
    Views:
    399
    Bob Barrows [MVP]
    Apr 22, 2004
  5. shank

    replacing apostrophes in text box

    shank, Apr 27, 2004, in forum: ASP General
    Replies:
    8
    Views:
    218
    Steven Burn
    Apr 27, 2004
Loading...

Share This Page