When I tried running the following command on MySQL from within Terminal:
mysql -u $user -p$password -e "statement"
The execution works as expected, but it always issues a warning:
Warning: Using a password on the command line interface can be insecure.
However, I have to conduct the statement above using an environment variable ($password
) that stores my password, because I want to run the command iteratively in bash script from within Terminal, and I definitely don't like the idea of waiting a prompt showing up and forcing me to input my password 50 or 100 times in a single script. So here's my question:
Is it feasible to suppress the warning? The command works properly as I stated, but the window becomes pretty messy when I loop over and run the command 50 or 100 times.
Should I obey the warning message and do NOT write my password in my script? If that's the case, then do I have to type in my password every time the prompt forces me to do so?
Running man mysql
doesn't help, saying only
--show-warnings Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.
and mentions nothing about how to turn off the functionality, if I'm not missing something.
I'm on OS X 10.9.1 Mavericks and use MySQL 5.6 from homebrew.
[client]
password=my_password
in ~/.my.cnf
). Surely it has some security implications too, but at least it's not accessible to anyone who can run ps
, and you have control over it with file permissions.
mysql -u root password root -e "statement" > /dev/null
?
pexcept
. It can do terminal insertions and also handle feedback that the command gives. This way you can just skip that verbose output and strip of the actual output you want :)
If your MySQL client/server version is a 5.6.x a way to avoid the WARNING message are using the mysql_config_editor tools:
mysql_config_editor set --login-path=local --host=localhost --user=username --password
Then you can use in your shell script:
mysql --login-path=local -e "statement"
Instead of:
mysql -u username -p pass -e "statement"
I use something like:
mysql --defaults-extra-file=/path/to/config.cnf
or
mysqldump --defaults-extra-file=/path/to/config.cnf
Where config.cnf contains:
[client]
user = "whatever"
password = "whatever"
host = "whatever"
This allows you to have multiple config files - for different servers/roles/databases. Using ~/.my.cnf will only allow you to have one set of configuration (although it may be a useful set of defaults).
If you're on a Debian based distro, and running as root, you could skip the above and just use /etc/mysql/debian.cnf to get in ... :
mysql --defaults-extra-file=/etc/mysql/debian.cnf
--defaults-extra-file
must be first option otherwise mysql complains with mysqldump: unknown variable 'defaults-extra-file
.
MYSQL_PWD
variable....
mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$user" "$pwd") -e "statement"
. Since the printf
is executed by Bash directly it doesn't show up in ps
.
--defaults-file
rather than --defaults-extra-file
, because the latter gave preference to the settings in ~/.my.cnf.
One method that is convenient (but equally insecure) is to use:
MYSQL_PWD=xxxxxxxx mysql -u root -e "statement"
Note that the official docs recommend against it.
See 6.1.2.1 End-User Guidelines for Password Security (Mysql Manual for Version 5.6):
Storing your password in the MYSQL_PWD environment variable This method of specifying your MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. On some systems, if you set MYSQL_PWD, your password is exposed to any other user who runs ps. Even on systems without such a version of ps, it is unwise to assume that there are no other methods by which users can examine process environments.
Access denied for user 'root'@'localhost' (using password: NO)
export MYSQL_PWD=whatever
.
export
, just place it all on one line: MYSQL_PWD=xxxxxxxx mysql -u root -e "statement"
If you wish to use a password in the command line, I've found that this works for filtering out the specific error message:
mysqlcommand 2>&1 | grep -v "Warning: Using a password"
It's basically redirecting standard error to standard output -- and using grep to drop all lines that match "Warning: Using a password".
This way, you can see any other output, including errors. I use this for various shell scripts, etc.
1>&2
)
Here's how I got my bash script for my daily mysqldump database backups to work more securely. This is an expansion of Cristian Porta's great answer.
First use mysql_config_editor (comes with mysql 5.6+) to set up the encrypted password file. Suppose your username is "db_user". Running from the shell prompt: mysql_config_editor set --login-path=local --host=localhost --user=db_user --password It prompts for the password. Once you enter it, the user/pass are saved encrypted in your home/system_username/.mylogin.cnf Of course, change "system_username" to your username on the server. Change your bash script from this: mysqldump -u db_user -pInsecurePassword my_database | gzip > db_backup.tar.gz to this: mysqldump --login-path=local my_database | gzip > db_backup.tar.gz
No more exposed passwords.
Easiest way is
mysql -u root -pMYPASSWORD -e "show databases" 2>/dev/null
2>/dev/null | grep -v "mysql: [Warning] Using a password on the command line interface can be insecure."
to supress only the warning in question
It's very simple. This is work for me.
export MYSQL_PWD=password; mysql --user=username -e "statement"
MYSQL_PWD
is one of the environment variables that are used directly or indirectly by MySQL.
From the docs:
MYSQL_PWD - The default password when connecting to mysqld. Using this is insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”.
MYSQL_PWD
environment variable to specify a MySQL password is deprecated.
ok, solution without temporary files or anything:
mysql --defaults-extra-file=<(echo $'[client]\npassword='"$password") -u $user -e "statement"
it is similar to what others have mentioned, but here you don't need an actual file, this part of the command fakes the file: <(echo ...)
(notice there is no space in the middle of <(
.my.cnf
file in your ~/.
with a password entry
mysql[dump]
comes from the fact that the password will appears in the list of processes running (see ps -ef | grep mysql
). If you have it in a variable like shown in that statement, it's probably okay. However, if you have the password in clear instead of $password
then it's not much better (the echo will be really fast, so as a hacker it's harder to get the info in this case, but still possible).
You can also run the mysql_config_editor in your script to pass in the password when specifying the login path
expect -c "
spawn mysql_config_editor set --login-path=$mySqlUser --host=localhost --user=$mySqlUser --password
expect -nocase \"Enter password:\" {send \"$mySqlPassword\r\"; interact}
"
This starts an expect session which can be used in scripts to interact with prompts
A simple workaroud script. Name this "mysql", and put it in your path before "/usr/bin". Obvious variants for other commands, or if the warning text is different.
#!/bin/sh
(
(
(
(
(
/usr/bin/mysql "$@"
) 1>&9
) 2>&1
) | fgrep -v 'mysql: [Warning] Using a password on the command line interface can be insecure.'
) 1>&2
) 9>&1
You can also just redirect the standard error STDERR output to /dev/null
So just do:
mysql -u $user -p$password -e "statement" 2> /dev/null
shell> mysql_config_editor set --login-path=local
--host=localhost --user=localuser --password
Enter password: enter password "localpass" here
shell> mysql_config_editor set --login-path=remote
--host=remote.example.com --user=remoteuser --password
Enter password: enter password "remotepass" here
To see what mysql_config_editor wrote to the .mylogin.cnf file, use the print command:
shell> mysql_config_editor print --all
[local]
user = localuser
password = *****
host = localhost
[remote]
user = remoteuser
password = *****
host = remote.example.com
The print command displays each login path as a set of lines beginning with a group header indicating the login path name in square brackets, followed by the option values for the login path. Password values are masked and do not appear as clear text.
As shown by the preceding examples, the .mylogin.cnf file can contain multiple login paths. In this way, mysql_config_editor makes it easy to set up multiple “personalities” for connecting to different MySQL servers. Any of these can be selected by name later using the --login-path option when you invoke a client program. For example, to connect to the local server, use this command:
shell> mysql --login-path=local
To connect to the remote server, use this command:
shell> mysql --login-path=remote
From https://gist.github.com/nestoru/4f684f206c399894952d
# Let us consider the following typical mysql backup script:
mysqldump --routines --no-data -h $mysqlHost -P $mysqlPort -u $mysqlUser -p$mysqlPassword $database
# It succeeds but stderr will get:
# Warning: Using a password on the command line interface can be insecure.
# You can fix this with the below hack:
credentialsFile=/mysql-credentials.cnf
echo "[client]" > $credentialsFile
echo "user=$mysqlUser" >> $credentialsFile
echo "password=$mysqlPassword" >> $credentialsFile
echo "host=$mysqlHost" >> $credentialsFile
mysqldump --defaults-extra-file=$credentialsFile --routines --no-data $database
# This should not be IMO an error. It is just a 'considered best practice'
# Read more from http://thinkinginsoftware.blogspot.com/2015/10/solution-for-mysql-warning-using.html
Another alternative is to use sshpass to invoke mysql, e.g.:
sshpass -p topsecret mysql -u root -p username -e 'statement'
Here is a solution for Docker in a script /bin/sh :
docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "[client]" > /root/mysql-credentials.cnf' docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "user=root" >> /root/mysql-credentials.cnf' docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "password=$MYSQL_ROOT_PASSWORD" >> /root/mysql-credentials.cnf' docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec mysqldump --defaults-extra-file=/root/mysql-credentials.cnf --all-databases'
Replace [MYSQL_CONTAINER_NAME] and be sure that the environment variable MYSQL_ROOT_PASSWORD is set in your container.
Hope it will help you like it could help me !
docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "[client] [RETURN HERE] password=pa55" > /root/defaults'
Using the --defaults-file
it already picks up root too.
Define the helper:
remove-warning () {
grep -v 'mysql: [Warning] Using a password on the command line interface can be insecure.'
}
Use it:
mysql -u $user -p$password -e "statement" 2>&1 | remove-warning
Tachaan! Your code is clean and nice to read
(tested with bash)
Personally, I use script wrapper to catch that error. Here is code sample:
#!/bin/bash
#echo $@ | cat >> /home/mysqldump.log 2>/dev/null
ERR_FILE=/tmp/tmp_mdump.err
# Execute dumper
/usr/bin/mysqldump $@ 2>$ERR_FILE
# Determine error and remove tmp file
ERROR=`cat $ERR_FILE`
rm $ERR_FILE
# Handle an error
if [ "" != "$ERROR" ]; then
# Error occured
if [ "Warning: Using a password on the command line interface can be insecure." != "$ERROR" ]; then
echo $ERROR >&2
exit 1
fi
fi
For PowerShell (pwsh
, not bash
), this was quite a rube-goldberg solution... My first attempt was to wrap the calls to mysql
in a try/catch
function, but due to some strange behavior in PowerShell error handling, this wasn't viable.
The solution was to override the $ErrorActionPreference
just long enough to combine and capture STDERR
and STDOUT
and parse for the word ERROR
and re-throw as needed. The reason we couldn't catch and release on "^mysql.*Warning.*password"
is because PowerShell handles and raises the error as one stream, so you must capture it all in order to filter and re-throw. :/
Function CallMySQL() {
# Cache the error action preference
$_temp = $ErrorActionPreference
$ErrorActionPreference = "Continue"
# Capture all output from mysql
$output = (&mysql --user=foo --password=bar 2>&1)
# Restore the error action preference
$ErrorActionPreference = $_temp
if ($output -match "ERROR") {
throw $output
} elseif($output) {
" Swallowing $output"
} else {
" No output"
}
}
Note: PowerShell is available for Unix, so this solution is cross-platform. It can be adapted to bash
with some minor syntax modifications.
Warning: There are dozens of edge-cases where this won't work such as non-english error messages or statements that return the word ERROR
anywhere in the output, but it was enough to swallow the warning for a basic call to mysql
without bombing out the entire script. Hopefully others find this useful.
It would be nice if mysql
simply added an option to suppress this warning.
If you happen to use Rundeck for scheduling your tasks, or any other platform where you ask for a mylogin.cnf
file, I have successfully used the following shell code to provide a new location for the file before proceeding with sql calls:
if test -f "$CUSTOM_MY_LOGINS_FILE_PATH"; then
chmod 600 $CUSTOM_MY_LOGINS_FILE_PATH
export MYSQL_TEST_LOGIN_FILE="$CUSTOM_MY_LOGINS_FILE_PATH"
fi
...
result=$(mysql --login-path=production -NBA -D $schema -e "$query")
Where MYSQL_TEST_LOGIN_FILE
is an environment variable that can be set to a different file path than the default one.
This is especially useful if you are running in a forked process and can't move or copy files to the $HOME
directory.
The problem I had was using the output in a conditional in a bash script.
This is not elegant, but in a docker env this should really not matter. Basically all this does is ignore the output that isn't on the last line. You can do similar with awk, and change to return all but the first line etc.
This only returns the Last line
mysql -u db_user -pInsecurePassword my_database ... | sed -e '$!d'
It won't suppress the error, but it will make sure you can use the output of a query in a bash script.
the best solution is to use alias:
alias [yourapp]-mysql="mysql -u root -psomepassword -P3306 -h 127.0.0.1"
example, put this in your script:
alias drupal-mysql="mysql -u root -psomepassword -P3306 -h 127.0.0.1"
then later in your script to load a database:
drupal-mysql database_name < database_dump.sql
to run a statement:
drupal-mysql -e "EXEC SOMESTATEMENT;"
The answer from @david-g is quite excellent, but at least BSD grep
will interpret the [Warning]
as a regular expression representation of a single character (matching any one of the characters in the word Warning
).
For my scripts, I'm using the same approach, wrapped in a function (and only matching parts of the string, for simplicity):
mysql() {
(
(
(
(
(
/usr/local/bin/mysql "$@"
) 1>&9
) 2>&1
) | grep -v 'Using a password on the command line interface can be insecure.'
) 1>&2
) 9>&1
}
out=$(mysql ...... 2>&1)
rc=$?
The above will call mysql with a set of options, redirect the filtered stderr to stdout, and capture the combined output in $opt
and the exit code in $rc
.
Another solution (from a script, for example):
sed -i'' -e "s/password=.*\$/password=$pass/g" ~/.my.cnf
mysql -h $host -u $user $db_name -e "$sql_cmd"
The -i''
option is here for compatibility with Mac OS X. Standard UNIX OSes can use straight -i
It worked for me- Just added 2> null
after the $(mysql_command)
, and it will suppress the Errors and Warning messages only.
2>/dev/null
. Using 2> null
would just put output into a file called "null" in current directory.
You can execute mySQL and suppress warning and error messages by using /dev/null for example:
# if you run just a SQL-command
mysql -u ${USERNAME} -p${PASSWORD} -h ${HOST} ${DATABASE} -e "${STATEMENT}" &> /dev/null
# Or you can run SQL-script as a file
mysql -u ${USERNAME} -p${PASSWORD} -h ${HOST} ${DATABASE} < ${FILEPATH} &> /dev/null
Where:
${USERNAME} - existing mysql user
${PASSWORD} - password
${HOST} - ip or hostname, for example 'localhost'
${DATABASE} - name of database
${STATEMENT}- SQL command
${FILEPATH} - Path to the SQL-script
enjoy!
The easiest way:
mysql -u root -p YOUR_DATABASE
Enter this and you'll need to type your password in.
Note: Yes, without a semicolon.
Success story sharing
--login-path
has to come before all other arguments. I was tryingmysqldump --tables --login-path=local
and getting the errorunknown variable 'login-path=local'
.