Where is the MySQL password kept / How do I configure applications

MySQL access on suso.org is setup to provide you with the most options and security for your account. This document will guide you through how you can access MySQL from the client program as well as from the programs that setup and write for your account.

The first concept I'd like to explain about MySQL is that the MySQL server uses it's own set of passwords and access system. Therefore, the password that you use to access your MySQL databases may and will be different than the password that you use to login to your shell account.

Most likely you are going to setup a database so that programs that you write or setup can view and manipulate your data in an unattended data. Therefore there should be a way that your programs can login to the MySQL system. Sometimes people simply put the clear text password used to login to their MySQL account directly into their scripts. This however is a bad idea for two reasons. First of all it might be possible that your script source code could be seen by another user on the system or even worse, by a random host on the internet. The second bad reason for keeping the password in your scripts is that it is scattered among several unknown scripts and if you ever need to change your password, it can be difficult to track down and change those old password strings.

At suso.org, we've developed a new way to keep your database password in one secure location that only allows you and your scripts to view it. That location is

/home/username/private/mysqlinfo

The format of the file is the following:

mysqlusername:password:mysqlhostname

Your mysqlinfo file will look like this:

username:XXXXXXXX:mysql.suso.org

The password has been masked out with Xs for safety.

This mysqlinfo file has been setup in a way that only you and the webserver can view the file. No other user or program on the system will be able to view this file.

We have been using this method for over 5 years now and it has worked very successfully and is starting to catch on with other web hosting providers as a safer way to store passwords. Any web hosting provider who wishes to use this method should read this file, and ask us if they have any questions.

Setting up your applications to use the mysqlinfo file

This file should be used by your scripts to get at the clear text password for your database instead of putting the password in your scripts (which is dangerous). I've taken the proper precautions to make sure that only you can get at that file.

You can use the following code at the top of your web applications to read the clear text password and connect to your databases.

For PHP:

<?php

unset($userpass);

unset($dbusername);
unset($dbpassword);
unset($dbhostname);

$filename = '/home/username/private/mysqlinfo';
$fp = fopen("$filename", 'r');
$contents = fread($fp, filesize($filename));
$contents = chop($contents);
fclose($fp);
$array = explode(":", $contents);

$dbusername = $array[0];
$dbpassword = $array[1];
$dbhostname = $array[2];

?>

For Perl:

#!/usr/bin/perl

undef $userpass;

undef $dbusername;
undef $dbpassword;
undef $dbhostname;

open(MYSQLINFO, '/home/username/private/mysqlinfo');
$userpass = <MYSQLINFO>;
chomp($userpass);
close(MYSQLINFO);

($dbusername, $dbpassword, $dbhostname) = split(":", $userpass);

For Python: (Thanks to Rob Bryant)

#!/usr/bin/python

import MySQLdb


# open and parse

datafile = open('/home/username/private/mysqlinfo')
s = datafile.read()
s = s.strip()
username, password, hostname = s.split(':')
datafile.close()

# connect

conn = MySQLdb.connect(host=hostname, user=username,
                      passwd=password, db='database_name')

cursor = conn.cursor()


# ...


conn.close()

Now your programs can simply use the above code or include it from a header file and simply login to the database automatically using the variables dbusername, dbpassword and dbhostname.