John V John V - 6 months ago 16
MySQL Question

HTTP Status 500 at line 35

When i am running create-dest.jsp and fill the form i am taking this error , anyone can help , and suggest me a solution?

PS: I know that coding java in jsp is bad...


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>


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 tables :

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

SQL injection attack vulnerabilities For The Win:

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

cat[i] is unquoted in the query, so you're passing in external output directly into the query, producing the following statement:

 SELECT ... Category_name=Winter

producing your exact error message.

Ignoring proper SQL writing practices, you need something like:

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

note the extra '.

Comments