3

It's not clear to me which characters can occur in MySQL/MariaDB user account names, and which are illegal. I've read section 6.3.1 "User Names and Passwords" in the MySQL documentation. This section includes information on character encodings and the maximum name length, but it doesn't state whether the characters that can be used in an account name are restricted (or perhaps I missed that part?).

So, are there any special charachters in MySQL/MariaDB that cannot be used in user account names? For example, are control characters (like a line break) or wildcard characters (like the asterisk) illegal characters?

  • Outside of pure ASCII it depends on the character set, see: To connect when the user name or password contain non-ASCII characters, the client should call the mysql_options() C API function with the MYSQL_SET_CHARSET_NAME option and appropriate character set name as arguments. This causes authentication to take place using the specified character set. Otherwise, authentication will fail unless the server default character set is the same as the encoding in the authentication defaults. – Patrick Mevzek Jun 8 '18 at 17:56
4

User names are stored on the mysql.user table. On my system (mariadb 10.3), that table is created as a CHAR(80) with the utf8 character set. You can check yours by running:

MariaDB [(none)]> SHOW CREATE TABLE mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

That means, for my version, usernames can be between 0 and 80 characters that are valid utf8 strings but not outside the basic multilingual plane (only 3-byte characters).

So control and wildcard characters are valid, but for example, not emojis:

MariaDB [(none)]> CREATE USER 'a*b';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> CREATE USER 'a
    '> b';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> CREATE USER '💩';
ERROR 1470 (HY000): String '????' is too long for user name (should be no longer than 80)

MariaDB [(none)]> CREATE USER 'Robert\'); DROP TABLE Students;-- ';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT user FROM mysql.user;
+----------------------------------+
| user                             |
+----------------------------------+
| Robert'); DROP TABLE Students;-- |
| a
b                              |
| a*b                              |
| root                             |
| root                             |
| root                             |
| root                             |
+----------------------------------+
7 rows in set (0.000 sec)

Check the type in your system, I guess it could change depending on the version/vendor or be different in the future/past.

  • "Pile of poo" and other Emoji are a part of utf8mb4, but not in utf8. – Rick James Jun 9 '18 at 23:53
1

The MariaDB documentation about usernames is slightly more helpful than that for MySQL, although still not particularly explicit.

It says that (at least) hyphens and wildcard characters require quoting, if used:

The user name and host name may be unquoted, quoted as strings using double quotes (") or single quotes ('), or quoted as identifiers using backticks (`). You must use quotes when using special characters (such as a hyphen) or wildcard characters. If you quote, you must quote the user name and host name separately (for example 'user_name'@'host_name').

  • As a general recommendation, if you can avoid characters that require the username be quoted, do so; the same rule I would apply to table and columns names, etc. – RDFozz Oct 12 '18 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.