修复SQL注入漏洞：解决PreparedStatement参数自动加单引号问题

Ah, I see exactly where you went wrong here—you’re trying to pass the entire filter clause as a single string parameter to PreparedStatement, which treats it as a literal value. That’s why it wraps your condition in single quotes, turning your logical WHERE check into a useless comparison against a string instead of evaluating the expression you need. Let’s fix this properly while keeping your query safe from SQL injection.

Step 1: Refactor Filter Clause Generation #

We’ll create a helper method that generates both a parameterized WHERE fragment (using ? placeholders for user input) and collects the corresponding parameter values. This keeps the SQL safe and avoids injection risks.

First, define a simple helper class to hold our result (you can also use a Pair if you’re on Java 8+ or use a library like Apache Commons):

private static class FilterQueryParts {
    String whereFragment;
    List<Object> parameters;

    FilterQueryParts(String whereFragment, List<Object> parameters) {
        this.whereFragment = whereFragment;
        this.parameters = parameters;
    }
}

Now rewrite your filter builder to use placeholders and validate inputs:

private FilterQueryParts buildFilterClause(List<String[]> filters) {
    StringBuilder whereBuilder = new StringBuilder();
    List<Object> params = new ArrayList<>();
    Iterator<String[]> filterIterator = filters.iterator();

    // Critical: Whitelist allowed columns to prevent column name injection
    Set<String> allowedColumns = new HashSet<>(Arrays.asList(
        "mrbts", "siteid", "address", "state", "status", "plan", "remarks", "parent_id"
    ));

    while (filterIterator.hasNext()) {
        String[] filter = filterIterator.next();
        String columnName = filter[ScFilterCriteria.FILTER_PARM_COL]; // Column from filter
        String filterValue = filter[ScFilterCriteria.FILTER_PARM_VAL]; // User input value

        // Validate column name to block malicious inputs
        if (!allowedColumns.contains(columnName)) {
            throw new IllegalArgumentException("Invalid column name: " + columnName);
        }

        // Add parameterized condition (adjust operator if you need other filters like =, >)
        whereBuilder.append("(UPPER(")
                    .append(columnName)
                    .append(") LIKE UPPER(?))");
        // Wrap value with wildcards and add to parameters list
        params.add("%" + filterValue + "%");

        if (filterIterator.hasNext()) {
            whereBuilder.append(" AND ");
        }
    }

    return new FilterQueryParts(whereBuilder.toString(), params);
}

Step 2: Assemble and Execute the Query #

Now build the full SQL statement, attach the filter clause (only if there are filters), and set parameters correctly:

public ResultSet getFilteredArchiveData(List<String[]> filters) throws SQLException {
    String baseSql = """
        SELECT coalesce(parent_id, siteid) as siteid, address, state, status, plan, remarks 
        FROM archive 
        LEFT OUTER JOIN site_mappings ON site_dn = mrbts AND siteid = child_site_id
        """;

    FilterQueryParts filterParts = buildFilterClause(filters);
    String fullSql = baseSql;

    // Add WHERE clause only if there are active filters
    if (!filterParts.whereFragment.isEmpty()) {
        fullSql += " WHERE " + filterParts.whereFragment;
    }

    try (Connection connection = getYourConnection(); // Replace with your connection logic
         PreparedStatement ps = connection.prepareStatement(fullSql)) {

        // Set each parameter in the order they appear in the WHERE clause
        int paramIndex = 1;
        for (Object param : filterParts.parameters) {
            ps.setObject(paramIndex++, param);
        }

        // Execute and return the result set (or process it directly here)
        return ps.executeQuery();
    }
}

Key Safety & Functionality Notes #

1. Column Name Whitelisting: We added a check for allowed columns to block attackers from injecting malicious column names (like mrbts) OR 1=1 --). Never trust user-provided column names without validation.
2. Parameterized Values: All user input is passed as PreparedStatement parameters, which JDBC handles safely—no more risky string concatenation of user data.
3. Flexible Filter Support: If your filters use different operators (e.g., =, >, <), extend the buildFilterClause method to store and use the operator from your filters list.

Why Your Original Approach Failed #

When you set the entire filter string as a single parameter, JDBC treats it like any other string value. Your query ended up looking like this:

SELECT ... WHERE '((UPPER(mrbts) like UPPER('%6105%')))'

This checks if the string literal evaluates to TRUE (which it never does in SQL) instead of executing the logical condition you intended.

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