Juan M Juan M - 1 year ago 78
SQL Question

User Authentication with Razor MVC + Mysql

I'm trying to code a simple authentication function for a user/password stored in a Mysql database. Thing is I'm new to c# so please excuse any flaws that may occur in my code snippet. What I have so far is:

User Registration (tested and saving record to DB):

public User saveUser(User user)
MySqlConnection conn = this.connect();

var password = "";
var sha256 = "";
var returnString = "";

var salt = Crypto.GenerateSalt();
sha256 = Crypto.SHA256(user.password);
password = Crypto.HashPassword(salt + sha256);

string sql = "INSERT INTO user(last_name ,first_name, email, password, city_id) "
+ "VALUES (?lastName,?firstName,?email,?password,?cityId); ";

MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add("?lastName", MySqlDbType.VarChar).Value = user.lastName;
cmd.Parameters.Add("?firstName", MySqlDbType.VarChar).Value = user.firstName;
cmd.Parameters.Add("?email", MySqlDbType.VarChar).Value = user.email;
cmd.Parameters.Add("?password", MySqlDbType.VarChar).Value = password;
cmd.Parameters.Add("?cityId", MySqlDbType.VarChar).Value = user.city.cityId;

returnString = "Success";
catch (Exception e)
returnString = "Error when executing method " + System.Reflection.MethodBase.GetCurrentMethod().Name
+ ". Possible Cause: " + e.Message;


return new User(user.lastName, user.firstName, user.email,
new City(user.city.cityId, user.city.cityDescription)); }

AND User Validation (which I don't think is quite right)

public bool authenticateUser(string email, string pass)
MySqlConnection conn = this.connect();

var sha256 = Crypto.SHA256(pass);
var hashedPw = Crypto.HashPassword(sha256);

string query = "SELECT u.password FROM user u WHERE u.email = @email AND u.password = @pass";

MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("email ", email);
cmd.Parameters.AddWithValue("password ", hashedPw);

bool output = false;

int totalCount = Convert.ToInt32(cmd.ExecuteScalar());
if (totalCount == 0)
output = true; //login failed
output = false; //login succeeded
catch (MySqlException e)
Console.WriteLine("Error executing method " + System.Reflection.MethodBase.GetCurrentMethod().Name
+ ". Possible Cause: " + e.Message);

return output;

Once again, any help will be greatly appreciated. Thanks!!!

Answer Source

I'm trying to code a simple authentication function for a user/password stored in a Mysql database.

Firstly, some Internet/Programming points go to you; for seeing the opportunity to use a hash when storing passwords. It is a major nuance to me when I learn that people/companies are storing passwords in Clear Text or simply using Encryption (who pays these people!?).

Below, I outline the key points you need to consider when storing and retrieving hashed passwords.

How I Store A Hashed Password:
This is the only way I store a password in the database - I understand there maybe others - please don't judge.

There are 3 things you will need to consider when storing the User in the database:

  • Store the Salt, so that you can later retrieve it
  • Know the exact pattern that you're using to concatenate the input password and salt, to generate the hash in the first place (e.g. salt + inputPassword)
  • Store the HashedPassword, so that you can later retrieve it

You're along the right lines with your idea of generating a Salt to use when hashing the new user's password. Only thing is, you're not actually storing it anywhere - this is essential to then validating the user's credentials.

More on that, below.

How Do I Retreive The Password?:
As I'm sure you already know, the only information you will know about a user's password is its hash, that you have saved in the database. Couple this with the randomly generated salt, and you have yourself a very secure password.

Remember the points I made about "Storing the Salt" in your database; and remembering how you put this Salt and the original Password together? This is where you will need that.

The only way to check that the Login and Stored passwords match, is to run the same process as you did when creating the user. Except this time you don't generate a new Salt; you use the one stored against the user in the database (that's why we stored it).

Like so:

  • First, verify that the user is actually existent in the database (a bit obvious) and retrieve the data against them
  • Using their stored Salt, concatenate this with the password they have input in the same way you did when generating the hash (in the above example, I used salt + inputPassword, so we will go with this)
  • Then, you can generate a hash based on this concatenation: var loginPasswordHash = Crypto.HashPassword(salt + inputPassword);
  • If you're using exactly the same method you first used to generate the hash, the loginPasswordHash should then be exactly the same as the one in the database (providing it is correct) - e.g. if (loginPasswordHash == storedPasswordHash)...
  • You can perform a check on this and if they match, then you know they've got their password correct; otherwise, well, you know it's wrong

Unlike Encryption - which is reversible and uses a private key - or Plain Text (what the hell, really?), you will never be able to actually find out what the original password was, just from the salt and the hash.
Unless you're not using a salt, then using some pretty neat rainbow lookup tables could get you the password - a hash maybe only one way, but it always generates the same end-result every time.

How to apply the above principles in your code:
First things first, you're currently not storing the generated salt in the database when creating a user.

Do it.

You will need a new column in your MySQL table for Salt. Create it.

In your SaveUser method, also make sure you save the salt you're generating, in the database:

var salt = Crypto.GenerateSalt();
sha256 = Crypto.SHA256(user.password);
password = Crypto.HashPassword(salt + sha256);

string sql = "INSERT INTO user(last_name ,first_name, email, password, salt, city_id) "
             + "VALUES (?lastName,?firstName,?email,?password,?salt,?cityId); "; // Add 'salt' in here

MySqlCommand cmd = new MySqlCommand(sql, conn);
// ...
cmd.Parameters.Add("?password", MySqlDbType.VarChar).Value = password;
cmd.Parameters.Add("?salt", MySqlDbType.VarChar).Value = salt; // Save the 'salt' value

In your AuthenticateUser method, retrieve the known User details from the database, in order to check authentication.

Then, using the same method you did when generating the password hash to store in the database, you can use this to generate another hash to check against when logging in:

var salt = user.salt; // Salt from the DB
sha256 = Crypto.SHA256(login.password); // Hash their input password
loginPasswordHash = Crypto.HashPassword(salt + sha256); // Generate a hash of this, just like you did when creating the user

You can then simply run a check against the password in the database:

var actualHash = user.password; // Get the hashed password from the DB
if (loginPasswordHash == actualHash)
    // Password is correct
    // Password was incorrect

Last Note: One last thing I'm always picky about is this.

When a login attempt is made and the username and/or password is incorrect, what do you display?

I would advise to display one error, and one error only:

"The login credentials you supplied were incorrect"

This helps to fend off potential hackers that want to check the validity of potential usernames in the first place.

Well, it's been a long old journey.

Nevertheless, I hope this helps you in your project :)

Regards, Geoff

Although the examples and dialogue above outlines the answer to your original question; this is with thanks to @zaph, for pointing out how secure the hashing is/should be:

This is not close to secure, the hash must be iterated to require a minimum amount of CPU time. Iterate over an HMAC with a random salt for about a 100ms duration. Use functions such as password_hash, PBKDF2, Bcrypt and similar functions. The point is to make the attacker spend a lot of time finding passwords by brute force.

See OWASP (Open Web Application Security Project) Password Storage Cheat Sheet and How to securely hash passwords, The Theory on Security Stackexchange.

For more information see:

This should convince anyone that just hash protected passwords provided essentially no security. My laptop can compute 750,000 HMACs per second. Salting protects against one cracked hash revealing all similar passwords

I hope this also helps you with how securely you generate your hashes :)

Again, credit to @zaph for pointing this out.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download