c# - 从 C# 应用程序文本框将值插入 MySQL 数据库

Question

这是我的插入方法:

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;
}

然后这是我的按钮单击，实际上应该添加用户:

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!";
}

可能有一些我以前从未听说过或使用过的方法，我只是错过了。我知道插入方法正在寻找要插入到我的数据库表中的字符串。我有一系列文本框供用户输入其信息，然后我想将这些字符串发送到数据库。在程序中如何将这些文本框值转换为字符串？请原谅，我对此很陌生。

Answer 1

正如 Jonesy 提到的，您绝对应该使用参数来防止SQL 注入(inject)。

我认为，如果您是 C# 新手，以“好”方式学习基础知识并不是一个坏习惯。

您应该考虑为所有 MySQL 方法创建一个类，并牢记正确的对象处置。

例如:

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;
        }
    }
}

现在，如果用户想要在数据库中添加新用户，您可以调用此方法，并让用户知道一切是否顺利。

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 !");
    }
}

我希望你今天在这里学到了一些东西，祝你好运!