How to resolve Sonar warning - Use a variable binding mechanism for SELECT BETWEEN

Question

I'm getting error while performing Sonar for my code:

Use a variable binding mechanism to construct this query instead of concatenation
"SELECT ipfrom, ipto, city
from ipAddTable where " + ipAddress   + " between ipfrom and ipto";

For SELECT equal to something, we can use LIKE ? For BETWEEN statement, I'm not sure.

Any idea on how to solve it?

@JoeTaras ipAddress is a parameter passing value from another function. — inayzi, Mar 17 '21 at 07:51
As you are using JDBC I would suggest you need to use PreparedStatements here. It looks like Sonar is telling you your method of constructing dynamic SQL isint best practice. Its probably open to SQL injection attacks as well. — Gavin, Mar 17 '21 at 07:52
Is `ipAddress` represented as a string (e.g. `'127.0.0.1'`) or as a number (`0x7F000001`), in the first case `BETWEEN` may not be quite appropriate to check the IP ranges. — Nowhere Man, Mar 17 '21 at 09:12

score 0 · Answer 1 · answered Mar 22 '21 at 08:48

Just try the comment given. Using Prepared Statement.

Sonar doesnt complaint anymore.

sql = "SELECT ipfrom, ipto, city from ipAddTable where ? " +  "between ipfrom and ipto";
            
            pst = conn.prepareStatement(sql);
            pst.setLong(1, ipAddress);
            rs = pst.executeQuery();

How to resolve Sonar warning - Use a variable binding mechanism for SELECT BETWEEN

1 Answers1