Using the using() to implement Mysql connection

Question

Is the way I used the using statement in the following code correct?

else if(updated_password == confirm_password)
{
   using (old_connection = new MySqlConnection("server=localhost;user id=" + old_user + ";database=DB;password=" + old_pass))
   {
       MySqlCommand old_cmd = new MySqlCommand("ALTER USER'" + old_user + "'@'localhost' IDENTIFIED BY'" + confirm_password + "'", old_connection);

       old_cmd.ExecuteNonQuery();
       //old_connection.Close();
       MessageBox.Show("Password changed successfully.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
       //old_connection.Open();
    }
}

Tom Dee · Accepted Answer · 2020-05-17 14:21:10Z

1

You pretty much are using 'using' correctly however a few small issues with the code.

Ideally you can create a new connection each time instead of using the same variable every time. This will help with scoping issues and less likely to make mistakes such as using an old instance of it. Like:

 using (var connection = new MySqlConnection("server=localhost;user id=" + old_user + ";database=DB;password=" + old_pass))

The connection still needs to be opened in the 'using' statement. 'using' only handles is disposal of the object. So first line inside the 'using' should be:

connection.Open();

Parameters should also be used when creating the 'MySqlCommand' to protect against SQL injection attacks.

answered May 17, 2020 at 14:21

Tom Dee

2,6844 gold badges20 silver badges27 bronze badges

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

10 Comments

JoeyB Over a year ago

"Parameters should also be used when creating the 'MySqlCommand' to protect against SQL injection attacks." So basically MySqlCommand(query,old_connection) where query is ""ALTER USER'" + old_user + "'@'localhost' IDENTIFIED BY'" + confirm_password + "'""

JoeyB Over a year ago

I declared the MySqlConnection old_connection; at the beginning of the code. So you say I should remove the declaration and just use var old_connection every time I want to connect to the database? and by using the using() statment is there ever a need to say old_connection.Open(), the part that is commented out?

Tom Dee Over a year ago

Yes you should be creating a new connection object each time and call connection.Open(). Using will close the connection so if there are connections then you'll never open it back up again. Parameters wise the top answer here explains it pretty well: stackoverflow.com/questions/13580993/…

JoeyB Over a year ago

I am still a bit confused. So you say if I want to make another connection I should NOT use var connection (or var old_connection) but rather use another one like var conn(...) and immediately use conn.Open()?

Tom Dee Over a year ago

It isn't necessary as the application will still work but could be a security flaw. I'd say put it in another question as it's not really relevant to "using" statements

|

Ori Cohen · Accepted Answer · 2020-05-17 14:15:10Z

1

using(var old_connection = ...)

Would be better. There is no need to save the connection outside of the scope of the using statement

answered May 17, 2020 at 14:15

Ori Cohen

1859 bronze badges

1 Comment

JoeyB Over a year ago

I declared the MySqlConnection old_connection; at the beginning of the code. So you say I should remove the declaration and just use var old_connection every time I want to connect to the database? and by using the using() statment is there ever a need to say old_connection.Open(), the part that is commented out?

Collectives™ on Stack Overflow

Using the using() to implement Mysql connection

2 Answers 2

10 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related