How can handle reserved character in SQL query

Question

I want to read data from a table but I got a error because the value I want to compare may contain a word like this: abcd l'jdmd

I try it like this:

String s = "select ref(ad) from adresse_tab ad where ad.ort='"+rs.getString(11)+"' and ad.plz='"+rs.getString(13)+"' and ad.land='"+rs.getString(14)+"'";
        
PreparedStatement stmt5 = nsdCon.prepareStatement(s);
ResultSet rs5 = stmt5.executeQuery();

The query could look like this:

select ref(ad) 
  from adresse_tab ad 
 where ad.ort='Frankfurt am Main' 
   and ad.plz='65301' 
   and ad.land='Deutschland' 
   and ad.strasse='almundo l'tare '

So the problem in this query is this comparison:

ad.strasse='almundo l'tare '

How can I handle reserved character in SQL query?

Answer 1

Please avoid creating a SQL query with supplied parameters using string concatenation. Instead you can continue using PreparedStatement, but use placeholders for the actual param values, and use the statement's set<X>() methods for setting params. Here's official Oracle docs on this.

You must supply values in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. Do this by calling one of the setter methods defined in the PreparedStatement class. The following statements supply the two question mark placeholders in the PreparedStatement named updateSales:

updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey()); The first argument for each of these setter methods specifies the question mark placeholder. In this example, setInt specifies the first placeholder and setString specifies the second placeholder.

For your case:

String s = "select ref(ad) from adresse_tab ad where ad.ort=? and ad.plz=? and ad.land=?";

PreparedStatement stmt5 = nsdCon.prepareStatement(s);
stmt5.setString(1, rs.getString(11));
... and so on