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?

what you just got is an example of sql injection, consider using parameters instead of string concatenation, this will escape any special characters in your input — sameh.q, Jan 12 '16 at 08:39

score 3 · Accepted Answer · answered Jan 12 '16 at 08:39

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

score 3 · Answer 2 · answered Jan 12 '16 at 08:43

3

Use a prepared statement (and for added clarity of named bind variables you can use an OraclePreparedStatement):

String s = "select ref(ad) from adresse_tab ad where ad.ort=:ort and ad.plz=:plz and ad.land=:land";
PreparedStatement st5 = nsdCon.prepareStatement(s);
OraclePreparedStatement ost5 = (OraclePreparedStatement) st5;
ost5.setStringAtName("ort",rs.getString(11))
ost5.setStringAtName("plz",rs.getString(13))
ost5.setStringAtName("land",rs.getString(14))
ResultSet rs5 = st5.executeQuery();

answered Jan 12 '16 at 08:43

MT0

143,790
11
59
117

Good point. Named parameters are much more readable, and maintainable. I wish Java would provide them out of the box rather than having to rely on [external deps](http://stackoverflow.com/q/1097855/1782481). – varevarao Jan 12 '16 at 08:51

score 1 · Answer 3 · edited May 23 '17 at 12:15

1

You should not add your query parameters directly to the query string. Use a Prepared Statement instead and pass the query parameters there. See also Does the preparedStatement avoid SQL injection?

edited May 23 '17 at 12:15

Community

1
1

answered Jan 12 '16 at 08:34

David Tanzer

2,732
18
30

score 1 · Answer 4 · answered Jan 12 '16 at 08:37

The whole point of prepared statements is to use parameters within your query so values can be automatically escaped:

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));
stmt5.setString(2, rs.getString(13));
stmt5.setString(3, rs.getString(14));

ResultSet rs5 = stmt5.executeQuery();

score 0 · Answer 5 · answered Jan 12 '16 at 08:35

0

ad.strasse='almundo l'''tare '

answered Jan 12 '16 at 08:35

Vampiro

335
4
15

The problem is how can i handle this, if i do not know in advance that this comparison could happen . – user3232446 Jan 12 '16 at 08:38
If you're using a variable, oracle will take care of it and you won't have an issue - but you had hard coded. – Vampiro Jan 12 '16 at 08:40
Please use the edit link on your question to complement it with context, detailed information and/or code examples. The _Post Answer_ button should be used only for **complete answers to the question**. – mathielo Jan 12 '16 at 11:28

How can handle reserved character in SQL query

5 Answers5