Validating credentials using JDBC against PostgreSQL

Question

So I've been trying to get my credentials to be validated when I log in to my server. This is the simple set of data I am trying to validate.

(456789, 'Dave123', 'password', 'Dave', 'Davidson', 'dave@dadavid', 2), (123456, 'John456', '123456', 'John', 'Johnson', 'john@jojohn', 1), (456878, 'Kate789', 'abcdef', 'Kate', 'Kateson', 'kate@kitkat', 1)

public class LoginService {
//username and password with id identifier
    //0 for false, 1 for employee and 2 for manager
    public boolean login(String username, String password, int id) {
        
        
        
try(Connection connection = ConnectionUtil.getConnection()) {
            
            ResultSet resultSet = null; // intialize an empty resultset that will store the results of our query.
            //sql statement to get all of the info from reimbursement
            String sql = "select * from users where username =? and pass=? and user_role_id=?"
                    + "values (?,?,?)";
            
            
            PreparedStatement preparedStatement = connection.prepareStatement(sql); 
            
            preparedStatement.setString(2, username );
            preparedStatement.setString(3, password );
            preparedStatement.setInt(7, id);
            
            if(username.equals(username) && password.equals(password) && id == (1) ) {
                return true;
            }
            if(username.equals(username) && password.equals(password) && id == (2) ) {
                return true;
            }
        
        }catch (Exception e) {
        // TODO: handle exception
    }
return false;
    }
}

So when it finishes validating, if the username and password are in the database it would return true. Otherwise, it would return a false, and not let the user log in. But currently, all it does is returning false, and not letting the user log in.

I tried to run this on postman and it would accept the values and would let me log in, but trying this on the live server would reject it.

 <input id="username" type="text" placeholder="username" class="col-sm-4 form-control">
 <input id="password" type="password" placeholder="password" class="col-sm-4 form-control"> 
 <input id="id" type="number" placeholder ="id" class="col-sm-4 formcontrol">

This is what I have in my html.

Your preparedStatement.setXxx() calls seem to miss a couple of parameters. I assume you left out those calls but if not you're very likely getting an exception since the query has incomplete parameters. Also, //TODO: handle exception is something you should fix first since right now you're swallowing it and thus you don't get any info. At least print the stacktrace if you can't handle it in another way right now. And btw, catch specific exceptions for more specific handling than just logging. — Thomas
– Thomas, Commented Jul 20, 2021 at 6:26
Other problems with your code: 1) you're not even executing the prepared statement so it can'd do anything. 2) username.equals(username) && password.equals(password) basically compares the parameters to themselves and that would always be true (unless one is null and you get a NPE). 3) Instead of comparing the credentials in your code use the query anc check whether it returns something or not. 4) For better security have a look at password hashing and salting or ideally into using an identity provider like Keycloak. — Thomas
– Thomas, Commented Jul 20, 2021 at 6:29

score 4 · Accepted Answer · 2021-07-20 06:45:34Z

Your SQL query is wrong to begin with. But more importantly you are not even running that query nor are you processing the result of the query.

The values clause for a select statement is invalid. So remove it.

String sql = "select * from users where username =? and pass=? and user_role_id=?";

You only have three parameters, so you need to pass them with the numbers 1,2 and 3 (not 2,3,7)

preparedStatement.setString(1, username );
preparedStatement.setString(2, password );
preparedStatement.setInt(3, id);

Then you must use execute() to actually run the query and receive the result:

ResultSet rs = preparedStatement.execute();

The values returned are then retrieved from the ResultSet not from the statement, after you called next(), e.g.:

if (rs.next()) {
  String nameFromDb = rs.getString("username");
  String pwdFromDb = rs.getString("password");
  int id = rs.getInt("id");
}

But as all three parameters are part of the WHERE clause, you will never get a result where the values are different than your input. So all you need is to check if the query did return a row:

if (rs.next()) {
  return true;
}

You need to close the preparedStatement() before you return though, to avoid resource leaks in the database.

Collectives™ on Stack Overflow

Validating credentials using JDBC against PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related