How do I best use a declared variable in bash in mysql command?

Question

I'm writing a bash script for automatic installation and configuration of a system, and I need to be able to set the root password for mysql from a declared variable in the script, but I can't get it to work.

read -p "Password `echo $'\n> '`" mysql_passwd

mysql --defaults-file=/etc/mysql/debian.cnf mysql -Bse 'UPDATE user SET password=PASSWORD("$mysql_passwd") WHERE User="root";flush privileges;'

When running the command from the script without the variable (with just the password directly in it) it works:

mysql --defaults-file=/etc/mysql/debian.cnf mysql -Bse 'UPDATE user SET password=PASSWORD("password") WHERE User="root";flush privileges;'

tried things like:

PASSWORD("$mysql_passwd") 

PASSWORD("${mysql_passwd}")

PASSWORD('$mysql_passwd')

No error message, the root password just doesn't change.

Thank you for reading.

fancyPants · Accepted Answer · 2019-01-24 09:00:40Z

1

You have to put the quotes the other way round. First the double quotes, then the single quotes:

mysql --defaults-file=/etc/mysql/debian.cnf mysql -Bse "UPDATE user SET password=PASSWORD('$mysql_passwd') WHERE User='root';flush privileges;"

When something is escaped with single quotes, variable names are not resolved.

See the difference here:

# a=asdf
#
# echo "'$a'"
'asdf'
# echo '"$a"'
"$a"

answered Jan 24, 2019 at 9:00

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

George · Accepted Answer · 2019-01-24 09:00:08Z

0

You construct the SQL statement in a single-quoted string:

'UPDATE user SET password=PASSWORD("$mysql_passwd") WHERE User="root";flush privileges;'

Bash only replaces variable names ($mysql_passwd) when they are bare or in a double-quoted string. In a single quoted string it is treated as literally "$mysql_passwd". For example:

mysql_passwd="abcdef"
echo $mysql_passwd  # prints abcdef
echo "$mysql_passwd"  # prints abcdef
echo '$mysql_passwd'  # prints $mysql_passwd

So you need to either swap single quotes for double quotes, and escape the existing double quotes:

"UPDATE user SET password=PASSWORD(\"$mysql_passwd\") WHERE User=\"root\";flush privileges;"

Or terminate the single-quoted string, add a double quoted string with $mysql_passwd, and then do the rest of the query in another single-quoted string (bash concatenates strings that are next to each other like this):

'UPDATE user SET password=PASSWORD("'"$mysql_passwd"'") WHERE User="root";flush privileges;'

answered Jan 24, 2019 at 9:00

George

4,27727 silver badges34 bronze badges

1 Comment

Rick James Over a year ago

Even if you get past that, it won't get you past the need to escape certain characters once you get into MySQL. So do not depend on this for arbitrary password strings.

Collectives™ on Stack Overflow

How do I best use a declared variable in bash in mysql command?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related