Supplying password to wrapped-up MySQL

Question

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.

Answer 1

This may be a windows / SQL Server feature, but could you use a Trusted Connection (i.e. use your OS login/password to access the DB)? There may be an OS X equivalent for MySQL.

Or you may just need to set up your DB to use the OS login and password so that you don't need to keep it in your code.

Anyway, just an idea.

Answer 2

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

Answer 3

Try this:

process.stdin.write(mysql_password + "\n")
process.communicate()
(stdout, stderr) = process.communicate( sql_statement )
process.stdin.close()

return stdout

Call communicate() to force what you just wrote to the buffer to send. Also, it's good to close stdin when you are done.

Answer 4

The only secure method is to use a MySQL cnf file as one of the other posters mentions. You can also pass a MYSQL_PWD env variable, but that is insecure as well: http://dev.mysql.com/doc/refman/5.0/en/password-security.html

Alternatively, you can communicate with the database using a Unix socket file and with a little bit of tweaking you can control permissions at the user id level.

Even better, you can use the free BitNami stack DjangoStack that has Python and MySQLDB precompiled for OS X (And Windows and Linux) http://bitnami.org/stacks