Handling Special Characters in MySQL Passwords: Issues with $ and #

Background

The author, a DBA from iKexing, discovered that some MySQL users created via a script could log in when the password was supplied on the command line without quotes, but could not log in when the password was entered manually or quoted, and the root cause was the presence of the special character "$" in the password.

Problem Reproduction

In a test environment a simplified user‑creation script was used:

#
#!/bin/bash
pw="abc$2UY"
mysql --login-path=root -e"create user app@'%' identified by '$pw'"
mysql --login-path=root -e"grant insert,update,delete,select on *.* to app@'%'"
# Test login attempts
[root@node3 ~]# mysql -h127.0.0.1 -uapp -p   # manual entry fails
[root@node3 ~]# mysql -h127.0.0.1 -uapp -p'abc$2UY'   # single quotes fail
[root@node3 ~]# mysql -h127.0.0.1 -uapp -pabc$2UY   # no quotes or double quotes succeed

Problem Analysis

Comparing the authentication_string values of the created user and a manually created test user showed different hashes, indicating that the script stored a different password. The difference is caused by shell quoting: double‑quoted strings allow variable expansion, so $2 is interpreted as an empty variable, resulting in the password abcUY being stored. When the password is entered manually or quoted with single quotes, the literal $2 is sent to MySQL, which does not match the stored value, causing authentication failure. Using double quotes or no quotes lets the shell drop $2 , matching the stored password.

Another issue appears with the login-path configuration when the password contains the character "#". In MySQL 5.7.33 and 8.0.23 (before the fix) the password must be entered with double quotes to avoid a bug.

# Bug demonstration
mysql> create user app2@'%' identified by '123#abc';
# configure login‑path without quotes – login fails
mysql_config_editor set --login-path=app --user=app2 --host=127.0.0.1 -p
Enter password: 123#abc
mysql --login-path=app   # Access denied
# configure with double quotes – login succeeds
mysql_config_editor set --login-path=app --user=app2 --host=127.0.0.1 -p
Enter password: "123#abc"
mysql --login-path=app   # Welcome to MySQL monitor

The bug was fixed in MySQL 5.7.33 and MySQL 8.0.23.

Summary

1. Avoid using special characters such as $ or # in MySQL passwords; if they must be used, enclose the password in single quotes or escape the characters to prevent shell expansion.

2. For MySQL versions prior to 5.7.33/8.0.23, when configuring login-path with a password containing "#", wrap the password in double quotes.