How can I modify the code to avoid SQL-injection attack?

Question

I need to implement a fuzzy search for two fields, businessName and businessAddress. Both of them can be null. If one field is null, search should be based on the other field.

To be specific,

• if businessName="name" and businessAddress="address" then execute select * from business where businessName like '%name%' and businessAddress like '%address%'
• if businessName=null and businessAddress="address" then execute select * from business where businessAddress like '%address%'
• if businessName=null and businessAddress=null then execute select * from business

My code:

StringBuilder sb = new StringBuilder("select * from business where 1=1 ");

if (businessName != null) {
    sb.append("and businessName like '%" + businessName + "%' ");
}
if (businessAddress != null) {
    sb.append("and businessAddress like '%" + businessAddress + "%' ");
}

try {
    con = DBUtil.getConnection();
    pst = con.prepareStatement(sb.toString());
    rs = pst.executeQuery();
} ...

Apparently it's in danger of SQL-injection attack. I know method prepareStatement.setString() can avoid attack, but number of fields is uncertain before verification.

How can I modify it? Separate method for each case or code like below seem ugly.

if(businessName!=null){
    if(businessAddress!=null){
        sql = ...;
    }else {
        sql = ...;
    }
else{
...

Answer 1

Never, ever, concatenate values into a query string like that. Always use prepared statements with parameters when executing queries, especially with user-sourced values.

A simple solution for your case is to use a list of values for each parameter you add, and then set the values collected for those parameters before execute:

StringBuilder sb = new StringBuilder("select * from business where 1=1 ");
List<String> parameters = new ArrayList<>();
if (businessName != null) {
    sb.append("and businessName like '%' || ? || '%' ");
    parameters.add(businessName);
}
if (businessAddress != null) {
    sb.append("and businessAddress like '%' || ? || '%' ");
    parameters.add(businessAddress)
}

try (Connection con = DBUtil.getConnection();
     PreparedStatement pst = con.prepareStatement(sb.toString())) {
    int index = 1;
    for (String parameter : parameters) {
        pst.setString(index++, parameter);
    }
    
    try (ResultSet rs = pst.executeQuery()) {
        // ...
    }
}

If you have parameters of varying types, use a List<Object> and setObject instead.

---

The solution in the answer by MT0 also works, but not all database systems optimize that type of query well (especially if you have a lot of such conditions), which might affect performance. For only a few conditions, the solution by MT0 is more readable, while having same/similar performance.