How to get user roles using query in jsp?

Question

i am new in java i want role based access using switch statement but when i execute query and provide user role for example(user or admin) then user log-in well but if i leave (role='') empty in query then query not execute so well and jump in else statement.Second if i use prepared statement then how i can get user role thanks.Here is my code.

<%
  String userid = request.getParameter("username");   
  String pwd = request.getParameter("password");
  Class.forName("com.mysql.jdbc.Driver");
  Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/auto_lube","root", "password");

  Statement st = con.createStatement();
  ResultSet rs;
  rs = st.executeQuery("select * from users where uname='" + userid + "' and pass='" + pwd + "' and role=''");  

  if (rs.next()) {           
    String username =  rs.getString("uname");
    String email =  rs.getString("email");
    String rolle =  rs.getString("role");
    session.setAttribute("customer_name", username); 

    int usser =  rs.getInt("id");
    session.setAttribute("customer_id", usser);
    switch(rs.getInt(rolle)) {                  
      case 1:
        if (rolle.equals("admin")) {                  
          response.sendRedirect("adminPage.jsp");
          break;
        }

      case 2:
        if (rolle.equals("user")) {                 
          response.sendRedirect("user.jsp");
        }
        break;

      case 3:
        if (rolle.equals("assistant")) {                 
          response.sendRedirect("assistant.jsp");
        }
        break;

      case 4:
        if (rolle.equals("supplier")) {                 
          response.sendRedirect("supplier.jsp");
        }
        break;

      default:
        break;
    } 
  } else {
  out.print("invalid");
  } 
%>         

Answer 1

Congratulations, you put in only one piece of code a lot of bad practices !

You put a lot of scriptlet in JSP. You'd better use a servlet for this processing, and only use JSP for the view (display) part. Servlet are true Java classes and as such are easier to write and test.

You concatenate user input strings in a query without using prepared statements (but I acknowledged you noticed it :-) ). Never do that, it is a open trap for SQL injection (just google for it)

You store in database cleartext passwords. Good practices recommend to only store a non inversible hash of the password. If the database is compromised, the attacker cannot get passwords.

You impose role = '' in the query. I would be very surprised if you ever find an admin role with that !

You use the role for choosing to what page redirect. It is not by itself a bad practice, but it can be if you do not test that user has a valid role in redirected page. Common usage is to have one single page with parts displayed only according to role (<c:if> blocs in JSP).

And what means rs.getInt(rolle) ? You get rolle from result set, fine use it directly and test if it is null before doing any rolle.equals(...) (NPE is not far)

For the prepared statement, you could do:

PreparedStatement st = connection.prepareStatement("select * from users where uname=? and pass=?");
st.setString(1, userid);
st.setString(2, pwd);
ResultSet rs = st.executeQuery();

That way, not only you are protected from SQL injection, but you get a line from database from userid, pwd whatever the role can be.

Edit :

After that query, you can still read any attribute from users table :

  if (rs.next()) {           
    String username =  rs.getString("uname");
    String email =  rs.getString("email");
    String rolle =  rs.getString("role");
    session.setAttribute("customer_name", username); 

    int usser =  rs.getInt("id");
    session.setAttribute("customer_id", usser);

And the tests should be

if (rolle == null || role.isEmpty()) {
    // process no role case first - it deals with the null role problem
}
else if (role.equals("admin") {
    ...
}
...
else {
    // process for unknown role
}