jueves, 7 junio 2007
Replacing Apostrophes from Strings // Cleaning String to pass them as SQL statements
« Detecting Tab Key Pressed Event in JTextField 's // Event.VK_TAB KeyPressed | Main | Getting started with JasperReports // Reporting in Java (Part I) »Such a simple thing as replacing an apostrophe with an escape character from a string sometimes can become a very tedious process, more if you're using String's replaceAll(...) function. Replacing apostrophes from Strings may be very useful when creating Statements to pass to an SQL database, preventing SQL injection. Many may say that using PreparedStatements is easier and safer, but in many occasions you can't use prepared statements to accomplish what you need.
The problem with the replaceAll function comes because of the arguments the function expects. In first place you must pass a regular expression that suits what you want to replace. Secondly, the function expects another regular expression with the String's replacement. In postgreSQL when passing apostrophes and other special characters as text, you must use the backslash as an escape character, just like in java. So if you want to insert "John's hand" as an argument to an Insert statement you must write something such as 'John\'s hand'.
If you refer to Pattern javadoc you can read:
The backslash character ('\') serves to introduce escaped constructs, as defined in the table above, as well as to quote characters that otherwise would be interpreted as unescaped constructs. Thus the expression \\ matches a single backslash and \{ matches a left brace.
Here is the answer to the problem. When replacing ' for \' you need to place 4 backslashes before the apostrophe.
String replacedString = "John's hand";
replacedString.replaceAll("'","\\\\'");
Technorati Tags: Pattern regex replaceAll String PreparedStatement apostrophe
Posted by at 8:25 AM in Java
[Trackback URL for this entry]
You're totally rigth, prepared statements solve this issue in a very easy way. But sometimes you don't want to use PreparedStatements in terms of performance. If the statement is run only once during all the app's lifecycle statements have better performance than prepared statments.
http://www.onjava.com/pub/a/onjava/excerpt/oraclejdbc_19/index.html?page=1
I guess it would be a pain in the ass to escape all your String parameters to an SQL statement. Luckily for me I have never come across a instance where I had to pick Statements over PreparedStatements. Actually I haven't worked at the JDBC API level in the last 3 years , thanks to EJB, Hibernate and iBatis :)
It would really be if you had to do it repeatedly. You can avoid it by using a static function to do it every time.
public static String parseSQL(String in){
return in.replaceAll("'", "\\\\'").replaceAll(..whatever other things you need...)...;
}
This way, you can call this function any time you need to pass an argument to an SQL statement.
as was mentioned on Sun's Java forums, the two ticks is the ANSI standard, so replacing a ' with '' would be easier.. but PS is surely the way to go for oft repeated statements.
Yeah - it sure would be nice to have literal regexs in Java - would solve all that sort of stuff.
For the specific case you mention, I think you would be better off using the '?' argument notation provided by JDBC PreparedStatements, rather than rolling your own. The Postgres driver should take care of it for you.