Java - escape string to prevent SQL injection

I'm trying to put some anti sql injection in place in java and am finding it very difficult to work with the the "replaceAll" string function. Ultimately I need a function that will convert any existing \ to \\, any " to \", any ' to \', and any \n to \\n so that when the string is evaluated by MySQL SQL injections will be blocked.

I've jacked up some code I was working with and all the \\\\\\\\\\\ in the function are making my eyes go nuts. If anyone happens to have an example of this I would greatly appreciate it.

PreparedStatements are the way to go, because they make SQL injection impossible. Here's a simple example taking the user's input as the parameters:

public insertUser(String name, String email) {
   Connection conn = null;
   PreparedStatement stmt = null;
   try {
      conn = setupTheDatabaseConnectionSomehow();
      stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
      stmt.setString(1, name);
      stmt.setString(2, email);
      stmt.executeUpdate();
   }
   finally {
      try {
         if (stmt != null) { stmt.close(); }
      }
      catch (Exception e) {
         // log this error
      }
      try {
         if (conn != null) { conn.close(); }
      }
      catch (Exception e) {
         // log this error
      }
   }
}

No matter what characters are in name and email, those characters will be placed directly in the database. They won't affect the INSERT statement in any way.

There are different set methods for different data types -- which one you use depends on what your database fields are. For example, if you have an INTEGER column in the database, you should use a setInt method. The PreparedStatement documentation lists all the different methods available for setting and getting data.

The only way to prevent SQL injection is with parameterized SQL. It simply isn't possible to build a filter that's smarter than the people who hack SQL for a living.

So use parameters for all input, updates, and where clauses. Dynamic SQL is simply an open door for hackers, and that includes dynamic SQL in stored procedures. Parameterize, parameterize, parameterize.

If really you can't use Defense Option 1: Prepared Statements (Parameterized Queries) or Defense Option 2: Stored Procedures, don't build your own tool, use the OWASP Enterprise Security API. From the OWASP ESAPI hosted on Google Code:

> Don’t write your own security controls! Reinventing the wheel when it comes to developing security controls for every web application or web service leads to wasted time and massive security holes. The OWASP Enterprise Security API (ESAPI) Toolkits help software developers guard against security‐related design and implementation flaws.

For more details, see Preventing SQL Injection in Java and SQL Injection Prevention Cheat Sheet.

Pay a special attention to Defense Option 3: Escaping All User Supplied Input that introduces the OWASP ESAPI project).

