java - 第 35 行的 HTTP 状态 500

Question

当我运行 create-dest.jsp 并填写表单时出现此错误，任何人都可以提供帮助并建议我解决方案吗？

PS:我知道用jsp写java不好...

type Exception report

message An exception occurred processing JSP page /assets/jsp/create-dest-code.jsp at line 35

description The server encountered an internal error that prevented it from fulfilling this request.

exception org.apache.jasper.JasperException: An exception occurred processing JSP page /assets/jsp/create-dest-code.jsp at line 35

32: //out.print(id); 33: if ( cat != null) { 34: for(int i=0;i } else {

Stacktrace: org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:574) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:461) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340) javax.servlet.http.HttpServlet.service(HttpServlet.java:729) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

root cause javax.servlet.ServletException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'Winter' in 'where clause' org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:909) org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:838) org.apache.jsp.assets.jsp.create_002ddest_002dcode_jsp._jspService(create_002ddest_002dcode_jsp.java:173) org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) javax.servlet.http.HttpServlet.service(HttpServlet.java:729) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:438) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340) javax.servlet.http.HttpServlet.service(HttpServlet.java:729) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

root cause com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'Winter' in 'where clause' com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) com.mysql.jdbc.Connection.execSQL(Connection.java:3118) com.mysql.jdbc.Connection.execSQL(Connection.java:3047) com.mysql.jdbc.Statement.executeQuery(Statement.java:1166) org.apache.jsp.assets.jsp.create_002ddest_002dcode_jsp._jspService(create_002ddest_002dcode_jsp.java:140) org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) javax.servlet.http.HttpServlet.service(HttpServlet.java:729) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:438) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340) javax.servlet.http.HttpServlet.service(HttpServlet.java:729) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

note The full stack trace of the root cause is available in the Apache Tomcat/8.0.33 logs. create-dest.jsp

<form name="createdest" method="get" action="../jsp/create-dest-code.jsp">
        Country: <input type="text"  required  name="id8" /> <br>
        City: <input type="text"  required  name="id9" /> <br>
        URL Video: <input type="url"  required  name="id10" /> <br> <br>
        <i><ins>Categorize the destination (max 2): </ins></i>  <br> <br>
        <input type="checkbox" name="dest1" value="Winter"  >Winter<br>
        <input type="checkbox" name="dest1" value="Christmas"  >Christmas <br>
        <input type="checkbox" name="dest1" value="Summer"  >Summer <br> <br>

        <input type="submit" class="button" value="CREATE DESTINATION" >  /> 
       <input type="hidden" name="varname" value="" />
        </form>

创建目标代码.jsp

<%
String id8=request.getParameter("id8"); //Country field  
String id9=request.getParameter("id9"); //City field
String id10=request.getParameter("id10"); //URL field
String cat[]=request.getParameterValues("dest1"); //categories
int status, id , id1=0 ;

Class.forName("com.mysql.jdbc.Driver"); 
String myDatabase = "jdbc:mysql://localhost:3306/project_app?user=root&password=1234"; 
Connection myConnection = DriverManager.getConnection(myDatabase);
Statement myStatement = myConnection.createStatement();  
Statement myStatement2 = myConnection.createStatement();  

String sqlInsert = "INSERT INTO dest(Country,City,URL) VALUES ('"+id8+"', '"+id9+"','"+id10+"')";
status = myStatement.executeUpdate(sqlInsert);
if (status ==0 ) {
    throw new SQLException("INSERT INTO DEST FAILED!");
}

ResultSet rs = myStatement.executeQuery("SELECT idDest FROM dest WHERE Country='"+id8+"' AND City='"+id9+"' AND URL='"+id10+"'" );
if (rs.next()) {
    id=rs.getInt(1); 

    if ( cat != null) {
        for(int i=0;i<cat.length;i++) {
            rs = myStatement.executeQuery("select idCategories from categories where Category_name="+cat[i]+" ");
            if (rs.next()) {
                    id1 = rs.getInt(1);
            } else {
                throw new SQLException("ERROR");
            }
            status = myStatement2.executeUpdate("INSERT INTO Dest_has_Categories (Dest_idDest,Categories_idCategories) VALUES ('"+id+"','"+id1+"')");
            if ( status ==0 ) {
                throw new SQLException("INSERT INTO DEST_HAS_CATEGORIES FAILED!");
            }
        }
    }
} else {
    throw new SQLException("INSERT INTO DEST FAILED! NO ID OBTAINED!");
}
myStatement.close();
myStatement2.close();
myConnection.close();
%>

mysql 表:

CREATE TABLE IF NOT EXISTS `project_app`.`Dest` (
  `idDest` INT NOT NULL AUTO_INCREMENT,
  `Country` VARCHAR(45) NOT NULL,
  `City` VARCHAR(45) NOT NULL,
  `URL` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idDest`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Categories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_app`.`Categories` (
  `idCategories` INT NOT NULL,
  `Category_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idCategories`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Dest_has_Categories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project_app`.`Dest_has_Categories` (
  `Dest_idDest` INT NOT NULL AUTO_INCREMENT,
  `Categories_idCategories` INT NOT NULL,
  PRIMARY KEY (`Dest_idDest`, `Categories_idCategories`),
  INDEX `fk_Dest_has_Categories_Categories1_idx` (`Categories_idCategories` ASC),
  INDEX `fk_Dest_has_Categories_Dest_idx` (`Dest_idDest` ASC),
  CONSTRAINT `fk_Dest_has_Categories_Dest`
    FOREIGN KEY (`Dest_idDest`)
    REFERENCES `mydb`.`Dest` (`idDest`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Dest_has_Categories_Categories1`
    FOREIGN KEY (`Categories_idCategories`)
    REFERENCES `mydb`.`Categories` (`idCategories`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO categories(idCategories,Category_name) VALUES (1,'Winter');
INSERT INTO categories(idCategories,Category_name) VALUES (2,'Christmas');
INSERT INTO categories(idCategories,Category_name) VALUES (3,'Summer');

Answer 1

SQL 注入(inject)攻击漏洞 For The Win:

rs = myStatement.executeQuery("[..snip..] Category_name="+cat[i]+"");

cat[i] 在查询中未被引用，因此您将外部输出直接传递到查询中，生成以下语句:

SELECT ... Category_name=Winter

生成您的确切错误消息。

忽略正确的 SQL 编写实践，您需要像这样的东西:

rs = myStatement.executeQuery("[..snip..]Category_name='"+cat[i]+"' ");
                                                       ^----------^

注意额外的 '。