Converting VARBINARY values into String in a Select Query in MySQL?

Question

According to this answer, I should save my String BCrypt hashed password, passHash, as a BINARY or BINARY(60) (I chose BINARY(60)) when storing it in my MySQL table (I stored it in a column named passHash).

Now when I select and retrieve that passHash column value from my table into Java, it is now a byte[] data type.

How do I then convert it back to its String form so that I can validate it using my validateLogin() method below:

//Validate username and password login
    public boolean validateLogin(String username, String userpass) 
    {
        boolean status = false;  
        PreparedStatement pst = null; 
        ResultSet rs = null;  

        User user = new User(); //This is my Java bean class

        try(Connection connect= DBConnection.getConnection())
        {
            //Here, passHash is stored as VARBINARY(60) 
            pst = connect.prepareStatement("SELECT * FROM user WHERE username=? and passHash=?;"); 

            pst.setString(1, username);  
            pst.setString(2, userpass);  
            //Here's where I'm having difficulty because `passHash` column in my user table is VARBINARY(60) while `userpass` is a String

            rs = pst.executeQuery(); 
            status = rs.next();  
        } 

        catch (SQLException e) 
        {
            e.printStackTrace();
        }
            return status;  //status will return `true` if `username` and `userpass` matches what's in the columns
    }

The parameters username and userpass are being used to get the user's input in my Login.jsp form:

String username = request.getParameter("username");
String userpass = request.getParameter("userpass");

EDIT: My BCrypt code is as follows:

//returns a hashed String value
public static String bCrypt (String passPlain) {
        return BCrypt.hashpw(passPlain, BCrypt.gensalt(10));
    }

//Returns a true if plain password is a match with hashed password
public static Boolean isMatch(String passPlain, String passHash){
        return (BCrypt.checkpw(passPlain, passHash));
    }

Have a look at this link https://auth0.com/blog/hashing-in-action-understanding-bcrypt/ — Scary Wombat, Nov 20 '18 at 01:54
Also as per https://docs.spring.io/spring-security/site/docs/4.2.5.RELEASE/apidocs/org/springframework/security/crypto/bcrypt/BCrypt.html the values are actually `Strings` so maybe the [linked answer](https://stackoverflow.com/questions/5881169/what-column-type-length-should-i-use-for-storing-a-bcrypt-hashed-password-in-a-d) is not correct? — Scary Wombat, Nov 20 '18 at 01:58
@ScaryWombat So you suggest I change my `passHash1 data type into a `VARCHAR(60)` instead? — James McTyre, Nov 20 '18 at 02:00
actually I don't see what the fuss is, String to byte use `String::getBytes` byte to String use `new String (bytes)` store in the DB however you want — Scary Wombat, Nov 20 '18 at 02:02

Tim Biegeleisen · Accepted Answer · 2018-11-20T03:33:51.183

0

When you create your user accounts, you must be hashing the passwords in some way to generate a byte[] in Java, which you then insert into your user table.

public static byte[] bCrypt (String passPlain) {
    return BCrypt.hashpw(passPlain, BCrypt.gensalt(10)).getBytes();
}

// here is how you generate the hash
byte[] hashed = bCrypt(userpass).toBytes();

// here is how you authenticate a login
String password; // from the UI
String sql = "SELECT passHash FROM user WHERE username = ?";
pst = connect.prepareStatement(sql);
pst.setString(1, username);
rs = pst.executeQuery();

if (rs.next()) {
    byte[] hash = rs.getBytes(1);
    if (isMatch(password, new String(hash))) {
        // authenticate
    }
}

The pattern for checking an existing password is to pass in the plain text password and the hash.

edited Nov 20 '18 at 03:33

answered Nov 20 '18 at 01:28

Tim Biegeleisen

502,043
27
286
360

My hash function returns a hashed String value instead of a byte[] (as noted in my EDIT above). When I save and insert this String value in the `passHash` column in my MySQL table, i used `setString(myHashedStringPassword)`. – James McTyre Nov 20 '18 at 01:35
@JamesMcTyre My answer does not change much. It isn't clear why your hashing function returns a `String` when your table is storing a `byte[]`. – Tim Biegeleisen Nov 20 '18 at 01:45
Hi, I appreciate all your help. But if I try to refactor my `bCrypt()` into returning a `byte()` I will have to do more work backwards because `hashpw()` is supposed to return a `String`. Is there any way I can just convert the original returned `String` into a `byte[]` for my `bCrypt()`? – James McTyre Nov 20 '18 at 01:53
Yes, I already told you this, use `String#getBytes()`. But, if your schema requires a binary input, then why not have your code be consistent? I mean, what are you doing with a hashed password string besides sticking it into your user table? – Tim Biegeleisen Nov 20 '18 at 01:54
I pinpointed the problem: `pst.setBytes(2, hashed)` (or `pst.setString(2, hashedString)`) is comparing the actual string on a character-by-character basis. For example, if the value stored my `passHash` column is `$2a$10$gnzKum16RdSXjoNk5fdv7u` then login would only work if `hashedString` (or its equivalent converted byte[] `hashed`) is also EXACTLY `$2a$10$gnzKum16RdSXjoNk5fdv7u`. The problem is: BCrypt generates many different hashes for the same password. So a password like `helloWorld` can generate the above hash or a different hash. And that's where I'm stuck. – James McTyre Nov 20 '18 at 02:42
`return (BCrypt.checkpw(passPlain, passHash));` should take care of that – Scary Wombat Nov 20 '18 at 02:58

Converting VARBINARY values into String in a Select Query in MySQL?

1 Answers1