(This is in answer to the OP's comment under the original question; I agree completely that PreparedStatement is the tool for this job, not regexes.)

When you say \n, do you mean the sequence \+n or an actual linefeed character? If it's \+n, the task is pretty straightforward:

s = s.replaceAll("['\"\\\\]", "\\\\$0");

To match one backslash in the input, you put four of them in the regex string. To put one backslash in the output, you put four of them in the replacement string. This is assuming you're creating the regexes and replacements in the form of Java String literals. If you create them any other way (e.g., by reading them from a file), you don't have to do all that double-escaping.

If you have a linefeed character in the input and you want to replace it with an escape sequence, you can make a second pass over the input with this:

s = s.replaceAll("\n", "\\\\n");

Or maybe you want two backslashes (I'm not too clear on that):

s = s.replaceAll("\n", "\\\\\\\\n");

PreparedStatements are the way to go in most, but not all cases. Sometimes you will find yourself in a situation where a query, or a part of it, has to be built and stored as a string for later use. Check out the SQL Injection Prevention Cheat Sheet on the OWASP Site for more details and APIs in different programming languages.

Using a regular expression to remove text which could cause a SQL injection sounds like the SQL statement is being sent to the database via a Statement rather than a PreparedStatement.

One of the easiest ways to prevent an SQL injection in the first place is to use a PreparedStatement, which accepts data to substitute into a SQL statement using placeholders, which does not rely on string concatenations to create an SQL statement to send to the database.

For more information, Using Prepared Statements from The Java Tutorials would be a good place to start.

Prepared Statements are the best solution, but if you really need to do it manually you could also use the StringEscapeUtils class from the Apache Commons-Lang library. It has an escapeSql(String) method, which you can use:

import org.apache.commons.lang.StringEscapeUtils; … String escapedSQL = StringEscapeUtils.escapeSql(unescapedSQL);

You need the following code below. At a glance, this may look like any old code that I made up. However, what I did was look at the source code for http://grepcode.com/file/repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.31/com/mysql/jdbc/PreparedStatement.java. Then after that, I carefully looked through the code of setString(int parameterIndex, String x) to find the characters which it escapes and customised this to my own class so that it can be used for the purposes that you need. After all, if this is the list of characters that Oracle escapes, then knowing this is really comforting security-wise. Maybe Oracle need a nudge to add a method similar to this one for the next major Java release.

public class SQLInjectionEscaper {

	public static String escapeString(String x, boolean escapeDoubleQuotes) {
		StringBuilder sBuilder = new StringBuilder(x.length() * 11/10);
	
		int stringLength = x.length();

		for (int i = 0; i < stringLength; ++i) {
			char c = x.charAt(i);

			switch (c) {
			case 0: /* Must be escaped for 'mysql' */
				sBuilder.append('\\');
				sBuilder.append('0');

				break;

			case '\n': /* Must be escaped for logs */
				sBuilder.append('\\');
				sBuilder.append('n');

				break;

			case '\r':
				sBuilder.append('\\');
				sBuilder.append('r');

				break;

			case '\\':
				sBuilder.append('\\');
				sBuilder.append('\\');

				break;

			case '\'':
				sBuilder.append('\\');
				sBuilder.append('\'');

				break;

			case '"': /* Better safe than sorry */
				if (escapeDoubleQuotes) {
					sBuilder.append('\\');
				}

				sBuilder.append('"');

				break;

			case '\032': /* This gives problems on Win32 */
				sBuilder.append('\\');
				sBuilder.append('Z');

				break;

			case '\u00a5':
			case '\u20a9':
				// escape characters interpreted as backslash by mysql
				// fall through

			default:
				sBuilder.append(c);
			}
		}

		return sBuilder.toString();
	}
}

In case you are dealing with a legacy system, or you have too many places to switch to PreparedStatements in too little time - i.e. if there is an obstacle to using the best practice suggested by other answers, you can try http://sourceforge.net/projects/antisqlfilter/">AntiSQLFilter</a>

Most of the people are recommending PreparedStatements, however that requires you to have a direct connection with your Database using the Java Application. But then you'll have everyone else saying that you shouldn't have a direct connection to your database due to security issues, but utilize a Restful API to deal with queries.

In my opinion, as long as you're aware that you have to be careful with what you escape and do It deliberately, there shouldn't be a problem.

My solution is using contains() to check for SQL keywords such as UPDATE or other dangerous characters like = to completely nullify the SQL injection by asking the user to insert other characters on input.

Edit: You can use this source material from W3Schools about Java Regular Expressions to do this validation on Strings.

After searching an testing alot of solution for prevent sqlmap from sql injection, in case of legacy system which cant apply prepared statments every where.

java-security-cross-site-scripting-xss-and-sql-injection topic WAS THE SOLUTION

i tried @Richard s solution but did not work in my case. i used a filter

> The goal of this filter is to wrapper the request into an own-coded > wrapper MyHttpRequestWrapper which transforms: > > the HTTP parameters with special characters (<, >, ‘, …) into HTML > codes via the org.springframework.web.util.HtmlUtils.htmlEscape(…) > method. Note: There is similar classe in Apache Commons : > org.apache.commons.lang.StringEscapeUtils.escapeHtml(…) the SQL > injection characters (‘, “, …) via the Apache Commons classe > org.apache.commons.lang.StringEscapeUtils.escapeSql(…)

<filter>
<filter-name>RequestWrappingFilter</filter-name>
<filter-class>com.huo.filter.RequestWrappingFilter</filter-class>
</filter>

<filter-mapping>
<filter-name>RequestWrappingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>




package com.huo.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletReponse;
import javax.servlet.http.HttpServletRequest;

public class RequestWrappingFilter implements Filter{

	public void doFilter(ServletRequest req, ServletReponse res, FilterChain chain) throws IOException, ServletException{
		chain.doFilter(new MyHttpRequestWrapper(req), res);
	}

	public void init(FilterConfig config) throws ServletException{
	}

	public void destroy() throws ServletException{
	}
}




package com.huo.filter;

import java.util.HashMap;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletRequestWrapper;

import org.apache.commons.lang.StringEscapeUtils;

public class MyHttpRequestWrapper extends HttpServletRequestWrapper{
	private Map<String, String[]> escapedParametersValuesMap = new HashMap<String, String[]>();

	public MyHttpRequestWrapper(HttpServletRequest req){
		super(req);
	}

	@Override
	public String getParameter(String name){
		String[] escapedParameterValues = escapedParametersValuesMap.get(name);
		String escapedParameterValue = null; 
		if(escapedParameterValues!=null){
			escapedParameterValue = escapedParameterValues[0];
		}else{
			String parameterValue = super.getParameter(name);

			// HTML transformation characters
			escapedParameterValue = org.springframework.web.util.HtmlUtils.htmlEscape(parameterValue);
			
			// SQL injection characters
			escapedParameterValue = StringEscapeUtils.escapeSql(escapedParameterValue);
			
			escapedParametersValuesMap.put(name, new String[]{escapedParameterValue});
		}//end-else
		
		return escapedParameterValue;
	}

	@Override
	public String[] getParameterValues(String name){
		String[] escapedParameterValues = escapedParametersValuesMap.get(name);
		if(escapedParameterValues==null){
			String[] parametersValues = super.getParameterValues(name);
			escapedParameterValue = new String[parametersValues.length];

			// 
			for(int i=0; i<parametersValues.length; i++){
				String parameterValue = parametersValues[i];
				String escapedParameterValue = parameterValue;
				
				// HTML transformation characters
				escapedParameterValue = org.springframework.web.util.HtmlUtils.htmlEscape(parameterValue);
			
				// SQL injection characters
				escapedParameterValue = StringEscapeUtils.escapeSql(escapedParameterValue);
			
				escapedParameterValues[i] = escapedParameterValue;
			}//end-for
			
			escapedParametersValuesMap.put(name, escapedParameterValues);
		}//end-else
		
		return escapedParameterValues;
	}
}

From:Source

public String MysqlRealScapeString(String str){
  String data = null;
  if (str != null && str.length() > 0) {
    str = str.replace("\\", "\\\\");
    str = str.replace("'", "\\'");
    str = str.replace("\0", "\\0");
    str = str.replace("\n", "\\n");
    str = str.replace("\r", "\\r");
    str = str.replace("\"", "\\\"");
    str = str.replace("\\x1a", "\\Z");
    data = str;
  }
  return data;
}

If you are using PL/SQL you can also use DBMS_ASSERT it can sanitize your input so you can use it without worrying about SQL injections.

see this answer for instance: https://stackoverflow.com/a/21406499/1726419

First, ask the question - are double or single quotes, or backslashes needed in user entry fields?

Backslashes - no. Double and single quotes are rarely used in English and they are used differently in Britain than the U.S.

I say remove or replace them and you simplify.

private String scrub(
    String parameter,
    int    length
    )
{
    String parm = null;

    if ( parameter != null && parameter.length() > 0 && parameter.length() < length )
    {
        parm = parameter
            .replace( "\\", " " )
            .replace( "\"", " " )
            .replace( "\'", " " )
            .replace( "\t", " " )
            .replace( "\r", " " )
            .replace( "\n", " " )
            .trim();
    }

    return parm;
}