#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2001
    Posts
    94
    Rep Power
    13

    Parsing escape characters


    I have a query:

    //query

    String query_one = "UPDATE homepage SET section1='" + section1 + "' ";
    //execute query
    section1SQL.executeUpdate(query_one);



    Here's the problem: if the var + section1 + contains a single quote or an apostrophe - the SQL query trips up.

    Is there a function or way to parse the characters so that whatever is contained in the var can be submitted into the DB without tripping the SQL query?

    Any help is appreciated.

    Thanks
    Mark
    A gentle push and a mild arc -
    And the cowhide globe hit home

    Hot Rod Hundley
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Posts
    0
    Rep Power
    0
    Hi there,

    this is a more general approach, which lets you specify the charachters you want to replace, and the characters you want to insert, so its also quite handy to replace html codes and so on.

    You just call the method with three parameters, the string that you want to replace, the string you want to insert instead of it, and the string that has to be cleaned. It returns the cleaned string.

    You could also define the different Strings in two arrays, and call the method in a for(int i = 0;i < stringReplace[].length:i++), which is what i did for html replacement.


    public static String searchReplace
    (String search, String replace, String str)
    {
    int startIndex = str.indexOf ( search );
    while( startIndex != -1)
    {
    str = str.substring ( 0, startIndex ) + replace + str.substring
    ( startIndex + search.length(), str.length() );
    startIndex = str.indexOf ( search, startIndex
    + replace.length() );
    }
    return ( str );
    }

    Hope it helps,
    Birger
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Posts
    0
    Rep Power
    0

    Another option


    If you pass your variables to this method it will replace the single quote with two single quotes and allow the value to be passed in a SQL statement.

    Hope this helps


    private String replaceApostrophe(String searchString){
    int index = 0;
    String StrOut = "";

    //account for any apostrophes in the parameter
    for (index = searchString.indexOf("'"); index != -1; index = searchString.indexOf("\'")) {
    // Copy up to the apostrophe
    StrOut += searchString.substring(0, index);

    // Add double apostrophe
    StrOut += "''";
    searchString = searchString.substring(index + 1);
    //Chop off "used" part
    }
    StrOut += searchString;
    // Add the left over part. (Whole thing, if there was no ')
    return StrOut;
    }
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    Boston, Ma
    Posts
    0
    Rep Power
    0
    Search & replace methods are always useful, but you can also use a PreparedStatement to be sure there's no problems.

    Code:
    //connection is your JDBC Connection object
    
    java.sql.PreparedStatement stmt = connection.prepareStatement("UPDATE homepage SET section1=?");
    stmt.setString(1, section1);
    stmt.executeUpdate();
    If you do this, the JDBC driver will handle any characters that would make the db choke. There's some overhead, but you can retain a reference to the object once it's prepared and reuse it. You can also let the driver handle most datatype conversions for you in a similar way using the the methods setInt(), setDate(), etc.

IMN logo majestic logo threadwatch logo seochat tools logo