How to capture/encode special character for SQL Server in java app?

Question

I have a java app + SQL server database. DB operation use JDBC with dynamic SQL string. Example:

Select Column from tab where column=StringParm

StringParam is user input. if the stringParm include apostrophe, Java app will throw exception and said Can't execute the SQL.

How to resolve this problem with no java code changing?

Answer 1

Never put user input directly in a SQL query. You need to use a PreparedStatement with parameters. Without changing the Java code, I don't see any way to make this safe.

Answer 2

You should really use java.sql.PreparedStatement to set parameters. The code changes should be minimal and it is less problematic that trying to escape user input.

Answer 3

Have a look at http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain

Answer 4

I'm guessing you construct the SQL in some manner like

String sql = "Select Column from tab where column='" + StringParm + "'";

Or something like it ? If you do that, you're open to all kinds of exploits and you'll also see behavior like you describe, where the resulting string is no longer valid SQL. You'd have to escape the user supplied parameter first.

The best solution is to use PreparedStatements, so you do

Statement stmt = conn.prepareStatement("Select Column from tab where column=?");
stmt.setString(1,StringParam);

I can't see any quick way of solving your problem without altering any Java code though, bar perhaps escaping/sanitizing the input before it hits your code (e.g. javascript if you're a webapp)

Answer 5

You can not fix this without changing the application. SQL Server can handle quotes, however your application (Java code) is not properly escaping the quotes when you build your dynamic SQL commands. I prefer using stored procedures and passing in parameters, this way there are never any quotes issues or injections.

Answer 6

You could fix this by putting a trigger in the database to clean up the entry - i.e. when an insert is attempted, instead do proper escaping on the input, and then continue with the new insert input. However, this is the wrong layer and it should probably not be done down there. A much better solution (IMO) is to use a prepared statement, and do variable replacements, letting JDBC do the escape work for you.