C# Parameterized MySQL query

Question

How do I change below code to Visual Studio analyze note below? I want to change below my code to parameterized MySQL query?

CA2100 Review SQL queries for security vulnerabilities The query string passed to 'MySqlDataAdapter.MySqlDataAdapter(string, MySqlConnection)' in 'LogIn.button1_Click(object, EventArgs)' could contain the following variables 'this.userIDText.Text', 'this.password.Text'. If any of these variables could come from user input, consider using a stored procedure or a parameterized SQL query instead of building the query with string concatenations.

MySqlConnection cs = new MySqlConnection(@"Persist Security Info=True;Password=XXX;User ID=XXX;Initial Catalog=hypolk_safranbp_tgl;Data Source=XXX;Character Set=utf8");

cs.Open();
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter(@"Select UserName,Password from Staff where UserName='" + userIDText.Text + "' AND Password='" + password.Text + "'", cs);
da.Fill(ds);

int count = ds.Tables[0].Rows.Count;

Does this answer your question? [c# Using Parameters.AddWithValue in SqlDataAdapter](https://stackoverflow.com/questions/13276602/c-sharp-using-parameters-addwithvalue-in-sqldataadapter) — Vincent, Apr 27 '22 at 08:53
Or [Parameterized Query for MySQL with C#](https://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp) ? — JonasH, Apr 27 '22 at 09:11

score 2 · Answer 1 · answered Apr 27 '22 at 09:20

Your query must be something like this:

Select UserName,Password from Staff where UserName=@username AND Password=@password

and then add parameters to command:

command.Parameters.AddWithValue("username", UserIdText.Text);
...

Much better for security.

score 0 · Answer 2 · answered Apr 27 '22 at 10:15

As well as adding parameters, you should use using blocks to correctly dispose the ADO.Net objects:

DataTable dt = new DataTable();

const string query = @"
Select
  UserName,
  Password
from Staff
where UserName = @username
  AND Password = @password;
";

using (var cs = new MySqlConnection(@"Persist Security Info=True;Password=XXX;User ID=XXX;Initial Catalog=hypolk_safranbp_tgl;Data Source=XXX;Character Set=utf8")
using (var cmd = new MySqlCommand(query, cs))
{
    cs.Open();
    using (var rdr = cmd.ExecuteReader())
        dt.Load(rdr);
}

int count = dt.Rows.Count;

Although if you want to just get the count then I suggest you use SELECT COUNT(*) and (int)cmd.ExecuteScalar().

C# Parameterized MySQL query

2 Answers2