Avoiding One Quote When Using JdbcTemplate

Question

We use JdbcTemplate to modify our base Oracle database. We do this using the update(String sql) method.

The code looks something like this:

 String name = "My name yellow"; String sql = "update FIELD set NAME = '" + name "' where ID = 10 jdbcTemplate.update(sql); 

This causes an error:

 java.sql.SQLException: ORA-00933: SQL command not properly ended 

The problem is the unselected ' in the name variable.

What is the most convenient and correct way to avoid this symbol?

Answer 1

Use PreparedStatement . Thus, you assign a placeholder, and the JDBC driver will do this correctly by sending instructions to the database, as well as parameters as arguments.

  String updateStatement = "update " + dbName + ".COFFEES " + "set TOTAL = TOTAL + ? " + "where COF_NAME = ?"; PreparedStatement updateTotal = con.prepareStatement(updateStatement); updateTotal.setInt(1, e.getValue().intValue()); updateTotal.setString(2, e.getKey()); 

The question marks in the above representation are placeholders.

Since these values ​​are passed as parameters, you have no problem citing, and it also protects you from SQL injection .

Answer 2

Try entering a name:

 if ( name.contains("'") ){ name.replaceAll("'", "''"); }