如何在Java中使用JdbcTemplate编写无SQL注入漏洞的动态SQL查询

漏洞原因 #

• 代码直接将用户输入的customerName、customerNumber参数拼接进SQL语句，攻击者可以构造包含单引号、SQL关键字的恶意输入篡改查询逻辑，引发SQL注入风险。
• 额外存在边界异常问题：如果两个查询参数都为空，whereClause.substring会因为找不到AND关键字抛出索引越界异常。

修复方案 #

核心思路：动态拼接SQL时仅拼接代码可控的SQL结构部分，所有用户输入的参数全部用?占位符代替，通过JdbcTemplate的参数绑定传入执行，JDBC预编译机制会自动处理特殊字符转义，彻底避免注入风险。

调整后完整代码 #

import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.RowMapper;
import com.google.common.base.Strings;
import java.sql.ResultSet;
import java.sql.SQLException;

public List<Customer> fetchCustomers(CustomerRequestBean customerRequst, int startIndex) {
    customerDetailsRequst request = customerRequst.getCustomerDetailsRequst();
    List<Customer> customers = new ArrayList<>();
    
    // 获取拼接好的预编译SQL和对应参数列表
    Map.Entry<String, List<Object>> queryInfo = getFramedQuery(request);
    String query = queryInfo.getKey();
    List<Object> paramList = queryInfo.getValue();
    // 把startIndex放到参数列表最前面，对应SQL中第一个?占位符
    paramList.add(0, startIndex);

    try {
        customers = jdbcTemplate.query(query, paramList.toArray(), new RowMapper<Customer>(){
            @Override
            public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
                Customer cust = new Customer();
                cust.setCustomerName(rs.getString("CUSTOMER_NAME"));
                cust.setCustomerNumber(rs.getString("CUSTOMER_NUMBER"));
                cust.setCustomerId(rs.getInt("CUSTOMER_ID"));
                cust.setStatus(rs.getString("STATUS"));
                cust.setGsa(rs.getString("GSA_INDICATOR"));
                return cust;
            }
        });
    }catch (Exception e) {
        System.out.println("Error is ... "+e);
    }
    return customers;
}

private Map.Entry<String, List<Object>> getFramedQuery(customerDetailsRequst request) {
    List<Object> params = new ArrayList<>();
    List<String> conditions = new ArrayList<>();

    // 处理客户名查询条件
    if (!Strings.isNullOrEmpty(request.getCustomerName())) {
        String upperName = request.getCustomerName().toUpperCase();
        if(upperName.contains("%")) {
            conditions.add("UPPER(CUSTOMER_NAME) LIKE ?");
        }else {
            conditions.add("UPPER(CUSTOMER_NAME) = ?");
        }
        params.add(upperName);
    }

    // 处理客户编号查询条件
    if (!Strings.isNullOrEmpty(request.getCustomerNumber())) {
        String upperNum = request.getCustomerNumber().toUpperCase();
        if(upperNum.contains("%")) {
            conditions.add("UPPER(CUSTOMER_NUMBER) LIKE ?");
        }else {
            conditions.add("UPPER(CUSTOMER_NUMBER) = ?");
        }
        params.add(upperNum);
    }

    // 拼接完整SQL，兼容无动态条件的场景
    StringBuilder sqlBuilder = new StringBuilder("SELECT CUSTOMER_NAME,CUSTOMER_NUMBER,CUSTOMER_ID,STATUS,GSA_INDICATOR FROM CUSTOMERS_DETAILS WHERE CUSTOMER_ID >= ?");
    if (!conditions.isEmpty()) {
        sqlBuilder.append(" AND ").append(String.join(" AND ", conditions));
    }
    sqlBuilder.append(" ORDER BY customer_id");

    return new AbstractMap.SimpleEntry<>(sqlBuilder.toString(), params);
}

修复说明 #

• 所有用户可控的输入内容全部走参数绑定，不会直接拼入SQL，从根源避免注入风险
• 用条件集合拼接的方式替代原有的字符串截取逻辑，解决了无查询参数时的索引越界异常
• SQL中的列名、操作符全部为代码固定生成，不存在用户可控的拼接项，无安全风险

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