Java equivalent for PHP's mysql_real_escape_string()

Question

Is there a Java equivalent to PHP's mysql_real_escape_string() ?

This is to escape SQL injection attempts before passing them to Statement.execute().

I know I can use PreparedStatement instead, but let's assume these are one shot statements so preparing them will result in lower performance. I've already changed the code to use PreparedStatement but given the way the existing code was structured, an escape() function would make the code changes much simpler to review and maintain; I prefer easy to maintain code unless there is a compelling reason for the extra complexity. Also PreparedStatements are handled differently by the database, so this could expose us to bugs in the database that we haven't run into before, requiring more testing before releasing to production.

Apache StringEscapeUtils escapeSQL() only escapes single quotes.

Postscript: There are a lot of subtleties in the environment I inherited that I deliberately avoided in my question.

Two points to consider:

1) Prepared statements are not a panacea and do not provide 100% protection against SQL injection. Some database drivers instantiate parameterised queries using unsafe string concatenation, rather than pre-compiling the query to a binary form. Also, if your SQL relies on stored procedures, you need to ensure the stored procedures do not themselves build queries in unsafe ways.

2) Most prepared statement implementation bind the statement to the database connection the statement was instantiated on. If you are using database connection pooling, you need to be careful to
use the prepared statement reference only with the connection it was prepared on. Some pooling mechanisms do implement this transparently. Otherwise you could pool the prepared statements as well or (simplest but more overhead) create a new prepared statement for every query.

Raid

You prefer easy to maintain code, and yet you'd rather use manual string escaping rather than PrearedStatement? – Barleycorn

Given the way the existing code (which I didn't write) is, yes, it would be easier to maintain. – Raid

the lower performance may well be more attractive than the security risk. the security penalty can just be too high. Prepare your statements in the application initialisation code and the penalty may not be noticable (application-dependent of course). – Scaleboard

Answer 1

As far as I know, there is no "standard" way to do it.

I strongly suggest using prepared statements despite your current concerns. The performance impact is going to be negligible - we have a similar situation with several thousand statements per second - most of them one-shots as well.

The security you gain should be weighed much higher than a performance problem you have not even seen yet. In my opinion this is a clear situation of "Don't optimize prematurely".

In any case should you really find out later that you run into performance problems, make sure that the prepared statements are really the cause by profiling carefully and then look for alternatives. Till then you should save yourself the hassle of trying to get the escaping right.

This is even more important as I infer you are developing some sort of public facing site - internal apps seldom get enough traffic to be concerned about performance anyway.

Poult

Answer 2

Here is some code which achieves what you are looking for. Originally on the Vnet Publishing wiki.

https://web.archive.org/web/20131202082741/http://wiki.vnetpublishing.com/Java_Mysql_Real_Escape_String

/**
  * Mysql Utilities
  *        
  * @author Ralph Ritoch <[email protected]>
  * @copyright Ralph Ritoch 2011 ALL RIGHTS RESERVED
  * @link http://www.vnetpublishing.com
  *
  */

 package vnet.java.util;

 public class MySQLUtils {

     /**
      * Escape string to protected against SQL Injection
      *
      * You must add a single quote ' around the result of this function for data,
      * or a backtick ` around table and row identifiers. 
      * If this function returns null than the result should be changed
      * to "NULL" without any quote or backtick.
      *
      * @param link
      * @param str
      * @return
      * @throws Exception 
      */

     public static String mysql_real_escape_string(java.sql.Connection link, String str) 
           throws Exception
     {
         if (str == null) {
             return null;
         }

         if (str.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\\Q[\\E\\Q]\\E<>{}\\/? ]","").length() < 1) {
             return str;
         }

         String clean_string = str;
         clean_string = clean_string.replaceAll("\\\\", "\\\\\\\\");
         clean_string = clean_string.replaceAll("\\n","\\\\n");
         clean_string = clean_string.replaceAll("\\r", "\\\\r");
         clean_string = clean_string.replaceAll("\\t", "\\\\t");
         clean_string = clean_string.replaceAll("\\00", "\\\\0");
         clean_string = clean_string.replaceAll("'", "\\\\'");
         clean_string = clean_string.replaceAll("\\\"", "\\\\\"");

         if (clean_string.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\\Q[\\E\\Q]\\E<>{}\\/?\\\\\"' ]"
           ,"").length() < 1) 
         {
             return clean_string;
         }

         java.sql.Statement stmt = link.createStatement();
         String qry = "SELECT QUOTE('"+clean_string+"')";

         stmt.executeQuery(qry);
         java.sql.ResultSet resultSet = stmt.getResultSet();
         resultSet.first();
         String r = resultSet.getString(1);
         return r.substring(1,r.length() - 1);       
     }

     /**
      * Escape data to protected against SQL Injection
      *
      * @param link
      * @param str
      * @return
      * @throws Exception 
      */

     public static String quote(java.sql.Connection link, String str)
           throws Exception
     {
         if (str == null) {
             return "NULL";
         }
         return "'"+mysql_real_escape_string(link,str)+"'";
     }

     /**
      * Escape identifier to protected against SQL Injection
      *
      * @param link
      * @param str
      * @return
      * @throws Exception 
      */

     public static String nameQuote(java.sql.Connection link, String str)
           throws Exception
     {
         if (str == null) {
             return "NULL";
         }
         return "`"+mysql_real_escape_string(link,str)+"`";
     }

 }

