SQL & Java Error with using SQL characters

Question

I have a problem with SQL that I can't seem to get rid of. The problem is when special characters are being used that sql statements itself use, it does not like it at all. I tried many things and could not find a answer.

The error is caused by using the ' character in the text that is being put into the SQL table.

NobleCore.getSQLStaticly().updateSQL("INSERT INTO Punishment (`ID`, `UUID`, `PlayerName`, `StaffName`, `Sev`, `pType`, `Reason`, `Activated`, `Ends`, `Active`, `RemoveReason`, `RemovedBy`) VALUES (NULL, '" + playerUUID + "', '" + player + "', '" + staffName + "' , '" + severity + "', '" + pType +"', '" + reason + "', CURRENT_TIMESTAMP,'" + end + "', '1', NULL, NULL );");

As you can see when it adds the '" + reason + "' the string from the method parameter it will error out causing a syntax exception because it contains the ' character. How can I get it to add this character so it knows it's part of the string and not to be used in the statement.

[17:28:29] [Server thread/WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ']', CURRENT_TIMESTAMP,'2015-02-26 17:58:29.708', '1', NULL, NULL )' at line 1

If you need any other code, let me know but I don't see how it will help :P

Answer 1

It seems you have a character within the variable reason which is rendering your SQL invalid. Use a prepared statement to save the values instead. BTW NULL values can be omitted from the query

Answer 2

The usual practice in this case is to use a PreparedStatement rather than building a statement dynamically from scratch.

In a PreparedStatement, you put question marks where the values should go, and then you use various setXXX methods to fill them up with values. The setString() method, for example, escapes the quotes in the given value appropriately for the database in question (that is, based on the JDBC driver, there may be different ways of escaping quotes, and JDBC does this for you automatically).

If you insist on building the query yourself, the standard way to escape quotes in SQL is to double them. For example, if your value is Sean O'Connor, the appropriate quoted value is Sean O''Connor. It's easy to write a method to double each single quote in the string.

But again, I would recommend trusting the authors of your JDBC driver for this and using a PreparedStatement.