Inserting Values into MySQL database from C# application text box

Question

Here is my Insert Method:

public void Insert(string table, string column, string value)
{


    //Insert values into the database.

    //Example: INSERT INTO names (name, age) VALUES('John Smith', '33')
    //Code: MySQLClient.Insert("names", "name, age", "'John Smith, '33'");
    string query = "INSERT INTO " + table + " (" + column + ") VALUES (" + value + ")";


    try
    {
        if (this.Open())
        {
            //Opens a connection, if succefull; run the query and then close the connection.

            MySqlCommand cmd = new MySqlCommand(query, conn);

            cmd.ExecuteNonQuery();
            this.Close();
        }
    }
    catch { }
    return;
}

And then here is my Button click that should actually add the user:

private void createUser_Click_1(object sender, EventArgs e)
{

    //Example: INSERT INTO names (name, age) VALUES('John Smith', '33')
    //Code: MySQLClient.Insert("names", "name, age", "'John Smith, '33'");

    //gets the next userid to assign to the new user
    int counter = sqlClient.Count("UserList") + 1;

    //testing just to make sure values are correct 
    User user1 = new User(counter, textEmail.Text, textPass.Text, textLNAME.Text, textFNAME.Text);
    currentUser.AppendText(user1.ToString());

    //This works to add a user manually to the table
    //This is what I want to automate
    sqlClient.Insert("UserList", "userid, email, password, lastname, firstname", "counter, textEmail.Text, textPass.Text, textLNAME.Text, textFNAME.Text");

    //just to let the user know it worked
    reaction.Text = "Success!";
}

There is probably some method that I have never heard of or used before that I am just missing. I get that the insert method is looking for strings to insert into my database tables. I have a series of text boxes for the user to type in their information, and then I want to send those strings to the database. How do I convert those text box values to strings while in the program? Please excuse me, I am very new at this.

Answer 1

As Jonesy mentioned, you should definitely use parameters to prevent SQL injection.

I think if you're new to C#, it's not a bad practice to learn the basics the "good" way.

You should consider creating a class for all your MySQL methods, and keep proper object disposal in mind.

e.g:

public bool NewUser(string name, int age)
{
    // First let's create the using statement:
    // The using statement will make sure your objects will be disposed after
    // usage. Even if you return a value in the block.
    // It's also syntax sugar for a "try - finally" block.

    using (MySqlConnection cn = new MySqlConnection("your connection string here"))
    {
        // Here we have to create a "try - catch" block, this makes sure your app
        // catches a MySqlException if the connection can't be opened, 
        // or if any other error occurs.

        try
        {
            // Here we already start using parameters in the query to prevent
            // SQL injection.
            string query = "INSERT INTO table (name, age) VALUES (@name, @age);";

            cn.Open();

            // Yet again, we are creating a new object that implements the IDisposable
            // interface. So we create a new using statement.

            using (MySqlCommand cmd = new MySqlCommand(query, cn))
            {
                // Now we can start using the passed values in our parameters:

                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@age", age);

                // Execute the query
                cmd.ExecuteNonQuery();
            }

                // All went well so we return true
                return true;
        }
        catch (MySqlException)
        {
            // Here we got an error so we return false
            return false;
        }
    }
}

Now you can call this method if a user wants to add a new user in your database, and let the user know if all went well or not.

private void createUser_Click_1(object sender, EventArgs e)
{
    yourClass cl = new yourClass();

    // We defined age as an integer in our method, so we first parse (convert)
    // the text value in our textbox to an integer.

    int age;
    int.TryParse(tbAge.Text, out age);
    if (cl.NewUser(tbName.Text, age) == true)
    {
        MessageBox.Show("New user succesfully added !");
    }
    else
    {
        MessageBox.Show("An error occured !");
    }
}

I hope you learned something today here, good luck !

Answer 2

TextBox.Text is a string already

sqlClient.Insert("UserList", 
       "userid, email, password, lastname, firstname", 
       "counter," +textEmail.Text+ "," +textPass.Text+ "," +textLNAME.Text+ "," +textFNAME.Text+ ")";

also you should be concerned about SQL injection attacks, once you get the basics down