15

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?

Community
  • 1
  • 1
Michael
  • 10,063
  • 18
  • 65
  • 104
  • Describe your usecase. The simplest one would be "run database queries", and for that you normally don't need to escape any SQL (you can and should use bind variables). – Thilo Aug 19 '15 at 13:22
  • Why would you want to do that? It does seem like a bad idea, so I can understand why the method was removed. – Thilo Aug 19 '15 at 13:27
  • Easy use case ... PreparedStatement doesn't work if you happen to have parameters needed in the "order by" clause. – Nathan Crause Jun 09 '21 at 01:12
  • @NathanCrause: Hmm. What RDBMS is that? Can it be worked around by wrapping the query into an inline view / subselect, so that the expression to be ordered by becomes a "column"? Might be worth opening a new question for this specific example. – Thilo Jul 19 '21 at 02:02
  • @Thilo that's an interesting idea I hadn't considered. From a purely technical stand-point, it seems like tinkering around something that shouldn't need to be tinkered around. To answer the first question, it's MariaDB. (That's not by choice, BTW, I wanted PostgreSQL). – Nathan Crause Jul 26 '21 at 01:36

3 Answers3

22

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.

Sergio del Amo
  • 76,835
  • 68
  • 152
  • 179
Thilo
  • 257,207
  • 101
  • 511
  • 656
  • 1
    Actually a use case does exist: escaping parts of the SQL statement that can not be parametrized by means of bind variables. E.g. the table name iteself: http://stackoverflow.com/q/1208442/274677 – Marcus Junius Brutus Jan 23 '17 at 21:56
  • @MarcusJuniusBrutus: I wouldn't count on the same escaping rules being used there. And if you use things like spaces or quotes in schema object names you are asking for trouble already. – Thilo Jan 23 '17 at 23:48
  • @Thilo how about this use-case: PreparedStatement doesn't work if you happen to have parameters needed in the "order by" clause. – Nathan Crause Jun 09 '21 at 01:13
  • @NathanCrause not sure what you mean. If you want to have variable column names in `ORDER BY abc DESC` then yeah, like Marcus remarked about table names, bind parameters cannot be used for schema object names, just for values. – Thilo Jun 09 '21 at 12:42
  • @Thilo that's the point - a system I was working on had an "order by" clause which contained values passed in (the sort order was based on scoring based on whether a string supplied was at the beginning or the ending of the column value). So "I cannot think of a good reason" is an entirely irrelevant point just because a use-case can't be fathomed at one point, doesn't follow that there is NO use-case for it. – Nathan Crause Jul 18 '21 at 20:16
  • @NathanCrause And you need string literal escaping for that? That sounds like you could still use bind variables. Thinks like `CASE WHEN x LIKE '%' || ? THEN` do support that. – Thilo Jul 19 '21 at 01:31
  • @Thilo you would be wrong. The ? in that case doesn't get processed. At all. ANY "?" in the "order by" clause doesn't get processed. – Nathan Crause Jul 26 '21 at 01:33
4

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

bub
  • 657
  • 1
  • 9
  • 18
  • Here's an and example and link within the site that explains how to use this API as well as discussion on other approaches. ESAPI.encoder().encodeForSQL( new OracleCodec(), queryparam ); https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html – David Bradley Feb 19 '21 at 14:24
3

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

Community
  • 1
  • 1
Matjaž Pečan
  • 253
  • 3
  • 4
  • But it doesn't work for all SQL clauses. As I've mentioned in other comments here, it doesn't work if you happen to have parameters present in your "order by". – Nathan Crause Jun 09 '21 at 01:14
  • This also works if you are using JPA/Hibernate when you need to work with native query. Use a prepared statement instead. ```java Query q = entityManager.createQuery("SELECT * FROM people WHERE name = ?") ; q.setParameter(1, "Julie") ``` – kosgeinsky Jul 03 '21 at 04:16