Calling MySql Function in C# for validating username and password

ahmed_one

Development Environment: Microsoft Visual Studio 2010 Ultimate, C#, MySql Hi, I've create a function in mysql which accept 3 parameter to validate username and password.

DELIMITER $$

USE `generalledger`$$

DROP FUNCTION IF EXISTS `fLogin_Check`$$

CREATE DEFINER=`root`@`localhost`
FUNCTION `fLogin_Check`
(mUserName VARCHAR(50),mUserPass VARCHAR(40),mUserKey VARCHAR(40)) RETURNS INT
BEGIN
DECLARE mCount INT;

SELECT COUNT(*) INTO mCount FROM userMaster
WHERE userName = mUserName
AND AES_DECRYPT(userPass, mUserKey) = UPPER( mUserPass);

IF mCount > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$

DELIMITER ;

As you can see I am using AES_DECRYPT function of MySql to check password, because I've use AES_ENCRYPT for password when INSERT username and password to mysql table. Now I need to call the function fLogin_Check in C#, which I am doing by using following class method:

public int CheckUser(string mUserName, string mPass, string mKey)
{
oCn = da.GetConnection();

int res;

if (oCn == null)
{
    oCn.Open();
}

sInsProcName = "fLogin\_Check";
insertcommand = new MySqlCommand(sInsProcName, oCn);
insertcommand.CommandType = CommandType.StoredProcedure;
insertcommand.Parameters.Add(new MySqlParameter("mRes", MySqlDbType.Int32, 0));
insertcommand.Parameters\["mRes"\].Direction = ParameterDirection.ReturnValue;
insertcommand.Parameters.Add("mUserName", MySqlDbType.VarChar, 50, mUserName);
insertcommand.Parameters.Add("mUserPass", MySqlDbType.VarChar, 40, mPass);
insertcommand.Parameters.Add("mUserKey", MySqlDbType.VarChar, 40);
insertcommand.Parameters\["mUserKey"\].Value = mKey;

res = insertcommand.ExecuteNonQuery();
//res = int.Parse(insertcommand.Parameters\["mRes"\].Value.ToString());

return (res);

oCn.Close();

}

oCn is the connection abject which uses to call GetConnection method define in my DAL class and da is the object created from DAL class, use to opening and closing database connection. Using following Global class I am storing username and password after user enter them, and then try to validating with fLogic_Check Mysql function:

public static class Globals
{
public static string userName;
public static string userPass;
public const string sKey = "AHMEDFINANCEICMAP1122";
}

sKey is the key I use to encrypt password when insert username. Now I am

Bernhard Hiller

I do not know why your function fails, but I want to tell you that you use the wrong concept: Passwords have to be salted and hashed, not encrypted!

ahmed_one

Thanks for your reply... I've found lots of suggestion for encrypt the password is far better then salted and hashed method..But for now that is not the matter, I am looking for solution for MySql function not working in C#. Any ideas/suggestion for that??

Richard Deeming

ahmed_one wrote:

I've found lots of suggestion for encrypt the password is far better then salted and hashed method

Where? The consensus from every decent security expert is that salted hashed passwords are far superior to encrypted passwords. Even if the encryption key isn't compromised, encryption tends to produce the same output given the same input, which can make it trivial to compromise a large number of user's passwords. For example: http://nakedsecurity.sophos.com/2013/11/04/anatomy-of-a-password-disaster-adobes-giant-sized-cryptographic-blunder/[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Lost User

ahmed_one wrote:

I've found lots of suggestion for encrypt the password is far better then salted and hashed method

Anyone who understands the difference can point out that this is incorrect. If you store my password (encrypted), then YOU (and your boss, and everyone else in your company, and all subcontractors, and the cleaning-lady when you're AFK) will be able to decrypt the password. Next to having access to all the data you're supposed to secure, there's a huge chance that the user recycled one of his old passwords. Enough databases leaked with encrypted passwords to give us a nice and huge dictionary. Salt and hash, or omit the password entirely; the only thing worse than no security is a FALSE sense of security.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

ahmed_one

when creating new user account the password is encrypted via AEC_ENCRYPT function of mysql using a secret key which is only known by me. Let's say user James's account is created with: Username: James Password: somesecretword Now when password is saved in mysql db using AES_ENCRYPT it becomes something like "$-3+%,%kjunbsnd". Now when validation procedure is called it is using the AES_DECRYPT function using same key only i know, which validates the password alongwith username.. My question is how can anyone including the cleaning lady find the actual password without knowing the secret key? Please do not consider this reply as argue, I am only a beginner trying to learn from experience masters like you. Thanks Ahmed

Shujaat Ullah Khan

just to answer you question - ExecuteNonQuery() return the number of rows effected which is zero ! Try using ExecuteScalar() !

Shujaat

Dave Kreskowiak

ahmed_one wrote:

I've found lots of suggestion for encrypt the password is far better then salted and hashed method

Really? I'd be willing to bet every single one of those "suggestions" was written by a security moron. The problem with encrypting a password is that it can be unencrypted and therefor broken. A salted and hashed password cannot be reversed to get the original string, making it FAR more secure.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

Dave Kreskowiak

ahmed_one wrote:

using a secret key which is only known by me.

Bullshit! It's known by the system because the system needs the key to unencrypt the password. Can you GUARANTEE with YOUR LIFE that your system is unhackable and there is zero chance of anyone else getting that key?? If you answer yes, quit your job right now because you're lying to everyone, but even worse, you're lying to yourself.

ahmed_one wrote:

Now when password is saved in mysql db using AES_ENCRYPT it becomes something like "$-3+%,%kjunbsnd". Now when validation procedure is called it is using the AES_DECRYPT function using same key only i know, which validates the password alongwith username..

This is completely INSECURE! The best security systems do NOT compare a typed password against a decrypted password. They compare a hashed password against the hash in the database. No decryption necessary and the passwords are never "out in the open" in decrypted form. YOU ARE HELL BENT ON DOING THIS THE WRONG WAY! DON'T DO IT!

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

Lost User

ahmed_one wrote:

which is only known by me.

That's the problem. You should not have access to other people's passwords, end of story.

ahmed_one wrote:

I am only a beginner trying to learn from experience masters like you.

If you did, you'd be asking for explanation on the salt.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]