javascript - 带有 SQL 查询问题的准备语句

Question

下面显示的代码用于搜索职位列表，添加了准备好的语句以防止 SQL 注入(inject)，该语句也很容易受到攻击。查询语句搜索在添加准备好的语句之前有效，但是现在不返回任何内容。谁能看到我做错了什么吗？谢谢。之前的代码如下所示:

try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/SQL_DB", "root", "root"); 语句 stmt = con.createStatement()) {
rs = stmt.executeQuery(queryStatement);

代码现在看起来像这样:

public List<jobs> searchjobs(@RequestParam String query) {
    log.debug("REST request to search jobs for query {}", query);


    String queryStatement = "SELECT * FROM jobs WHERE description like '%" + query + "%'";
    log.info("Final SQL query {}", queryStatement);

    ResultSet rs = null;

    try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/SQL_DB", "root", "root");
        PreparedStatement stmt = con.prepareStatement(queryStatement)) {
        stmt.setString(1, query);
        rs = stmt.executeQuery(queryStatement);
        return extractjobs(rs);
    } catch (SQLException ex) {
        log.error(ex.getMessage(), ex);
        return new ArrayList();
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            log.error(ex.getMessage(), ex);
        }
    }
}

Answer 1

您错过了准备好的声明的本质。使用它时，您用占位符替换所有变量，如下所示

String queryStatement = "SELECT * FROM jobs WHERE description like ?";

然后您可以像您一样绑定(bind)查询变量。只需先添加这些百分比字符即可

stmt.setString(1, "%" + query + "%");