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

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

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

Search & replace methods are always useful, but you can also use a PreparedStatement to be sure there's no problems.



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.