Cinnabar

@droope the code checks if there are any dangerous characters in a string. If not than the string is returned without processing. The code then checks if escaping the string normally removes any possibly dangerous characters. If so than the string is returned in the escaped form. Finally, the escaped string is sent to MySQL to be escaped by the QUOTE function which is specifically meant to escape strings. – Cinnabar

@Ken V.H. sorry about that, the site has been down for some time so I posted the original code instead. – Cinnabar

Answer 3

Do not assume that PreparedStatements are slower. Try it, measure it, and then judge.

PreparedStatements should always be used in preference to Statement, pretty much without exception, especially when SQL injection attacks are what you're trying to avoid.

Barleycorn

Answer 4

The only sensible way to avoid SQL injection is to use prepared/parameterized statements.

For example the PreparedStatement you are trying to avoid for some reason. If you do one-shot statements, the time to prepare them should be negligible ("one-shot" and "performance-critical" is a contradiction, IMHO). If you do things in a loop, prepared statements even cause performance to increase.

Schuck

Answer 5

org.apache.commons.lang.StringEscapeUtils.class in commons-lang.jar could solve your problem!

Potentiality

Answer 6

According to Daniel Schneller, there is no standard way to handle PHP's mysql_real_escape_string() in Java What I did was to chain replaceAll method to handle every aspect that may be necessary to avoid any exception. Here is my sample code:

public void saveExtractedText(String group,String content) { try { content = content.replaceAll("\\", "\\\\") .replaceAll("\n","\\n") .replaceAll("\r", "\\r") .replaceAll("\t", "\\t") .replaceAll("\00", "\\0") .replaceAll("'", "\\'") .replaceAll("\\"", "\\\"");

        state.execute("insert into extractiontext(extractedtext,extractedgroup) values('"+content+"','"+group+"')");
    } catch (Exception e) {
        e.printStackTrace();

    }

Callaway

Answer 7

I would not trust anything else than PreparedStatement to ensure security. But if you need to have a similar workflow when building queries you may use the code below. It uses a PreparedStatement underneath, works like a StringBuilder, adds escape functions and tracks the parameter indexes for you. It can be used like this:

SQLBuilder sqlBuilder = new SQLBuilder("update ").append(dbName).append(".COFFEES ");
sqlBuilder.append("set SALES = ").escapeString(sales);
sqlBuilder.append(", TOTAL = ").escapeInt(total);
sqlBuilder.append("where COF_NAME = ").escapeString(coffeeName);
sqlBuilder.prepareStatement(connection).executeUpdate();

Here's the code:

class SQLBuilder implements Appendable {
    private StringBuilder sqlBuilder;
    private List<Object> values = new ArrayList<>();

    public SQLBuilder() {
        sqlBuilder = new StringBuilder();
    }

    public SQLBuilder(String str)
    {
        sqlBuilder = new StringBuilder(str);
    }

    @Override
    public SQLBuilder append(CharSequence csq)
    {
        sqlBuilder.append(csq);
        return this;
    }

    @Override
    public SQLBuilder append(CharSequence csq, int start, int end)
    {
        sqlBuilder.append(csq, start, end);
        return this;
    }

    @Override
    public SQLBuilder append(char c)
    {
        sqlBuilder.append(c);
        return this;
    }

    // you can add other supported parameter types here...
    public SQLBuilder escapeString(String x)
    {
        protect(x);
        return this;
    }

    public SQLBuilder escapeInt(int x)
    {
        protect(x);
        return this;
    }

    private void escape(Object o)
    {
        sqlBuilder.append('?');
        values.add(o);
    }

    public PreparedStatement prepareStatement(Connection connection)
        throws SQLException
    {
        PreparedStatement preparedStatement =
            connection.prepareStatement(sqlBuilder.toString());
        for (int i = 0; i < values.size(); i++)
        {
            Object value = values.get(i);
            // you can add other supported parameter types here...
            if (value instanceof String)
                preparedStatement.setString(i + 1, (String) value);
            else if (value instanceof Integer)
                preparedStatement.setInt(i + 1, (Integer) value);
        }
        return preparedStatement;
    }

    @Override
    public String toString()
    {
        return "SQLBuilder: " + sqlBuilder.toString();
    }
}

Gride