Update MySQL in C#

Question

I have this code:

public partial class LogintoProfile : Form
{
    public LogintoProfile()
    {
        InitializeComponent();
    }

    private void BoxPawo_TextChanged(object sender, EventArgs e)
    {
        //Boxen viser stjerner i stedet for text
        BoxPawo.PasswordChar = '*';
        //Maxlængde på Password
        BoxPawo.MaxLength = 36;
    }

    private void BoxCopawo_TextChanged(object sender, EventArgs e)
    {
        //Boxen viser stjerner i stedet for text
        BoxPawo.PasswordChar = '*';
        //Maxlængde på Password
        BoxPawo.MaxLength = 36;
    }

    private void Update_Click(object sender, EventArgs e)
    {
        string connStr = "server=localhost;user=root;database=p4_projekt;port=3306;password=Jeppesen95;charset=latin1;";
        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
           MessageBox.Show("Forbinder til databasen");
            conn.Open();
            string sql = "UPDATE p4_projekt.customer_table set First_name='" + BoxFornavn.Text + "',Last_name='" + BoxEfternavn.Text + "',Email='" + textBoxEmail.Text + "',Password=" + BoxPawo.Text + "WHERE Customer_id=@id";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Console.WriteLine(rdr[0] + " -- " + rdr[1]);   // [] kan ikke huske om det er array plads?!
            }

            rdr.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
        Console.ReadLine();
    }

    private void TankOp_Click(object sender, EventArgs e)
    {
        string connStr = "server=localhost;user=root;database=p4_projekt;port=3306;password=**********;charset=latin1;";
        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            MessageBox.Show("Forbinder til databasen");
            conn.Open();
            string sql = "UPDATE p4_projekt.customer_table set Balance'" + Balance.Text + "WHERE Customer_id=@id";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Console.WriteLine(rdr[0] + " -- " + rdr[1]);   // [] kan ikke huske om det er array plads?!
            }

            rdr.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
        Console.ReadLine();
    }

    private void Back_Click(object sender, EventArgs e)
    {
        this.Hide();
        Login.Login name = new Login.Login();
        name.ShowDialog();
    }
}

It's the Update_Click method that causes me problems. I'm working on a Log-in application.

Profile

This page allows the user to edit their information. But to have an update clause, you need a where statement. It would be fine if the user knows its ID, because then I would figure it out. But in my case they don't.

I've read posts where you can use parameters, but I'm not sure about that. Here is the code for the log-in:

public partial class Login : Form
{
    private string connStr;
    private MySqlConnection conn;

    public Login()
    {
        InitializeComponent();
    }

    private void connect_to_DB()
    {
        try
        {
            connStr = "server=localhost;user=root;database=p4_projekt;port=3306;password=**********;charset=latin1;";
            conn = new MySqlConnection(connStr);
            conn.Open();
        }
        catch (MySqlException e)
        {
            throw;
        }
    }

    private bool login_validation(string email, string pass)
    {
        connect_to_DB();

        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select * from customer_table where Email=@email and Password=@pass";
        cmd.Parameters.AddWithValue("@email", email);
        cmd.Parameters.AddWithValue("@pass", pass);
        cmd.Connection = conn;

        MySqlDataReader login = cmd.ExecuteReader();

        if (login.Read())
        {
            conn.Close();
            return true;
        }
        else
        {
            conn.Close();
            return false;
        }
    }

    private void buttonSubmit_Click(object sender, EventArgs e)
    {
        string email = textBoxEmail.Text;
        string pass = textBoxPass.Text;

        if (email == "" || pass == "")
        {
            MessageBox.Show("Tomme felter, udfyld venligst begge felter");
            return;
        }

        bool r = login_validation(email, pass);

        if (r)
        {
            MessageBox.Show("Korrekte oplysninger");
            this.Hide();
            LogintoProfile name = new LogintoProfile();
            name.ShowDialog();  
        }
        else
            MessageBox.Show("Forkerte oplysninger");
    }
}

Answer 1

Your last value (the password) is not between single quotes as required by text fields. So fixing the problem seems to be trivial, but your code is wrong for another reason. You NEVER string concatenate values to form an sql command. This approach leads to problems like the one you have or a worse situations where your code could be easily hacked using Sql Injection.

Please use parameterized queries like here

string sql = @"UPDATE p4_projekt.customer_table 
               set First_name=@firstname, Last_name=@lastname,
                   Email=@email,Password=@password
               WHERE Customer_id=@id";
  MySqlCommand cmd = new MySqlCommand(sql, conn);
  cmd.Parameters.Add("@firstname", MySqlDbType.VarChar).Value = BoxFornavn.Text;
  cmd.Parameters.Add("@lastname", MySqlDbType.VarChar).Value = BoxEfternavn.Text;
  cmd.Parameters.Add("@email", MySqlDbType.VarChar).Value = textBoxEmail.Text;
  cmd.Parameters.Add("@password", MySqlDbType.VarChar).Value = BoxPawo.Text; 
  cmd.Parameters.Add("@id", MySqlDbType.Int32).Value =  ???? // no value for id ???

Also I notice that there is no value given for the last parameter. The @id one, this is essential for the update to work. The ID parameter identify uniquely the record to update. I suppose that it is the same ID that you retrieve when you check if the login is successful. To use this login id you need to store in some kind of global variable available for the lifetime of your application. So you could use a static variable in the login form

public partial class Login : Form
{
    public static int CurrentUserID = -1;

    public Login()
    {
        ......
    }    

private bool login_validation(string email, string pass)
{

    .....
    if (login.Read())
    {
        Login.CurrentUserID = login.GetInt32(login.GetOrdinal("ID"));
        conn.Close();
        return true;
    }
    else
    {
        conn.Close();
        return false;
    }
}

Now in the update form you could use that CurrentUserID to set your last parameter

....
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = Login.CurrentUserID;
....

Answer 2

You don't have an SQL problem^*, you have a business logic problem.

You have an input form with amongst others the following fields:

• First Name
• Last Name
• Email address

Now you want a user to be able to both insert their information when it is unknown to the system, and to update their information when they are known.

So you need to identify one or more of these properties as uniquely identifying a user, which they can provide when they want to update their information. The email address would be a good candidate.

Then you could write the update query as:

UPDATE ...
WHERE Email = @Email

And parameterize your queries.

^*: well you do, you have a massive SQL injection vulnerability. See How can I add user-supplied input to an SQL statement?.

Answer 3

I can't read German(?) but there are a lot of issues in the way this is architected.

1. Don't build your sql statement that way, you're inviting sql injection attacks.

I was going to add a ton more points but too many too list now. I can add more later but by is the connection string here? Why is the password here?

Is this a prototype?

To begin with, why don't you update the question with the requirements so we can understand what it is you're trying to do.

And yes parameters fed to a stored procedure would be better. I can add more info and refactoring suggestions once I understand more first. What unique piece of information does the user have that you can identify them? There must be something for you to use to be able to do a lookup. Again i need more info to help.