Mysql command line password with special characters is not working

Question

I've the following bash script to upgrade my database schema. Script reads hostname and database password from command line.

The problem is here that if the password is alphanumeric e.g r00t then script works. But if password contains special characters e.g pa**w0rd, then script does not work and directly exits. Please help me with this. Thanks.

#!/bin/bash  echo "Enter hostname." read -p "Hostname [localhost]: " DB_HOST DB_HOST=${DB_HOST:-localhost}  echo "Enter MySQL root password" DB_PASS= while [[ $DB_PASS = "" ]]; do    read -sp "Password: " DB_PASS done  MYSQL="mysql --force --connect-timeout=90 --host=$DB_HOST -u root --password=${DB_PASS}"  # Apply schema updates. My DBName is "mydb" # Upgrade schema file is stored in "mysql" folder  $MYSQL mydb -e exit > /dev/null 2>&1 && $MYSQL mydb < "../mysql/upgrade_schema_v.2.1.sql" 

Answer 1

Logging into mysql using bash

For ubuntu or linux shell try to use command

mysql -u username -p'p@ssw()rD' 

for remote host login use

mysql -h hostname -u user -p'password' 

Answer 2

This is occurring because you are using shell GLOB (wildcard) characters in the password, and in Bash (or on Linux generally) wildcards are expanded by the shell.

The safest and most reliable solution is to not use shell wildcard characters or other characters interpreted by the shell in the password. You should also avoid spaces. There are plenty of other characters.

Here are the ones you should avoid:

" ' $ , [ ] * ? { } ~ # % \ < > | ^ ;

Here are the ones it is usually safe to use:

: @ . , / + - ! =

To ensure the password is still secure, make it longer. As an example:

K@3amvv7l1wz1192sjqhym

This meets old-fashioned password complexity rules, because upper, lower, numbers and special characters are in the first four, the remainder is randomly generated but avoids any problematic characters.

However if you must use them, you can quote the password parameter with single quotes - though you will still run in to trouble if the password contains single quotes!