C# Parameterized MySQL query-CodePudding

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;

CodePudding user response：

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.

CodePudding user response：

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().