java - 错误 1064 : SQL syntax error

Question

我在 java 代码中有一个 SQL 代码，如下所示:

    Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        beforeExerTestDTO dto = new beforeExerTestDTO();

        StringBuffer sql = new StringBuffer();
        sql.append(" select * ");
        sql.append(" from n_before_exer ");
        sql.append(" where id=?");
        sql.append("    and reg_date = (select max(reg_date) from n_before_exer where id=?)");

        try {
            con = pool.getLocalConnection();
            pstmt = con.prepareStatement(sql.toString());
            pstmt.setString(1, id);
            pstmt.setString(2, id);
            System.out.println("여기까진 살까??");
            rs = pstmt.executeQuery();
            /......
            ...... some code /
            }catch(SQLException e){
            System.out.println("read : " + e);
            System.out.println("read : " + sql);
        }catch(Exception e){
            System.out.println("read : " + e.getStackTrace().toString());
        }finally{
            DBClose.close(con, pstmt, rs);
        }
        return dto;
}

当文件被执行时，它会在控制台中形成如下语句:

select *  from n_before_exer  where id=?    and reg_date = (select max(reg_date) from n_before_exer where id=?)

并抛出一个

java.sql.SQLEXCEPTION

我尝试过:

1. 我在 Mysql Workbench 查询中运行了相同的查询:

并出现以下错误:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? and reg_date = (select max(reg_date) from n_before_exer where id=?)' at line 1

对该主题的一些研究表明:

1. 这种方式不是首选方式，因为它可能导致注入(inject)攻击
2. 并建议对参数使用占位符

这对我来说似乎有点复杂，如果有人可以帮助我以正确的首选方式构建此声明

谢谢

Answer 1

您应该使用准备好的语句:

Connection con; // get a connection
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, someInt);
ps.setInt(2, someOtherInt);

ResultSet rs = ps.executeQuery();
while (rs.next()) {
    // process each record
}