Calling MySql Function for validating username and password

ahmed_one

Member
Joined
Nov 29, 2012
Messages
23
Programming Experience
Beginner
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.

C#:
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:

C#:
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:

C#:
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:

C#:
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:

C#:
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

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,274
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Nov 29, 2012
Messages
23
Programming Experience
Beginner
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:

C#:
        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

Member
Joined
Nov 29, 2012
Messages
23
Programming Experience
Beginner
Hi,

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

C#:
 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();
        }
 
Top Bottom