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
– sameh.q, Commented Jan 12, 2016 at 8:39

varevarao · Accepted Answer · 2016-01-12 08:39:35Z

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

MT0 · Accepted Answer · 2016-01-12 08:43:01Z

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, 2016 at 8:43

MT0

173k12 gold badges70 silver badges136 bronze badges

1 Comment

varevarao Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:15:23Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered Jan 12, 2016 at 8:34

David Tanzer

2,74219 silver badges30 bronze badges

Comments

shmosel · Accepted Answer · 2016-01-12 08:37:38Z

1

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();

answered Jan 12, 2016 at 8:37

shmosel

51.1k7 gold badges81 silver badges148 bronze badges

Comments

Vampiro · Accepted Answer · 2016-01-12 08:35:10Z

0

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

answered Jan 12, 2016 at 8:35

Vampiro

3354 silver badges15 bronze badges

3 Comments

user3232446 Over a year ago

The problem is how can i handle this, if i do not know in advance that this comparison could happen .

Vampiro Over a year ago

If you're using a variable, oracle will take care of it and you won't have an issue - but you had hard coded.

mathielo Over a year ago

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.

Collectives™ on Stack Overflow

How can handle reserved character in SQL query

5 Answers 5

Comments

1 Comment

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related