Skip to content
Advertisement

Validating credentials using JDBC against PostgreSQL

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.

Advertisement

Answer

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.

Advertisement