CODEWITHSUNDEEP
javasqloracle-databasejsporacle12c
Rajesh Acharya
Rajesh Acharya

Reputation: 55

Unable to retreive value from oracle database using JSP, getting java.sql.SQLException: Result set after last row

I have written simple jsp program where I am trying to retrieve username and password from database, here is my jsp program.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import ="java.sql.*" %>
<%@ page import ="oracle.sql.*" %>
<%@ page import ="oracle.jdbc.driver.*" %>
<%@page import="oracle.jdbc.driver.OracleDriver"%>
<%@ page import ="java.util.Date" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
String driver="oracle.jdbc.driver.OracleDriver";
String userid = request.getParameter("username"); 
String pwd=request.getParameter("password"); 

Connection con = null;




%>
<%try
{
Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:CCB25", "CISADM", "CISADM");
Statement st= con.createStatement();
String query="select * from CMLOGIN where USERID='"+userid+"'";
ResultSet rs=st.executeQuery(query); 
String pass1="";
rs.next();


pass1 = rs.getString("password");

if(pass1.equals(pwd))
{ 
%>
<%String name=request.getParameter("username");
session.setAttribute("nam",name);%> 
<jsp:forward page="admin.jsp"></jsp:forward>
<%}
else
{
String msg="Username or password failed";%>
<center> <p style="font-family:verdana;color:red;"><%=msg %>
<jsp:include page="Login.jsp"></jsp:include>

<%}
}
catch(Exception e)
{
e.printStackTrace();
}
%>

</body>

and i have javax.servlet.jar and ojdbc7-12.1.0.2.jar in web-inf/lib folder. I am getting java.sql.SQLException: Result set after last row on the following line "pass1 = rs.getString("password");" Could you please guide me what I am doing wrong.

Upvotes: 0

Views: 296

Answers (2)

Kris Rice
Kris Rice

Reputation: 3410

Here is the same cleaned up a little. You'll have to add back in exception handling and closing of the connection/statement/resultset

  • Hopefully you never have to use this code for real as it's taking in a password from query string in clear then comparing to a clear version in the db.
  • Password comparison moved to the sql itself.
  • Always use PreparedStatements to avoid sql injection

Comments are in the code.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ page import ="java.sql.*" %>
<%@ page import ="java.util.Date" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
String userid   = request.getParameter("username"); 
String pwd      = request.getParameter("password"); 

Connection con       = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/XE", "klrice", "klrice");

//
// USE BINDS ! to avoid sql injection
// push the userid and passwd comparison to the db 
// no need to get the password and compare locally
// 
String query         = "select password from cmlogin where userid=? and password=?";

PreparedStatement st = con.prepareStatement(query);

// 
//  Binds in the vaules
// 

st.setString(1,userid);
st.setString(2,pwd);

ResultSet rs         = st.executeQuery(); 

String pass1;

//  .next will advance if the query has any results
// 

if ( rs.next() ) {
            pass1 = rs.getString("password");
      String name =request.getParameter("username");
      session.setAttribute("nam",name);
%> 
    <jsp:forward page="admin.jsp"></jsp:forward>
<%
    } else {
        String msg="Username or password failed";
%>
        <center> <p style="font-family:verdana;color:red;"> <%=msg %>
        <jsp:include page="Login.jsp"></jsp:include>
<%  }  %>
</body>

Upvotes: 1

Daniel B.
Daniel B.

Reputation: 2601

You first need to check if the ResultSet has any rows in it before calling .next(), in order to prevent this exception.

Use this:

if (rs.next){
  //read next row
}

if you want to loop through all the rows of the ResultSet, use a while loop:

while(rs.next){
  //do something for every row
}

Upvotes: 2

Related Questions