Connection must be valid and open asp .net core

Question

This is my IActionResult, I'm working with MySQL, its supposed to make a user register, but for some reason everytime I try to execute it, I get the message "Connection must be valid and open" I've checked the connection string and it's valid, and I also open it, could anyone help me out?

Yes, I know this is prone to SQL Injection, so no need to tell me that.

 public IActionResult Maak()
        {
            string conn = "server=localhost;user id=Job;password=SamplePassword;database=kingdom_of_uluth";
            MySqlConnection con = new MySqlConnection(conn);
            string userName = HttpContext.Request.Query["usernamesignup"];
            string password = HttpContext.Request.Query["passwordsignup"];
            con.ConnectionString = conn;
            con.Open();
            try
            {
                MySqlCommand com = new MySqlCommand($"INSERT INTO account(username, password, lastlogin) VALUES({userName}, {password}, now());");
                com.ExecuteReader();
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }
            con.Close();
            return View("RegisterOK");
        }

I've tried the solution that has been suggested by Henk Mollema, but then I get another error which is

Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll Unknown column 'asdasd' in 'field list'

in which asdasd is the username I put in the username field

Possible duplicate of [Connection must be valid and open error](https://stackoverflow.com/questions/4233185/connection-must-be-valid-and-open-error) — Henk Mollema, Feb 13 '19 at 12:19
@HenkMollema tried it, still didnt work. edited the question thou — Job van Roozendaal, Feb 13 '19 at 12:43
Try adding quotes around the values in the `VALUES (...)` statement. E.g. `'{userName}'`. You wouldn't have this problem though if you'd just use proper command parameters for your query using `com.Parameters.AddWithValue()` rather than concatenating the values in the query. — Henk Mollema, Feb 13 '19 at 13:03

score 1 · Answer 1 · answered Feb 13 '19 at 15:18

There are two problems with your code snippet:

The connection object should be passed to the command object to associate the command with the connection, e.g.:

MySqlCommand com = new MySqlCommand("your query", con);

See Connection must be valid and open error as well.

You're missing quotes around the values in the VALUES (...) statement in the SQL query, for example '{userName}'. You wouldn't have this problem though if you'd just use proper command parameters for your query using com.Parameters.AddWithValue() rather than concatenating the values in the query which enables SQL injection.

Connection must be valid and open asp .net core

1 Answers1