Java Servlet/JSP: Checking whether role is NULL or admin in Database

Question

I have created the following function which is supposed to check whether the "role" column in my Database contains admin or is Null (which means it's a regular user). I try to test it in my Servlet class as shown in the code below but it redirects me to the USER JSP page every time. Is there any error in my checkRole() method? Thank you in advance.

`checkRole()` method

public static boolean checkRole() {

    boolean find = false;
    PreparedStatement pst = null;  
    ResultSet rs = null;  
    try(Connection conn= ConnectionConfiguration.getConnection()){
        pst = conn.prepareStatement("SELECT * FROM users WHERE role=?;");  
        pst.setString(1, role);  
        rs = pst.executeQuery();
        while (rs.next()) {
       if (rs.getString("role").equals("admin") {
           find = true;
            } else {find = false;}
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return find;  
}

SERVLET code

        { 
                String pass1 = request.getParameter("password");
                String email = request.getParameter("email");

            //checks whether user credentials are right and if it is admin
                if(User.validate(email,pass1) && User.checkRole()){

                    request.setAttribute("email",request.getParameter("email"));
                    request.setAttribute("pass", request.getParameter("password"));
                    s.invalidate();
                    forwardTo(ctx, request, response, "/Admin.jsp");
                }

     //checks whether user credentials are right and if it is a regular user
                else if (User.validate(email, pass1) && !User.checkRole()) {

                        request.setAttribute("email",request.getParameter("email"));
                        request.setAttribute("pass", request.getParameter("password"));
                        s.invalidate();
                        forwardTo(ctx, request, response, "/RegularUser.jsp");
                    }

                else {

                    //show some error message

                }
            }

score 0 · Answer 1 · answered Oct 26 '18 at 15:29

0

In the checkRole() method, you need to break out of the while loop after finding an admin, otherwise your "find" boolean will probably be set to false again on the next iteration.

answered Oct 26 '18 at 15:29

Conor Kelly

1
1

Avijit Barua · Answer 2 · 2018-10-27T11:28:56.140

0

Change your checkRole method like

public static boolean checkRole(String email) {

    boolean find = false;
    PreparedStatement pst = null;  
    ResultSet rs = null;  
    try(Connection conn= ConnectionConfiguration.getConnection()){
        pst = conn.prepareStatement("SELECT * FROM users WHERE email =? and role='admin';");  
        pst.setString(1, email);  
        rs = pst.executeQuery();
        if(rs.next()) {
           find = true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return find;  
}

And in your servlet code write if condition like

 if(User.validate(email,pass1) && User.checkRole(email))

edited Oct 27 '18 at 11:28

answered Oct 27 '18 at 07:26

Avijit Barua

2,950
5
14
35

your sql statement will result in an error. Missing asterisk (or field) – Jonathan Laliberte Oct 27 '18 at 10:49
Actually I wrote this answer from mobile , thats why I missed ! – Avijit Barua Oct 27 '18 at 11:30

Jonathan Laliberte · Accepted Answer · 2018-10-27T10:49:27.610

Your checkRole() method will always evaluate to true if you have more than 1 user with different roles in your users table. Because you're selecting all the rows where the field role is of a certain type. And if the certain role type exists in your users table, it will always be true...

Like the other answer has mentioned already, you need to pass a unique identifier. How else is the query supposed to know which user you are checking the role for? In most applications this is done by a user_id/id field, but since you only have email here, you can do use that also. I would do something like this:

public static boolean isAdmin(String email) {

boolean check = false;
PreparedStatement pst = null;  
ResultSet rs = null;  
try(Connection conn= ConnectionConfiguration.getConnection()){
    pst = conn.prepareStatement("SELECT * FROM users WHERE email =? and role='admin';");  
    pst.setString(1, email);  
    rs = pst.executeQuery();
   check = rs.next(); // if the resultSet has results, then check will evaluate to true

} catch (SQLException e) {
    e.printStackTrace();
}
return check;  
}

Then for your servlet:

       { 
                    String pass1 = request.getParameter("password");
                    String email = request.getParameter("email");

         //first check if valid login details (seperate it out so you can be more specific in the error you give back, and you don't have to repeat yourself)
         if(User.validate(email,pass1)){
             // s.invalidate(); //this isn't really necessary here, normally you invalidate the session variables when the user logs out. If a different user logs in (whilst one is already logged in), then any session variables you have set would override it.
              String url = "/RegularUser.jsp";
              String role = "regular";
              //now check if user is admin
              if(User.isAdmin(email)){
                url = "/Admin.jsp" 
                role = "admin";
              }
          //set your session variables
          //s.setAttribute("user_email", email);
          //s.setAttribute("user_role", role);

           forwardTo(ctx, request, response, url);

         }else{

        //wrong login details - set values back in form
        request.setAttribute("email",email); 
        request.setAttribute("pass", pass1);
        forwardTo(ctx, request, response, "/Login.jsp");
         }
}

Java Servlet/JSP: Checking whether role is NULL or admin in Database

checkRole() method

SERVLET code

3 Answers3

`checkRole()` method