Check if username exists in table with password

Question

I have created a login system that check for tries before blocks an account in case password fails more than three times. The problem is that I need to check if the username exists and if not, print a message saying that the id doesn't exists; but I don't know what to do. I mean, I have the idea of how to do it, but I don't know how to do it on Java.

Theese are my stored precedures

Login

create proc usp_Login
   @id varchar(50),
   @pw varchar(50)
as
   select id, pw from Login where id = @id and pw = @pw and lock = 0 and tries < 4
   update Login set tries = 0 where id = @id and pw = @pw;

Attempts

create proc usp_Attempts
   @id varchar(50)
as
   select id, tries from Login where id = @id and tries < 4
   update Login set tries = tries+1 where tries < 4 and id = @id

Block user

create proc usp_Lock
   @id varchar(50)
as
   update Login set lock = 1 where id = @id

And the java part is like this:

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        CallableStatement cStmt = null;
        ResultSet rs = null;
        try {
            cStmt = conn.prepareCall("{call usp_Login(?, ?)}");
            cStmt.setString(1, this.txtID.getText());
            cStmt.setString(2, this.txtPW.getText());
            cStmt.execute();

            rs = cStmt.getResultSet();

            if (rs.next()) {
                System.out.println("Login done");
                cStmt.close();
                rs.close();
                conn.close();
            } else {
                cStmt = conn.prepareCall("{call usp_Attempts(?)}");
                cStmt.setString(1, this.txtID.getText());
                cStmt.execute();

                rs = cStmt.getResultSet();

                if (rs.next()) {
                    System.out.println("Invalid login");
                } else {
                    cStmt = conn.prepareCall("{call usp_Lock(?)}");
                    cStmt.setString(1, this.txtID.getText());
                    cStmt.execute();

                    System.out.println("Login blocked");
                    cStmt.close();
                    rs.close();
                    conn.close();
                }
            }
        } catch (SQLException ex) {
            System.out.print(ex);
        }
    }

I know that maybe this is not the best way to do a login system, but I'm still learning and this is by now the best idea I got about doing it. Hope someone can help me to do the id verification part, thanks in advance.

Answer 1

Put all this logic into one single stored procedure (SP). At the very end of the body of this SP just do SELECT v AS RESULT; where v is some integer value. Make this value v different depending on the login attempt outcome e.g.

• v=0 means login succeeded;
• v=1 means login failed as there's no such username;
• v=2 means login failed as the account was already locked when this login was attempted;
• v=3 means login failed as the password does not match, but the SP did not lock the account;
• v=4 means login failed as the password does not match, and also the SP has just locked the account due to too many tries.

Of course through the RESULT value, you can encode other login attempt outcomes in any way you like. Then in the Java code just inspect the SP RESULT value to know exactly what happened inside it during this login attempt. That is in the Java code you will get a result set with 1 row and 1 column called RESULT, just inspect its value and proceed the way you like.