CODEWITHSUNDEEP
mysqlbashmariadb
lizlin
lizlin

Reputation: 25

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

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.

Upvotes: 1

Views: 972

Answers (2)

fancyPants
fancyPants

Reputation: 51868

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"

Upvotes: 1

George
George

Reputation: 4257

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;'

Upvotes: 0

Related Questions