Java Servlet执行SQL Insert报错：插入值被识别为不存在的列

问题根因 #

• 构造SQL语句时直接拼接字符串类型的参数，没有添加单引号包裹，PostgreSQL会将未加引号的字符串识别为列名而非字段值，因此抛出column "xxx" does not exist错误。你直接在数据库中执行SQL时会主动给字符串值加单引号，所以可以正常运行。
• 直接拼接SQL的写法存在SQL注入安全风险，属于不推荐的写法。

修复方案 #

临时修复（仅做原理演示，不推荐学习/生产使用） #

如果要保持拼接SQL的写法，需要给每个字符串参数前后加单引号，同时需要额外处理参数本身包含单引号的场景，否则仍会报错：

// 原错误写法：无单引号包裹字符串值
int result = stmt.executeUpdate("INSERT INTO testinserts (lastname, firstname, city) VALUES ("+ lastname + "," + firstname + "," + location + ");");
// 临时修改后
int result = stmt.executeUpdate("INSERT INTO testinserts (lastname, firstname, city) VALUES ('"+ lastname + "','" + firstname + "','" + location + "');");

标准正确写法（推荐） #

使用PreparedStatement预编译SQL，不需要手动处理引号、转义等问题，同时可以完全避免SQL注入风险：

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String firstname = request.getParameter("firstname");
    String lastname = request.getParameter("lastname");
    String location = request.getParameter("location");
    // 用?作为参数占位符
    String insertSql = "INSERT INTO testinserts (lastname, firstname, city) VALUES (?, ?, ?)";
    try {
        // 预编译SQL语句
        PreparedStatement pstmt = con.prepareStatement(insertSql);
        // 按顺序给占位符赋值，setString会自动处理字符串的引号、转义逻辑
        pstmt.setString(1, lastname);
        pstmt.setString(2, firstname);
        pstmt.setString(3, location);
        int result = pstmt.executeUpdate();
        PrintWriter writer = response.getWriter();
        if(result > 0){
            writer.println("<H1>" + firstname + " created " + "</H1>");
        } else {
            writer.println("<H1>" + "none created " + "</H1>");
        }
        // 使用完成后关闭Statement资源，避免资源泄漏
        pstmt.close();
    } catch (SQLException ex) {
        PrintWriter writer = response.getWriter();
        writer.println(ex.getMessage());
        StackTraceElement[] stackTrace = ex.getStackTrace();
        writer.println("<HTML>");
        for(StackTraceElement st : stackTrace){
            writer.println("<p style=\"color: red\">" + st.toString() + "</p>");
        }
        writer.println("</HTML>");
    }
}

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