Resultset automatically close itself and showing error Operation not allowed after ResultSet closed

Question

Getting Exception

java.sql.SQLException: Operation not allowed after ResultSet closed

<%
ResultSet rs=null,rs1=null;
Statement stmt=null,stmt1=null;
String UserID = request.getParameter("UserID"); 
String Password = request.getParameter("Password");
session.setAttribute("UserID",UserID);
int flag=0;
try{    
    System.out.println("Validating..1");
    //stmt =  con.createStatement();
    //stmt1 =  con.createStatement();

    String Query = "select * from login where UserID = '"+UserID+"' and Password='"+Password+"'";
    System.out.println(Query);
    rs = st.executeQuery(Query);
    System.out.println(rs);
    if(rs!=null)
    {
        String Query1="Select * from basicdetails where UserID='"+UserID+"' and Password='"+Password+"'";
        System.out.println(Query1);
        rs1=st.executeQuery(Query1);
        //System.out.println(rs1);
        if(!rs1.next())
        {
            System.out.println("RS1");
            while(rs1.next())
            {
                String PhotoPath=rs1.getString(4);
                System.out.println("-------------------"+PhotoPath);
                session.setAttribute("PhotoPath",PhotoPath);
            }
        }
    }
    if(!rs.next())
    {
        String Auth=rs.getString(3);
        session.setAttribute("Auth",new Integer(Auth));
        flag=1;
        if(Auth.equals("0"))
        {
            //Show Admin Menu
        %>
        <jsp:forward page="AdminMenu.jsp"/>
    <%
    }
        else if(Auth.equals("1"))
        {
            //Show user Menu
    %>
    <jsp:forward page="UserHome.jsp"/>
    <%
    }
    }
    else
    {
        flag=0;
    %>
    <jsp:forward page="Login.jsp"/>
    <%
    }
    stmt.close();
    con.close();
}catch(Exception e){
            System.out.println(e.getCause());
                        System.out.println(e.getStackTrace());
                        System.out.println(e.getClass());
            %><%=e%><%
        }

%>

And Here is the message shown in output window of Netbeans

Validating..1 select * from login where UserID = 'admin' and Password='admin' com.mysql.jdbc.JDBC4ResultSet@b2c1e7 Select * from basicdetails where UserID='admin' and Password='admin' RS1 null [Ljava.lang.StackTraceElement;@1d8608c class java.sql.SQLException

What is the issue with the above code which is .jsp file trying to validate login credential of admin and other users.

The glaring problem is java code inside a jsp. Try refactoring to use tag libraries or jstl expressions and it might get easier to debug — andrewdleach, Feb 26 '16 at 18:47
@ElliottFrisch Even if I delete both the last 2 line `stmt.close();` and `con.close();` still getting same error — cbsecommerce, Feb 26 '16 at 18:53
Master password: `' OR 1=1 --`. You need to switch to using [prepared statements](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). — Mick Mnemonic, Feb 26 '16 at 19:10
Every body is mentioning their own views no one clearing my doubt. Simply I'm asking Why rs1 close by itself what is the reason. I am not asking for solution — cbsecommerce, Feb 26 '16 at 20:00

score 0 · Answer 1 · answered Feb 26 '16 at 19:23

0

You are executing ResultSet.next() after it has already returned false.

This code doesn't make any sense. You are checking rs == null where it cannot be null. ResultSets returned by executeQuery() cannot be null. You could do the whole thing with a single query and a join. Why you would have a password field in two tables is a mystery, or else the second query is a bug.

answered Feb 26 '16 at 19:23

user207421

305,947
44
307
483

Why rs cannot be null if id & password is wrong then rs will be null – cbsecommerce Feb 26 '16 at 20:03
1

The result set would be _empty_, not `null`, if the query returned no rows. – Mick Mnemonic Feb 26 '16 at 20:06
Ok fine rs cannot be null, then is there any way to check whether rs is empty or not? – cbsecommerce Feb 26 '16 at 20:46
Seriously? Have you read the Javadoc? `ResultSet.next()` returns false when there are no more rows. If there are zero rows it returns false the first time you call it. – user207421 Feb 27 '16 at 01:23
@EJP Thank you EJP atleast you mentioned the reason, but I got the answer by reading Java Doc yesterday only and I issued the problem. – cbsecommerce Feb 27 '16 at 08:49
You got the answer which I had already mentioned by reading the Javadoc which you should have already done before you posted the question, and you stated here that nobody gave you the reason, which is false. – user207421 Feb 27 '16 at 09:04

score -1 · Accepted Answer · answered Feb 27 '16 at 08:54

I changed the whole code which I posted in my question, after getting answers from you all experts. We as a beginner expect your right guidance and proper clarification that's all. Here is the code -

<%
ResultSet rs=null,rs1=null;

    String UserID = request.getParameter("UserID"); 
    String Password = request.getParameter("Password");
    session.setAttribute("UserID",UserID);
    int flag=0;
    try{    
        System.out.println("Validating..1");
        int Auth=1,flag1 = 0;

        String Query = "select * from login where UserID = '"+UserID+"' and Password='"+Password+"'";
        String Que = "select * from basicdetails where userid='"+UserID+"' and password='"+Password+"';";
        System.out.println(Que);
        rs = st.executeQuery(Query);
        int i=0;
        while(rs.next())
        {
            if(UserID.equals(rs.getString(1)) && Password.equals(rs.getString(2)))
            {
               Auth=rs.getInt(3);
               session.setAttribute("Auth",new Integer(Auth));
            }
            i++;
        }
        rs.close();

        if(i>0)
        {
         System.out.println("I " + i);
            if(Auth==1)
            {
                System.out.println("USER Verification");
            rs1= st.executeQuery(Que);
            System.out.println(rs1);
            rs1.next();
            String PhotoPath=rs1.getString(4);
            System.out.println("-------------------"+PhotoPath);
            session.setAttribute("PhotoPath",PhotoPath);
            %>
                <jsp:forward page="UserHome.jsp"/>
            <%
            }
            else if(Auth==0)
            {
            %>
                <jsp:forward page="AdminMenu.jsp"/>
            <%
            }
        }
        else
        {
            %>
            <jsp:forward page="Login.jsp"/>
            <%
        }        
        st.close();
        con.close();
    }
    catch(Exception e)
    {
    %>
      <%=e%>
    <% } %>

Thank you everybody, and sorry for little harsh. Thank you again.

The user ID and password are already equal to the supplied parameters, by the terms of the query. You don't need to test them again. You can't ignore the result of `ResultSet.next().` You still haven't explained why you have the password column in two tables. — user207421, Feb 27 '16 at 09:03
@EJP UserID is Primary key in both the table, I tried to use join in SQL and successfully done also for but for admin login ONLY join doesn't work, because admin login detail is not in basicdetails table. So for users join works fine, but for admin join query fail. Here is the query `String Query = "select l.UserID,l.Password,l.Auth,b.UserID,b.Password,b.PhotoPath from login l,basicdetail b where l.UserID = '"+UserID+"' and l.Password='"+Password+"' and l.UserID = b.UserID;` this is fine for user in basicdetail table but for admin this will not work. — cbsecommerce, Feb 28 '16 at 02:28

Resultset automatically close itself and showing error Operation not allowed after ResultSet closed

2 Answers2