Usage of MySQL Parameter for creating new user

Question

So I am using a MySQL Server version 8.0.16 and if I try to let dynamically create a new user, i do receive a Error message what says: >>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax. to use near '$password' at line 1<<.

What i can't understand, becouse if i replace the Parameters with the actual value and try it with the shell it works perfectly. I let my code connect as root so and checked if the connection is open what it is. So if I stepped into the code and checked if the parameters are correct everything looked fine. I also added >>'<< at the beginning and end of thext strings that should replace the parameters but it didn't changed the error or what happened.

public bool CreateNewUser(string name, string password, string host)
{
   string query = "CREATE USER $name@$host IDENTIFIED BY $password;";
   List<MySqlParameter> mies = new List<MySqlParameter>
   {
      new MySqlParameter("$name", name),
      new MySqlParameter("$password", password),
      new MySqlParameter("$host", host)
   };

   return InsertIntoQuery(query, mies);
}

//The InsertIntoQuery looks like this

private bool InsertIntoQuery(string sql, List<MySqlParameter> sqlParameters = null)
{
   bool retBl = false;
   try
   {
      using (var SqlConnection = new MySqlConnection(ConnectionStr))
      {
         SqlConnection.Open();
         using (var cmd = new MySqlCommand(sql, SqlConnection))
         {
            if (sqlParameters != null)
               foreach (var item in sqlParameters)
                  cmd.Parameters.AddWithValue(item.ParameterName, item.Value);
            cmd.Prepare();
            var retValNonQuery = cmd.ExecuteNonQuery();

            retBl = (retValNonQuery > 0) ? true : false;
         }
      }
   }
   catch (Exception e)
   {
      MessageBox.Show("Error: " + e.Message);
   }
   return retBl;
}

I would expect it to create a new user but it doesn't.

Rahul · Accepted Answer · 2019-06-14T07:37:55.473

1

No, for CREATE USER command I don't think you can pass command parameter likewise. Rather substitute the value as is like below using string interpolation syntax.

string query = $"CREATE USER '{name}@{host} IDENTIFIED BY {password}";

For an older C# version consider using string.Format()

string query = string.Format("CREATE USER '{0}'@'{1}' IDENTIFIED BY '{2}'",name,host,password);

Per OP's comment: You can't cause it's not a DML operation. If you are worried about SQL Injection probably cause input value is coming from user input then you will have sanitize it someway and moreover if you observe the input are quoted.

Again, I would suggest that this kind of admin operation should go in a DB bootstrap script and not in your application code.

edited Jun 14 '19 at 07:37

answered Jun 14 '19 at 07:29

Rahul

76,197
13
71
125

But it wouldn't be save for SQLInjection like this, right? and is there a reason why i shuldn't be able to use parameters for CREATE USER ? – Archangel Jun 14 '19 at 07:30
1

`is there a reason why i shuldn't be able to use parameters for CREATE USER ?` Because it doesn't work. – mjwills Jun 14 '19 at 07:32
Do note that single quotes in those variables would break your sentence, some replace or scape would be needed – Cleptus Jun 14 '19 at 07:47
@bradbury9 no it shouldn't. See another similar post https://stackoverflow.com/questions/23460954/c-sharp-mysql-create-user – Rahul Jun 14 '19 at 07:49
@bradbury9 makes sence, thought to look at it with replacing the parameters with the values, but if you look at it with the parameters and assume the parameters just have a different meaning it is logical for me. Thanks for the advise – Archangel Jun 14 '19 at 07:51
@Rahul did see that post but i assumed i can make it more save with the parameters and wonderd why it didnt worked. So i did test this but just was sure it shuld work with parameters. (still duplicate? hope not) – Archangel Jun 14 '19 at 07:53

Usage of MySQL Parameter for creating new user

1 Answers1