0

I want to validate a SHA2 hash that I stored in my MySQL database (column named hash).

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(150) UNIQUE NOT NULL,
    role TINYINT NOT NULL,
    salt VARCHAR(255) NOT NULL,
    hash VARCHAR(255) NOT NULL
);

My insert statements for users:

INSERT INTO users(email, role, salt, hash) 
VALUES ('test@gmail.com', '0', 'salt' ,SHA2(CONCAT ('partOne', 'partTwo', 'salt',), 224), '1'),

The value that is to be checked against the stored hash comes from two parameters given in the servlet (called partOne and partTwo) and salt that is individually stored for each record (column salt). I tried to do this with the following MySQL prepared statement in the servlet:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE hash = SHA2(CONCAT (?, ?, salt), 224)");
ps.setString(1, partOne);
ps.setString(2, partTwo);

However, this does not work (no errors but it does not return any record). So my question is can I include something that I am selecting (i.e. the salt in the SHA2 or do I need to first execute a statement where I select the salt for that user and then pass it through a '?' like the other variables? I thought this would be fine since the condition also includes the 'hash' column so I don't see a problem with it. Or is there some error in my syntax for preparedstatements?

Update:

From the comments I understand that this is a bad plan. So my question is what is the best way to securely store something like a password for a user login/logout system to protect a webapp? The idea that I had tried came from this post.

Alfie Danger
  • 377
  • 5
  • 23
  • 2
    Using SHA2 hashes for passwords is an extremely bad plan. Is this a new system or some legacy one? – tadman Jul 15 '20 at 00:37
  • 2
    Not only does this use a really weak hash, SHA2 is intended for high speed not resistance to brute forcing, but this implementation requires testing **every single user in your database** for a possible match. If you have a non-trivial number of users this will abuse your database server severely and this would be easy to weaponize into a Denial of Service attack since a password attempt would have a huge server-side expense. You should be testing one user and one user *only*. – tadman Jul 15 '20 at 00:39
  • 2
    (1) Don't invent security protocols. (2) Don't implement security protocols. There are simpler ways to do this that are substantially more secure; please provide details about your use case. (At a minimum, you should be using the Spring Security `BcryptPasswordEncoder` or equivalent as a library even if you don't use any of the rest of it.) – chrylis -cautiouslyoptimistic- Jul 15 '20 at 00:39
  • 2
    One more thing: Make email address fields `VARCHAR(255)` by default. Some people have utterly ridiculous email addresses. – tadman Jul 15 '20 at 00:40
  • thanks for all the advice! This is just something I am developing to learn more MySQL and db design! I want to use the hash that is stored as a password for the user and I used this [posts answer](https://stackoverflow.com/questions/9971532/how-to-use-aes-encrypt-properly) to come to this idea that I posted in my question. I will look into BcryptPasswordEncoder thanks for the advice! Could you point me in the direction of some useful links? I have never used BcryptPasswordEncoder. – Alfie Danger Jul 15 '20 at 01:00
  • if there is something that would be even better to use would love to hear it! as mentioned this is something I am developing to learn for myself how to do certain things and what are good practices! cheers!! – Alfie Danger Jul 15 '20 at 01:01
  • 1
    If you're learning about DB design that's great, but the **number one** thing about passwords is to store using an appropriate *password-specific hash* like BCrypt. The documentation of that encoder is but a search away. If you're storing user data you have a huge responsibility to do it properly so that should your database leak for some reason it isn't trivial to [crack the passwords](https://hashcat.net/hashcat/) and weaponize your database. – tadman Jul 15 '20 at 02:06
  • ok thanks again! I am reading into using spring-boot and the spring security – Alfie Danger Jul 15 '20 at 10:52
  • since my approach wasn't valid should I delete this question? or do you think this might be helpful for others? – Alfie Danger Jul 15 '20 at 22:24

0 Answers0