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

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.