Java/JDBC-使用JDBC PreparedStatement进行多参数搜索

Question

使用JDBC准备语句创建多参数搜索，以防止SQL注入攻击，提高性能。因为我在网上找不到最好的方法。

我试着自己实现如下。

null

    PreparedStatement stat = conn.prepareStatement(sql.toString());

    public class EmpDAO {

        public static List<Employee> findByCriteria(Employee e)
                throws SQLException, IOException {

            try (Connection conn = getConnection()) {

                StringBuilder sql = new StringBuilder("SELECT * FROM Employee ");

                //collect user supplied parameters
                Map<String, String> params = new HashMap<String, String>();
                if (e.getFname() != null && e.getFname().length() != 0) {
                    params.put(EmpDAO.FNAME, e.getFname());
                }
                if (e.getLname() != null && e.getLname().length() != 0) {
                    params.put(EmpDAO.LNAME, e.getLname());
                }

                if (e.getDepid() > 0) {
                    params.put(EmpDAO.DEPT_ID, new Integer(e.getDepid()).toString());
                }

                //construct prepared statement based on the parameters

                Set<String> colSet = params.keySet();
                if (colSet != null && !colSet.isEmpty()) {
                    StringBuilder whereClause = new StringBuilder(" WHERE");
                    String andOp = "";
                    for (String colName : colSet) {                 
                        whereClause.append(andOp);
                        whereClause.append(" ");
                        whereClause.append(colName);
                        whereClause.append("=? ");
                        andOp = " AND ";
                    }

                    sql.append(whereClause);
                }

                PreparedStatement stat = conn.prepareStatement(sql.toString());
                int paramPos = 1;
                for (String colName : colSet) {
                    if (colName.equals(EmpDAO.FNAME)) {
                        stat.setString(paramPos, params.get(colName));
                    }

                    if (colName.equals(EmpDAO.LNAME)) {
                        stat.setString(paramPos, params.get(colName));
                    }

                    if (colName.equals(EmpDAO.DEPT_ID)) {
                        stat.setInt(paramPos, Integer.parseInt(params.get(colName)));
                    }
                    paramPos++;
                }

                List<Employee> emp1 = new ArrayList<>();
                try (ResultSet result = stat.executeQuery()) {

                    while (result.next()) {
                        Employee emp = new Employee();
                        emp.setDepid(result.getInt("DEPT_ID"));
                        emp.setEmpid(result.getInt("EMP_ID"));
                        emp.setFname(result.getString("FNAME"));
                        emp.setJobid(result.getInt("JOB_ID"));
                        emp.setLname(result.getString("LNAME"));
                        emp.setMangid(result.getInt("MANAGER_EMP_ID"));
                        emp.setSalary(result.getInt("SALARY"));
                        emp1.add(emp);
                    }
                }
                return emp1;
            }
        }

        public static Connection getConnection() throws SQLException, IOException {
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException ex) {
                ex.printStackTrace();
            }
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/test",
                    "root", "root");
        }

        public static final String FNAME = "FNAME";
        public static final String LNAME = "FNAME";
        public static final String DEPT_ID = "FNAME";
    }

Answer 1

是的--正如所写的那样，这将防止SQL注入攻击，是的，这就是使用.PrepareStatement的方式。如果您希望对它的实现方式进行批评，那么您可能应该将此代码发布到https://codereview.stackExchange.com/上。