Java - escape string to prevent SQL injection

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

Why don't you use binds / query parameters instead?

Answer 4

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 = setupTheDatabaseConnectionSomehow();
   PreparedStatement stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
   stmt.setString(1, name);
   stmt.setString(2, email);
   stmt.executeUpdate();
}

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.

Answer 5

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).

Answer 6

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 AntiSQLFilter

Answer 7

(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 the latter, 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");