SQL预编译语句中操作符绑定错误及安全实践

String sql = "SELECT * FROM total WHERE totals ? ?;";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, signString); // signString可能是">=", "<=", "="
stmt.setString(2, amount);
ResultSet rs = stmt.executeQuery();

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SqlOperatorBindingExample {

    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsupermarket", "root", "");

            // 假设type和amount是外部传入的参数
            int type = 1; // 示例：1表示大于等于
            String amount = "100"; // 示例：查询金额

            String signString = "";
            switch (type) {
                case 1: // greater or equal
                    signString = ">=";
                    System.out.println("1 selected (Greater than or Equal)");
                    break;
                case 2: // lesser or equal
                    signString = "<=";
                    System.out.println("2 selected (Lesser than or Equal)");
                    break;
                case 3: // equal
                    signString = "=";
                    System.out.println("3 selected (Equal)");
                    break;
                default:
                    throw new IllegalArgumentException("Invalid type provided.");
            }

            // 正确的做法：将操作符拼接进SQL字符串，只用 ? 绑定值
            // 注意：SQL语句末尾不需要分号
            String sql = "SELECT * FROM total WHERE totals " + signString + " ?";

            stmt = con.prepareStatement(sql);
            // 绑定值，这里amount是字符串，如果totals列是数值类型，可能需要转换为数值类型
            // 例如：stmt.setInt(1, Integer.parseInt(amount));
            stmt.setString(1, amount); 

            rs = stmt.executeQuery();

            // 处理查询结果
            while (rs.next()) {
                // 示例：打印所有列
                // System.out.println("ID: " + rs.getInt("id") + ", Totals: " + rs.getDouble("totals") + "...");
                System.out.println("Row found: " + rs.getString(1) + ", " + rs.getString(2)); // 示例输出
            }

        } catch (ClassNotFoundException e) {
            System.err.println("JDBC Driver not found: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 确保资源被关闭
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                System.err.println("Error closing resources: " + e.getMessage());
            }
        }
    }
}