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 — Vincent Schmandt
– Vincent Schmandt, Commented Apr 27, 2022 at 8:53

Luca Biccì · Accepted Answer · 2022-04-27 09:20:10Z

2

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.

answered Apr 27, 2022 at 9:20

Luca Biccì

211 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2022-04-27 10:15:08Z

0

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

answered Apr 27, 2022 at 10:15

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

C# Parameterized MySQL query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related