bash script – MySQL commands and variables

Question

I have a bash script who automate a Nextcloud server installation.

To run MySQL commands I use the mysql -e command

user@hostname:~$ mysql -e "CREATE DATABASE ‘NextcloudDataBaseName’"

I would like store db name, user name, password, etc. In variables

How write mysql -e commands with variables ?

user@hostname:~$ mysql -e "CREATE USER ‘$UserName’@'localhost' IDENTIFIED BY ‘$UserPass’"

user@hostname:~$ mysql -e "CREATE USER ${UserName}@'localhost' IDENTIFIED BY ${UserPass}"

The two command above don’t run

Error message with user password (test password) :

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'qwerty1234'

This syntax seems to work :

user@hostname:~$ mysql -e "CREATE USER '${DbUserName}'@'localhost' IDENTIFIED BY '${DbUserPass}'"

Simple quotes around variable names but is this the best syntax, the best practice ?

Thanks :-)

Answer 1

Like this (with here-doc):

mysql<<EOF
CREATE USER '$UserName'@'localhost' IDENTIFIED BY '$UserPass';
EOF

Learn how to quote properly in shell, it's very important :

"Double quote" every literal that contains spaces/metacharacters and every expansion: "$var", "$(command "$var")", "${array[@]}", "a & b". Use 'single quotes' for code or literal $'s: 'Costs $5 US', ssh host 'echo "$HOSTNAME"'. See
http://mywiki.wooledge.org/Quotes
http://mywiki.wooledge.org/Arguments
http://wiki.bash-hackers.org/syntax/words