Special Characters in MySQL bash statement causing fail

Question

I am attempting to automate the creation of MySQL users -- So I am building a bash cron that will create them on the fly. The problem is, that when I create them, I am using a random password generation which can include characters such as ^ $ % etc etc ..

Using the following fails:

create_database=$(mysql --login-path=local -se "CREATE USER IF NOT EXISTS 'user'@'localhost' IDENTIFIED BY '1234!!ABC^@DEFGH';")

Whereas the following succeeds:

create_database=$(mysql --login-path=local -se "CREATE USER IF NOT EXISTS 'user'@'localhost' IDENTIFIED BY '1234';")

Is it the CLI or bash script that doesn't like the password, or is it MySQL that doesn't like it? Is there a work-around? I'd like to do this in bash vs a scripting language like PHP/PERL

Answer 1

This is a shell issue. In the example you show, the characters !! are being processed by shell history expansion before they are sent to the mysql client. So you are setting the password:

1234cd srcABC^@DEFGH

Assuming cd src was the command you ran before this one. !! is replaced with the previous command in your shell history.

There are lots of special characters in the shell that cause various expansion effects inside double-quoted strings.

You can read man bash:

There are seven kinds of expansion performed: brace expansion, tilde expansion, parameter and variable expansion, command substitution, arithmetic expansion, word splitting, and pathname expansion.

The order of expansions is: brace expansion, tilde expansion, parameter, variable and arithmetic expansion and command substitution (done in a left-to-right fashion), word splitting, and pathname expansion.

On systems that can support it, there is an additional expansion available: process substitution.

To master shell programming, you basically need to study all of these and understand which ones work inside different types of quotes.