Java equivalent for PHP's mysql_real_escape_string()
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.
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)+"`";
}
}
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.