Migrating StringEscapeUtils.escapeSql from commons.lang

I have started to migrate commons.lang 2 to commons.lang3.

According to

StringEscapeUtils.escapeSql

This was a misleading method, only handling the simplest of possible SQL cases. >As SQL is not Lang's focus, it didn't make sense to maintain this method.

Understand it but what is recommended to use instead of it?

Clarification

Can you recommend a third party that perform simple escapeSql similar to StringEscapeUtils.escapeSql?

3 Answers

From the Javadocs:

At present, this method only turns single-quotes into doubled single-quotes ("McHale's Navy" => "McHale''s Navy").

This was the method code:

 /** 675 * <p>Escapes the characters in a <code>String</code> to be suitable to pass to 676 * an SQL query.</p> 677 * 678 * <p>For example, 679 * <pre>statement.executeQuery("SELECT * FROM MOVIES WHERE TITLE='" + 680 * StringEscapeUtils.escapeSql("McHale's Navy") + 681 * "'");</pre> 682 * </p> 683 * 684 * <p>At present, this method only turns single-quotes into doubled single-quotes 685 * (<code>"McHale's Navy"</code> => <code>"McHale''s Navy"</code>). It does not 686 * handle the cases of percent (%) or underscore (_) for use in LIKE clauses.</p> 687 * 688 * see 689 * @param str the string to escape, may be null 690 * @return a new String, escaped for SQL, <code>null</code> if null string input 691 */ 692 public static String escapeSql(String str) { 693 if (str == null) { 694 return null; 695 } 696 return StringUtils.replace(str, "'", "''"); 697 } 

So you could easily replace the method with a simple call to String#replace.

However, there is a reason that the method was removed. It was really half-baked and I cannot think of a good reason why you would want to use it. To run JDBC queries for example, you can and should use bind variables instead of trying to interpolate and escape string literals.

In case you are using a JDBC connection, preparing a statement with parameters like:

con.prepareStatement("INSERT INTO table1 VALUES (?,?)"); pstmt.setInt(1, 200); pstmt.setString(2, "Julie"); pstmt.executeUpdate(); 

You do not need to escape any elements that you insert using the functions on a prepared statement. Those are escaped automatically.

This has been answered before in: Java - escape string to prevent SQL injection

There is an API by OWASP called ESAPI that provides some of these functions, you can check it out.