JDBC PreparedStatement用?指定列名失效，报invalid number错误求助

Ah, this is a classic JDBC pitfall! Let's break down why your code is throwing that invalid number error, then fix it properly.

The Root Cause #

PreparedStatement placeholders (?) are only designed to replace values in your SQL query — they cannot be used to substitute SQL structural elements like column names, table names, or keywords.

When you run your current code, here's what actually happens:

• Your SQL becomes something like SELECT * FROM sample WHERE 'sampleid' = 123 (assuming you passed sampleid as colName).
• The database tries to compare the string literal 'sampleid' with the integer 123, which causes the invalid number error (it can't convert the string to a number for comparison).

The Fix: Safe Column Name Handling #

Since we can't use placeholders for column names, we need to directly include the column name in the SQL string — but we must protect against SQL injection first. The safest way is to use a whitelist of allowed column names to ensure only valid columns are used.

Here's the revised code:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashSet;
import java.util.Set;

public ResultSet findSampleByAnyCol(String colName, Integer sampleId, Connection con) {
    ResultSet rs = null;
    
    // Step 1: Validate column name against a whitelist to prevent SQL injection
    Set<String> allowedColumns = new HashSet<>(Arrays.asList("sampleid", "other_col1", "other_col2"));
    // Adjust toLowerCase() if your database uses case-sensitive column names
    if (!allowedColumns.contains(colName.toLowerCase())) {
        throw new IllegalArgumentException("Invalid column name: " + colName);
    }

    // Step 2: Safely concatenate the validated column name into the SQL
    String sql = String.format("SELECT * FROM sample WHERE %s = ?", colName);
    
    try(PreparedStatement pstmt = con.prepareStatement(sql)) {
        pstmt.setInt(1, sampleId); // Keep using placeholder for the value (still safe!)
        rs = pstmt.executeQuery();
    } catch(SQLException e) {
        e.printStackTrace();
    }
    return rs;
}

Key Notes: #

• Whitelist Validation: We only allow column names that exist in our allowedColumns set. This stops attackers from injecting malicious SQL (like sampleid; DROP TABLE sample; --).
• Case Insensitivity: Using toLowerCase() ensures the check works even if the caller passes a mixed-case column name (remove this if your database enforces case-sensitive column names).
• Value Placeholder: We still use a placeholder for the sampleId value — this retains the core benefits of prepared statements: preventing injection for values and improving query caching performance.

Why Not Just Concatenate Without a Whitelist? #

If you skip the whitelist and directly concatenate colName into the SQL, you're opening yourself up to SQL injection attacks. For example, if someone passes sampleid; DROP TABLE sample; -- as colName, your database could execute that destructive command. Always validate user input when building dynamic SQL!

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