1

I have a bash script that runs a bunch of MySQL commands, separated by user input.

Each time a query is run, the password must be re-entered.

I understand it's best to avoid putting the password on the commandline, so I am wary of asking the user for the password and then passing it in on the commandline to each command.

Is there a recommended way to do this without compromising security?

Improve this question

5 Answers 5

Reset to default
1

You can store the credentials in ~/.my.cnf file. Mysql will look up this file by default and use the credentials specified in it, if no other credentials are specified in the command.

Improve this answer
0

I don't have a MySQL server to test with, but you should be able to

  1. ask for the password once,
  2. create a named pipe,
  3. link that to a background mysql process, and
  4. pass all the SQL to the named pipe.
Improve this answer
1
  • This does seem to have some potential, but it's a little messy -- you'd need to handle errors correctly and ensure that the mysql CLI correctly refuses to continue executing statements in runaway fashion after an error. In some contexts, it keeps going, although (iirc) not when reading from a pipe. It seems like an actual script in a language with a MySQL client library seems like the logical way to go, for anything very complex. Commented Apr 12, 2018 at 3:44
0

It's best not to handle password in shell scripts.

I had a similar question:

https://security.stackexchange.com/questions/182927/what-would-be-a-secure-way-to-handle-password-prompts-in-shell

I made the decision use a shell script as a wrapper to an expect script - based on my use case.

It's not perfectly secure (what is?) but:

  1. the password is not revealed via ps.
  2. the password is not hardcoded anywhere.
  3. the password is not long lived in RAM - the process terms in 30s.
  4. the password is for a non-privileged account
  5. the script runs as a non-privileged account
  6. root is locked down to a small group of staff on that box.
  7. the box that execs that command is internal and hardened

Is it 'secure'? It's good enough security for my situation.

Plenty of docs on how to run shell script w/an eye for security. Here's one:

https://developer.apple.com/library/content/documentation/OpenSource/Conceptual/ShellScripting/ShellScriptSecurity/ShellScriptSecurity.html

Also consider: https://security.stackexchange.com/questions/66832/are-shell-scripts-bash-inherently-less-secure-than-other-script-languages-su

Improve this answer
2
  • I wrote a bash script to run several sql scripts on Ubuntu 14 & 16 which prompted the user for their login/pass at first and supplied these as parameters to the mysql commands -- while it was running I checked PS and the password was redacted/masked. I'm not sure what the exact masking mechanism is, but it seemed reasonable to me. Commented Apr 11, 2018 at 22:35
  • 2
    interesting - thanks - see here unix.stackexchange.com/questions/385339/… as well Commented Apr 12, 2018 at 0:10
0

Instead of having MySQL -u root -pPASSWORD have this: `mysql —defaults-file=/etc/mysql/debian.cnf that will use the debian sys maint user

Improve this answer
1
  • I'll bite. What is secure about having a password saved/hard coded in a file? Commented Apr 13, 2018 at 0:39
0

You could store the user input (the password) on a variable and pass it as a param to the MySQL CLI. That's not bad. It doesn't compromise security.

As a user, when you are typing commands, they get stired in your ~/.bash_history in plain text. So, as a user, if you pass the password as a param, it gets stored in a file on the machine. This isn't any worse than storing the password in a config file.

Making the user type the password over and over is annoying from a UX perspective. Saving it to a variable and passing it into the command is much better without compromising security.

It is worth noting that this would expose the password in the process list while each one of the MySQL commands is running.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.