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?

Ben Holness
  • 944
  • 2
  • 10
  • 28

5 Answers5

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.

Shoan
  • 1,715
  • 5
  • 17
  • 23
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

jouell
  • 621
  • 1
  • 5
  • 20
  • 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. – S. Imp Apr 11 '18 at 22:35
  • 2
    interesting - thanks - see here https://unix.stackexchange.com/questions/385339/how-does-curl-protect-a-password-from-appearing-in-ps-output as well – jouell Apr 12 '18 at 00:10
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.
l0b0
  • 1,140
  • 1
  • 8
  • 17
  • 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. – Michael - sqlbot Apr 12 '18 at 03:44
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

Timothy Frew
  • 582
  • 3
  • 7
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.

Karl Wilbur
  • 140
  • 5