Migrating StringEscapeUtils.escapeSql from commons.lang

Question

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

According to https://commons.apache.org/proper/commons-lang/article3_0.html

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?

Answer 1

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 http://www.jguru.com/faq/view.jsp?EID=8881
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.

Answer 2

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

Answer 3

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