Python – Supplying password to wrapped-up MySQL

mysqlpythonsubprocess

Greetings.

I have written a little python script that calls MySQL in a subprocess. [Yes, I know that the right approach is to use MySQLdb, but compiling it under OS X Leopard is a pain, and likely more painful if I wanted to use the script on computers of different architectures.] The subprocess technique works, provided that I supply the password in the command that starts the process; however, that means that other users on the machine could see the password.

The original code I wrote can be seen here.

This variant below is very similar, although I will omit the test routine to keep it shorter:

#!/usr/bin/env python

from subprocess import Popen, PIPE

# Set the command you need to connect to your database
mysql_cmd_line = "/Applications/MAMP/Library/bin/mysql -u root -p"
mysql_password = "root"

def RunSqlCommand(sql_statement, database=None):

    """Pass in the SQL statement that you would like executed.
    Optionally, specify a database to operate on.  Returns the result."""

    command_list = mysql_cmd_line.split()
    if database:
        command_list.append(database)

    # Run mysql in a subprocess
    process = Popen(command_list, stdin=PIPE, stdout=PIPE,
                    stderr=PIPE, close_fds=True)

    #print "Asking for output"
    #needs_pw = process.stdout.readline()
    #print "Got: " + needs_pw

    # pass it in the password
    process.stdin.write(mysql_password + "\n")

    # pass it our commands, and get the results
    #(stdout, stderr) = process.communicate( mysql_password + "\n" + sql_statement)
    (stdout, stderr) = process.communicate( sql_statement )

    return stdout

I am suspicious that the MySQL password prompt is not actually on stdout (or stderr), although I don't know how that could be or if it means I could trap it.

I did try reading output first, before supplying a password, but it didn't work. I also tried passing the password

Again, if I supply the password on the command line (and thus have no code between the "Popen" and "communicate" functions) my wrapped function works.


Two new thoughts, months laster:

  1. Using pexpect would let me supply a password. It simulates a tty and gets all output, even that which bypasses stdout and stderr.

  2. There is a project called MySQL Connector/Python, in early alpha, that will allow provide a pure python library for accessing MySQL, without requiring you to compile any C-code.

Best Solution

You could simply build a my.cnf file and point to that on the mysql command. Obviously you'll want to protect that file with permissions/acls. But it shouldn't be really an more/less secure then having the password in your python script, or the config for your python script.

So you would do something like

mysql_cmd_line = "/Applications/MAMP/Library/bin/mysql --defaults-file=credentials.cnf"

and your config would look about like this

[client]
host     = localhost
user     = root
password = password
socket   = /var/run/mysqld/mysqld.sock