C#与MySQL开发求助：根据用户isOver18字段设置变量x的值

Hey there! Since you're new to C# and MySQL, let's walk through fixing your code to properly set x based on the isOver18 field in your users table. Your current code has a few small issues (like incorrect data reading and a critical SQL injection risk), so let's fix those while getting x assigned correctly.

Key Problems in Your Original Code #

• You used cmd.ExecuteNonQuery() for a SELECT statement — this method is designed for insert/update/delete operations, not reading data.
• You never actually pulled the isOver18 value from the query result to assign to x.
• Directly concatenating text box values into your SQL query is a huge SQL injection risk (never do this! Always use parameterized queries).
• There's a variable mix-up: you created datbs as a DataTable but tried to fill dt (which isn't defined in your snippet).

Corrected Code with Explanations #

private void button1_Click(object sender, EventArgs e)
{
    int i = 0;
    int x = 0; // Explicitly declaring here (matches your original definition)

    // Use using statements to auto-dispose resources (prevents connection leaks)
    using (MySqlConnection con = new MySqlConnection("YourConnectionStringHere"))
    {
        con.Open();
        // Parameterized query to avoid SQL injection attacks
        string query = "SELECT isOver18 FROM users WHERE username = @username AND password = @password";
        using (MySqlCommand cmd = new MySqlCommand(query, con))
        {
            // Add safe parameters instead of concatenating text
            cmd.Parameters.AddWithValue("@username", TextBox1.Text);
            cmd.Parameters.AddWithValue("@password", TextBox2.Text);

            // Use a data reader to fetch the query result
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.Read()) // Check if a matching user was found
                {
                    i = 1; // Mark user as existing
                    // Pull the isOver18 value and assign it to x
                    x = reader.GetInt32("isOver18");
                }
                else
                {
                    i = 0; // No matching user found
                }
            }
        }
    }

    // Handle the result messages
    if (i == 0)
    {
        MessageBox.Show("Invalid username or password", "Invalid data", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    else
    {
        // Adjusted logic to match the field name: isOver18=1 means over 18, 0 means under
        if (x == 0)
        {
            MessageBox.Show("The account associated with provided data is under 18.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        else
        {
            MessageBox.Show("Your account is over 18 and everything is as it should be.", "Successful connection", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}

Important Changes Breakdown #

1. Parameterized Queries: Replaced direct text concatenation with @username and @password parameters. This stops attackers from injecting malicious SQL into your query.
2. Using Statements: Wrapped connections, commands, and readers in using blocks to ensure resources are properly released even if an error occurs (no more hanging database connections).
3. Reading isOver18: Used MySqlDataReader to fetch the actual value from the database and assign it to x when a user is found.
4. Logic Alignment: Adjusted the message logic to match the isOver18 field name (1 = over 18, 0 = under). If your field uses the opposite convention, just swap the two message blocks!

Quick Tips for Beginners #

• Never store plain-text passwords: Always hash passwords using libraries like BCrypt before saving them to the database, then compare hashes instead of plain values.
• Secure your connection string: Don't hardcode it in your code — store it in app.config or use environment variables to keep it safe.

内容的提问来源于stack exchange，提问作者bro24s