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

If you proceed as you've been doing you will go insane. I suggest a switch to PreparedStatement with JDBC post haste. Essentially it's used like this.

//get a connection

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  

  

//write the statement, "?" in this case is the spot for whatever data you want

PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");  

//insert data like so (not 0 indexed, so 1 is first)

stmt.setInt(1,101);

stmt.setString(2,"Test");  

  

//execute update does just that, runs query and returns a value if it was successful or not

int i=stmt.executeUpdate();  

System.out.println(i+" records inserted");

//connection must be closed

con.close();

Can also be used for selections, values returned to ResultSet instead of integer

...

ResultSet rs = preparedStatement.executeQuery();

while (rs.next()) {

//get data at specific columns

String userid = rs.getString("USER_ID");

String username = rs.getString("USERNAME");

...

.executeQuery() used for selects, .executeUpdate() used for inserts.

Further reading:

http://www.ntu.edu.sg/home/ehchua/programming/java/jdbc_basic.html