[FIXED] How to fix a double/single quote inside the quotes in a MySQL 8 with BASH with EOF?

Issue

How to fix this error:

 # cat <<EOF | mysql -uroot -p${MYSQL_PASSWD}
 > create database zabbix character set utf8 collate utf8_bin; create user zabbix@localhost identified by '${ZABBIX_PASSWD}';
 > grant all privileges on zabbix.* to '\'zabbix'\'@'\'localhost'\' identified by '${ZABBIX_PASSWD}';
 > flush privileges;
 > exit
 > EOF
 mysql: [Warning] Using a password on the command line interface can be insecure.
 ERROR at line 2: Unknown command '\''.

Or with double quotes:

 # cat <<EOF | mysql -uroot -p${MYSQL_PASSWD}
 > create database zabbix character set utf8 collate utf8_bin; create user zabbix@localhost identified by '${ZABBIX_PASSWD}';
 > grant all privileges on zabbix.* to "\""zabbix\""@"\"localhost"\"" identified by '${ZABBIX_PASSWD}';
 > flush privileges;
 > exit
 > EOF
 mysql: [Warning] Using a password on the command line interface can be insecure.
 ERROR at line 2: Unknown command '\"'.

Without double/single quotes:

# cat <<EOF | mysql -uroot -p${MYSQL_PASSWD}
> create database zabbix character set utf8 collate utf8_bin; create user zabbix@localhost identified by '${ZABBIX_PASSWD}';
> grant all privileges on zabbix.* to zabbix@localhost identified by '${ZABBIX_PASSWD}';
> flush privileges;
> exit
> EOF
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by ''' at line 1

Or with just double/single quotes:

# cat <<EOF | mysql -uroot -p${MYSQL_PASSWD}
> create database zabbix character set utf8 collate utf8_bin; create user zabbix@localhost identified by '${ZABBIX_PASSWD}';
> grant all privileges on zabbix.* to "zabbix"@"localhost" identified by '${ZABBIX_PASSWD}';
> flush privileges;
> exit
> EOF
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by ''' at line 1

The error is the same in a EOF shell file?

Solution

You don't need backslashes here. You are confusing the command line for your heredoc and vice versa. You are also confusing mysql and the command line. Each of these (command line, heredoc, and mysql) have different rules regarding single and double quotes.

1. Mysql needs your string literals enclosed in single quotes (but can take double quotes too, but that's not standardish).
2. bash obviously has its rules regarding single and double quotes, but they don't apply here as this is a heredoc
3. Your heredoc don't care. Things inside your heredoc are treated as if they are a file. Single quotes, double quotes, whatever. The cool thing is bash will swap out variables still so it's like a SUPERFILE, but it's just a heredoc.

Something like the following should work fine:

cat <<EOF | mysql -uroot -p${MYSQL_PASSWD}
create database zabbix character set utf8 collate utf8_bin; create user 'zabbix'@'localhost' identified by '${ZABBIX_PASSWD}';
grant all privileges on zabbix.* to 'zabbix'@'localhost' identified by '${ZABBIX_PASSWD}';
flush privileges;
exit
EOF

Your variables will be replaced by bash even though there are single quotes around them because the heredoc don't care. This all then gets passed into mysql and mysql is happy because your string literals are properly quoted.

Lastly, if you are really wedded to those double quotes, you can use those instead inside your heredoc and it won't make a difference. bash will ignore them and mysql will allow them through.

I lied, one other last thing. You can use <<- when declaring your heredoc so you can precede the lines in your heredoc with whitespace which makes it easier to read if you are doing this in a script. You can also name your heredoc whatever you want as long as it ends with the same word (both of these for script clarity/readability). You also don't need cat here as mysql can consume directly from a file, and a heredoc is in nearly every way that matters, a file.

mysql -uroot -p${MYSQL_PASSWD} <<-MYSQLCOMMANDS
    create database zabbix character set utf8 collate utf8_bin; create user 'zabbix'@'localhost' identified by '${ZABBIX_PASSWD}';
    grant all privileges on zabbix.* to 'zabbix'@'localhost' identified by '${ZABBIX_PASSWD}';
    flush privileges;
    exit
MYSQLCOMMANDS

Answered By - JNevill

Answer Checked By - Willingham (PHPFixing Volunteer)