如何修改代码避免SQL注入并实现双字段可选模糊搜索？

需要实现对businessName和businessAddress两个字段的模糊搜索，字段均可为null，查询规则如下：

• 当businessName="name"且businessAddress="address"时，执行：select * from business where businessName like '%name%' and businessAddress like '%address%'
• 当businessName=null且businessAddress="address"时，执行：select * from business where businessAddress like '%address%'
• 当businessName=null且businessAddress=null时，执行：select * from business

现有代码存在SQL注入风险，希望通过PreparedStatement.setString()避免注入，但不想编写多分支的冗余代码，询问修改方案。

现有代码：

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

可以通过动态构建SQL语句+维护参数列表的方式解决，既避免SQL注入，又无需多分支冗余代码，具体实现如下：

StringBuilder sb = new StringBuilder("select * from business where 1=1 ");
List<String> params = new ArrayList<>();

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

try {
    con = DBUtil.getConnection();
    pst = con.prepareStatement(sb.toString());
    // 统一设置参数
    for (int i = 0; i < params.size(); i++) {
        pst.setString(i + 1, params.get(i));
    }
    rs = pst.executeQuery();
} ...

方案说明 #

1. 用?作为占位符替代直接拼接字符串，彻底规避SQL注入风险
2. 用List<String>存储需要设置的参数，每个非null字段对应的模糊查询参数（带前后%）加入列表
3. 最后通过循环遍历参数列表，给PreparedStatement按顺序设置参数，无需根据字段数量编写多分支逻辑
4. 保留原有的where 1=1逻辑，方便动态添加条件，无需处理第一个条件是否要加and的问题

内容的提问来源于stack exchange，提问作者sakura