第35行的HTTP状态500

Question

PS：我知道用jsp编码java是不好的...

类型异常报告

消息处理JSP页面/assets/JSP/create-dest-code.JSP第35行时发生异常

说明服务器遇到内部错误，导致它无法完成此请求。

exception org.apache.jasper.jasperException：在处理JSP页面/assets/JSP/create-dest-code.JSP第35行时发生异常

根本原因javax.servlet.servletexception：com.mysql.jdbc.exceptions.mysqlsyntaxerroreception：“where clause”中的列“winter”未知org.apache.jasper.runtime.pagecontextimpl.dohandlepageexception（pagecontextimpl.java:909)org.apache.jasper.runtime.pagecontextimpl.handlepageexception（pagecontextimpl.java:838)

root cause com.mysql.jdbc.exceptions.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)（httpservlet.java:729)org.apache.tomcat.websocket.server.wsfilter.dofilter（wsfilter.java:52）

注意，Apache Tomcat/8.0.33日志中提供了根本原因的完整堆栈跟踪。创建-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>

create-dest-code.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

Win的SQL注入攻击漏洞：

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

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

SELECT ... Category_name=Winter

产生准确的错误消息。

忽略正确的SQL编写实践，您需要以下内容：

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