EmergencyEMERGENCY? Get 24/7 Help Now!

Secure passwords being insecure

 | May 27, 2012 |  Posted In: Insight for DBAs, MySQL


If you follow the general advices to create secure password the following ones seem to be secure, right?

  • s11P$||!sh&2
  • pr0&!!ke0
  • 3kj39|!381
  • The answer to the question is, “it depends on how you use them” 🙂

    Notice that these passwords all contain multiple exclamation points and ampersands which are normally special characters for your shell. The people tend to copy and paste them directly to the terminal but that can lead to some non-predictable behavior and therefore cause big problems depending on the character combination.

    Let’s execute the previous examples:

    Login to mysql:

    root@debian:~# mysql -uroot -ps11P$||!sh&2
    mysql -uroot -ps11P$||shutdown -r now&2
    [1] 1758
    -bash: 2: command not found
    root@debian:~# ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

    Broadcast message from root@debian (pts/0) (Sun May 27 13:14:34 2012):

    The system is going down for reboot NOW!

    || is an OR condition and bash tries to execute the last part !sh that points to the “shutdown -r now” command from the history.

    Ok, login to mysql is not very safe. We’re going now to check the replication data:

    root@debian:~# pt-table-checksum u=root,p=pr0&!!&ke0
    pt-table-checksum h=localhost,u=root,p=pr0&shutdown -r now&ke0
    [1] 1736
    [2] 1737
    -bash: ke0: command not found
    Broadcast message from root@debian (pts/0) (Sun May 27 13:46:08 2012):

    The system is going down for reboot NOW!

    In this case we use the combination !! so Bash tries to run the last command, “shutdown -r now“.

    Let’s try to run some backups 🙂

    root@debian:~# innobackupex --password=3kj39|!381
    innobackupex --password=3kj39|/etc/init.d/mysql restart

    Stopping MySQL database server: mysqldinnobackupex: Missing command line argument
    Starting MySQL database server: mysqld.

    In this last example, !381 asks Bash to run the command with number 381 in the history, that is: “/etc/init.d/mysql restart

    These are only a small number of examples. Of course, I’m not telling you to use simple passwords. The lesson that we can learn here is “don’t paste your password on the shell“. First because they’re going to be logged to Bash’s history file and second because some combinations can cause big problems.

    In order to avoid these problems:

  • Don’t run commands from the root account. There are no small mistakes when you make them from root.
  • Use .my.cnf in your home directory to avoid typing a password. Protect the file with permission mode 0600.
  • Use the -p option with mysql and its utilities. This makes the programs prompt you for a password from the tty.
    Miguel Angel Nieto

    Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow.


    • Miguel,

      I wonder what other special characters you would recommend to avoid if you happen to use passwords in Shell sometimes.
      I would not use other special characters which Shell can interpret specially too as even though it might not cause such nasty behaviors it can just take time to modify password to escape those properly.

    • Peter,

      There are lot of characters to avoid because the shell can try to interpret them. Some examples are ” ´ ` ( ) { } ; : \

      They are not dangerous like my previous examples, but they can make you lose lot of time 🙂

    • Have you not heard of quotes?

      mysql -u user -p ‘your strange password goes here >< |$foo' ….

      Storing a password in my.cnf is just as bad. A compromise of your account = compromise of the db.

    • John,

      How about having quotes inside your password ? Having symbols which bash can interpret will bite some way especially if password can be used by inexperienced system users.

    • Of additional concern is when your system(s) have a number of ad-hoc scripts which interrogate the database using these credentials. I’ve seen several instances where scripts are written with incorrect quotes (ie: double quotes instead of single), which in Perl results in @,$,%,\ being interpreted, and in PHP, $,\ and shell $,\,`,;,’ .This can cause some very unexpected results when updating passwords in these scripts (in event of mysql password change).

      If you are sudo’er on the system (most probably are) where the mysql db is, I don’t see any additional risk with having your password in ~/.my.cnf, as an attacker that can login as you could also restart mysql with –skip-grant-tables and add new users or repass existing, or just copy the data files… Obviously this changes if you don’t have sudo access or you access from a host other than the db.

    • For me, putting the password in the command line is the biggest mistake and the worst security protocol to break by a DBA. You don’t only expose your password but also expose the entire data your server contains. Never ever include password after -p parameter in mysql and mysqldump to lessen security risk.

    Leave a Reply