Using Prepared Statement in C# with Mysql

Question

I tried prepared statement in my program, but not working.

The part commented is the Prepared Statement part. When I change it into normal statement, everything is right.

Can someone tell me what am I missing?

Many thanks.

private void btnLogin_Click(object sender, EventArgs e)
{
    MySqlCommand cmd = MySqlConn.cmd;
    //cmd = new MySqlCommand("SELECT * FROM admin WHERE admin_username='@val1' AND admin_password=PASSWORD('@val2')", MySqlConn.conn);
    //cmd.Prepare();
    //cmd.Parameters.AddWithValue("@val1", tboxUserName.Text);
    //cmd.Parameters.AddWithValue("@val2", tboxPassword.Text);
    cmd = new MySqlCommand("SELECT * FROM admin WHERE admin_username='"+tboxUserName.Text+"' AND admin_password=PASSWORD('"+tboxPassword.Text+"')", MySqlConn.conn);

    MySqlDataReader res = cmd.ExecuteReader();
    if (!res.HasRows) { MessageBox.Show("Error! "); res.Close(); return; }
    else
    {
        //do something
    }
    res.Close();
}

Answer 1

Try removing ' from your query and use Prepare after adding parameters:

cmd = new MySqlCommand("SELECT * FROM admin WHERE admin_username=@val1 AND admin_password=PASSWORD(@val2)", MySqlConn.conn);
cmd.Parameters.AddWithValue("@val1", tboxUserName.Text);
cmd.Parameters.AddWithValue("@val2", tboxPassword.Text);
cmd.Prepare();

Answer 2

Your solution is almost correct as-is. However, being this is a log-in process and thus a security-oriented task, there are a few suggestions I would like to make as well.

First, consider making your button event handler appear as follows:

private void btnLogin_Click(object sender, EventArgs e)
{
    if (Login(tboxUserName.Text, tboxPassword.Text))
    {
        // Log in was successful, do something...
    }
    else
    {
        // Log in was NOT successful, inform the user...
    }
}

This will make maintenance and readability of the application easier. Then declare a function named Login() to perform the heavy lifting:

private bool Login(string username, string password)
{
    try
    {
        MySqlCommand cmd = MySqlConn.cmd;
        cmd = new MySqlCommand(
            "SELECT count(*) FROM admin " + 
            "WHERE admin_username=@username " + 
            "AND admin_password=PASSWORD(@passwd)",
            MySqlConn.conn);
        cmd.Prepare();
        cmd.Parameters.AddWithValue("@username", username);
        cmd.Parameters.AddWithValue("@passwd", password);
        int result = (int)cmd.ExecuteReader();

        // Returns true when username and password match:
        return (result > 0);
    }
    catch (Exception e)
    {
        // Optional: log exception details

        // Deny log in if an error has occurred:
        return false;
    }
}

You will notice a few things here. First, the quotes were removed from your original query string that were preventing the named parameters from working correctly. Additionally, the query returns a count() function result instead of attempting to create a result set containing the administrator username and password. Lastly, the method is encapsulated in a try-catch block , such that in the event an error occurs, the method returns false and the log in is denied. I also broke the query into a concatenated string for easier readability.