Question 386511 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Use @ literal in SQL command text

I want to change a password for a user in MySQL which requires @ symbol. However, C# SQL command uses that as a parameter holder and because of that the following command fails.

command.CommandText = $"ALTER USER '{user}'@'%' IDENTIFIED BY '{password}'";

How does one escape or force DbCommand to use literal @?

I am aware of SQL injection issues. I am going this in a console app as one time maintenance to do some data migration.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    It turns out that adding single quotes around the username solves the problem. For example: command.CommandText = $"ALTER USER '{usr}'@'%' IDENTIFIED BY '{pwd}'" works resulting in SQL command text of ALTER USER 'user1'@'%' IDENTIFIED BY 'pwd123'. However, username does not have to be quoted if it does not have special characters. For example, the following SQL works fine when executed through MySQL client from command line: ALTER USER user1@'%' IDENTIFIED BY 'pwd123' but the same SQL fails when executed through MySqlCommand resulting in error: Parameter '@'%'' must be defined.


  2. 0

    The MySQL connector tries to parse SQL to implement named parameters, and seemingly offers no way to escape @; accounts and roles are the one syntax affected by it. Luckily, you’re using @'%', and @'%' is the default.

    command.CommandText = $"ALTER USER '{user}' IDENTIFIED BY '{password}'";

    You might even be able to use parameters. If this works, you should – one-off or not, it’s just much cleaner.

    command.CommandText = "ALTER USER @User IDENTIFIED BY @Password";
command.Parameters.AddWithValue("@User", user);
command.Parameters.AddWithValue("@Password", password);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search