Calling MySql Function 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 trying to use it in C# from Login Form when user enter Username and Password and click login button with following code:

private void btnCheck_Click(object sender, EventArgs e)
{
    Globals.userName = txtUser.Text.ToString();
    Globals.userPass = txtPass.Text.ToString();

    if (fUser.CheckUser(Globals.userName, Globals.userPass, Globals.sKey) == 0)
    {
        MessageBox.Show("Invalid Username or Password.");
    }
    else
    {
        MessageBox.Show("Login Successfull");
    }
}

It always return 0, means failed login. I've checked the Mysql function in MySql GUI and it works fine:

SELECT fLogin_Check("AHMED","AHMED1981","AHMEDFINANCEICMAP1122") FROM userMaster

Which successfully return 1, however it fails when calling in C#. I've also tried to access Parameter which I've comment out after failure...What am I doing wrong?

Please guide..


Ahmed

 

[jmcilhinney]

You have specified the CommandType as StoredProcedure but you are trying to execute a function. I'm not sure that you can execute a function directly. Have you tried using inline SQL that calls the function, just as you did in MySQL GUI?

 

[ahmed_one]

You have specified the CommandType as StoredProcedure but you are trying to execute a function. I'm not sure that you can execute a function directly. Have you tried using inline SQL that calls the function, just as you did in MySQL GUI?

Yes I've also tried the following:

        sInsProcName = "Select fLogin_Check(mUserName, mUserPass, mUserKey)";
        insertcommand = new MySqlCommand(sInsProcName, oCn);
        insertcommand.CommandType = CommandType.Text;
        insertcommand.Parameters.Add(new MySqlParameter("mRes", MySqlDbType.Int32, 0));
        insertcommand.Parameters["mRes"].Direction = ParameterDirection.ReturnValue;
       insertcommand.Parameters.Add(new MySqlParameter("mUserName", MySqlDbType.VarChar, 50, Globals.userName));
                insertcommand.Parameters.Add(new MySqlParameter("mUserPass", MySqlDbType.VarChar, 40, Globals.userPass));
                insertcommand.Parameters.Add(new MySqlParameter("mUserKey", MySqlDbType.VarChar, 40, Globals.sKey));
                var res = insertcommand.ExecuteReader();
                //res = int.Parse(insertcommand.Parameters["mRes"].Value.ToString());

                return (int.Parse(res.ToString()));
            
                oCn.Close();

Now the error says:

"Unknown column 'mUserName' in 'field list' "

Which is something strange...Because mUserName is exist in fLoign_Check function in MySql..

 

[ahmed_one]

Hi,

Atlast...I've make it working... Following is the code if anyone is looking for it:

 public int CheckUser()
        {
            oCn = da.GetConnection();
            

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

                
                sInsProcName = "fLogin_Check";
                insertcommand = new MySqlCommand(sInsProcName, oCn);
                insertcommand.CommandType = CommandType.StoredProcedure;
                
    insertcommand.Parameters.Add(new MySqlParameter("mUserName", MySqlDbType.VarChar)).Value = Globals.userName;
    insertcommand.Parameters.Add(new MySqlParameter("mUserPass", MySqlDbType.VarChar)).Value = Globals.userPass.ToUpper();
  insertcommand.Parameters.Add(new MySqlParameter("mUserKey", MySqlDbType.VarChar)).Value = Globals.sKey;
                insertcommand.Parameters.Add(new MySqlParameter("mRes", MySqlDbType.Int32, 0));
                insertcommand.Parameters["mRes"].Direction = ParameterDirection.ReturnValue;

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

                return (res);
            
                oCn.Close();
        }