mysql connection from bash shell with special characters in password errors out

Question

#!/bin/bash
set -x
MYSQLTEMPPWD=`grep 'temporary password' /var/log/mysqld.log | awk 'NF{ print 
$NF }'`;
MYSQLPWD=`echo "'$MYSQLTEMPPWD'"`;
echo $MYSQLPWD;
CONNINFO="mysql --host=localhost --user=root --password=$MYSQLPWD"
$CONNINFO <<EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Welcome!23';
show databases;
status;
EOF
set +x
exit

>  ./test.sh
++ grep 'temporary password' /var/log/mysqld.log
++ awk 'NF{ print $NF }'
+ MYSQLTEMPPWD='>fjkIyu#K7T?'
++ echo ''\''>fjkIyu#K7T?'\'''
+ MYSQLPWD=''\''>fjkIyu#K7T?'\'''
+ echo ''\''>fjkIyu#K7T?'\'''
'>fjkIyu#K7T?'
+ CONNINFO='mysql --host=localhost --user=root --password='\''>fjkIyu#K7T?'\'''
+ mysql --host=localhost --user=root '--password='\''>fjkIyu#K7T?'\'''
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
+ set +x

Problem i face is for some reasons during the execution step single quote is getting added before password parameter ('--password='''>fjkIyu#K7T?'''').

Tried almost all possible ways based on the blogs, nothing helped sofar.

Anyone please suggest. Thanks...

Kamil Maciorowski · Answer 1 · 2021-01-07T08:06:01.693

In the following part of the feedback enabled with set -x:

'--password='\''>fjkIyu#K7T?'\'''

all but two single-quotes are only to present this string to you. This is how set -x works. This is just an artifact of the way the shell implements the execution trace; it doesn't alter the way the argument is ultimately passed to the command.

mysql gets exactly this: --password='>fjkIyu#K7T?'. There are two literal single-quote characters in this string because you added them. Here:

MYSQLPWD=`echo "'$MYSQLTEMPPWD'"`

First of all, echo here is a mistake. It has its own problems but the weirdest thing is you echo one variable only to capture the result (using backticks) and store in another variable. If you want to create another variable with the same value then it's as simple as:

MYSQLPWD="$MYSQLTEMPPWD"

Or if you want to add single-quotes to the stored string:

MYSQLPWD="'$MYSQLTEMPPWD'"

(see Parameter expansion and quotes within quotes).

I think I know why you thought you needed these single-quotes. This brings us to the other issue. You store code in a variable:

CONNINFO="mysql --host=localhost --user=root --password=$MYSQLPWD"

and then execute the expanded variable:

$CONNINFO …

In general you should double-quote. But if you double-quoted then "$CONNINFO" would expand to a single word which is not a command. So you don't quote to let word splitting work. But then whatever $MYSQLPWD expanded to (and is now stored inside CONNINFO) undergoes word splitting and filename generation. I suspect you enclosed the actual password in single-quotes in hope to prevent this. The problem is quotes that appear from an expanded variable are not special, they are not interpreted by the shell, they are not removed. Not only the single-quotes you added don't prevent word splitting or filename generation at this point; they go to mysql as parts of one of the arguments it gets.

These quotes would matter and disappear if you let the (or a) shell evaluate expanded (and properly double-quoted) $CONNINFO again.

The current shell will evaluate expanded $CONNINFO again if you use eval "$CONNINFO", but don't do this. Another shell will evaluate expanded $CONNINFO again if you use bash -c "$CONNINFO" (or sh -c … or whatever shell), it will be about as bad as eval.

Note a single-quote character in $MYSQLTEMPPWD can pair with the opening single-quote you added. When interpreted again, some parts of the password will be unquoted. This can lead to a syntax error, code injection and other flaws.

Variables are simply not meant to store code. Why don't you keep it simple? Like this:

mysql --host=localhost --user=root --password="$MYSQLTEMPPWD" <<EOF
…

This way you need no MYSQLPWD nor CONNINFO; and what should be quoted is quoted.

OK, maybe the code in question is just an example and you think you really need to store code in a variable in your actual, more complex script. Then please read How can we run a command stored in a variable?

Additional notes:

Consider lowercase variable names.
My answer ignores the grep … | awk … part which probably can be improved, but it's a different story.

mysql connection from bash shell with special characters in password errors out

1 Answers1