2.11.1 Problem
You need to obtain connection parameters for a script so that it can connect to a MySQL server.
2.11.2 Solution
There are lots of ways to do this. Take your pick.
2.11.3 Discussion
Any program that connects to MySQL needs to specify connection parameters such as the username, password, and hostname. The recipes shown so far have put connection parameters directly into the code that attempts to establish the connection, but that is not the only way for your programs to obtain the parameters. This section briefly surveys some methods you can use, then shows how to implement two of them.
The parameters can be given either in the main source file or in a library file that is used by the program. This is convenient because users need not enter the values themselves. The flip side, of course, is that it's not very flexible. To change the parameters, you must modify your program.
In a command-line environment, you can ask the user a series of questions. In a web or GUI environment, this might be done by presenting a form or dialog. Either way, this gets to be tedious for people who use the program frequently, due to the need to enter the parameters each time.
This method can be used either for commands that you run interactively or that are run from within a script. Like the method of obtaining parameters interactively, this requires you to supply parameters each time you use MySQL, and can be similarly tiresome. (A factor that significantly mitigates this burden is that many shells allow you to recall commands from your history list for reexecution.)
The most common way of using this method is to set the appropriate environment variables in one of your shell's startup files (such as .cshrc for csh; .tcshrc for tcsh; or .profile for sh, bash, and ksh). Programs that you run during your login session then can get parameter values by examining their environment.
With this method, you store information such as the username and password in a file that programs can read before connecting to the MySQL server. Reading parameters from a file that's separate from your program gives you the benefit of not having to enter them each time you use the program, while allowing you to avoid hardwiring the values into the program itself. This is especially convenient for interactive programs, because then you need not enter parameters each time you run the program. Also, storing the values in a file allows you to centralize parameters for use by multiple programs, and you can use the file access mode for security purposes. For example, you can keep other users from reading the file by setting its mode to allow access only to yourself.
The MySQL client library itself supports an option file mechanism, although not all APIs provide access to it. For those that don't, workarounds may exist. (As an example, Java supports the use of properties files and supplies utility routines for reading them.)
It's often useful to combine some of the preceding methods, to afford users the option of providing parameters different ways. For example, MySQL clients such as mysql and mysqladmin look for option files in several locations and read any that are present. Then they check the command-line arguments for further parameters. This allows users to specify connection parameters in an option file or on the command line.
These methods of obtaining connection parameters do involve some security issues. Briefly summarized, these issues are:
The rest of this section shows how to process command-line arguments to get connection parameters, and how to read parameters from option files.
2.11.4 Getting Parameters from the Command Line
The usual MySQL convention for command-line arguments (that is, the convention followed by standard MySQL clients such as mysql) is to allow parameters to be specified using either a short option or a long option. For example, the username cbuser can be specified either as -u cbuser (or -ucbuser) or --user=cbuser. In addition, for the options that specify the password (-p or --password), the password value may be omitted after the option name to indicate that the program should prompt for the password interactively.
The next set of example programs shows how to process command arguments to obtain the hostname, username, and password. The standard flags for these are -h or --host, -u or --user, and -p or --password. You can write your own code to iterate through the argument list, but in general, it's much easier to use existing option-processing modules written for that purpose. The programs presented here are implemented using a getopt( )-style function for each API, with the exception of PHP. Insofar as possible, the examples mimic the behavior of the standard MySQL clients. (No example program is provided for PHP, because few PHP scripts are written for use from the command line.)
2.11.4.1 Perl
Perl passes command-line arguments to scripts in the @ARGV array, which can be processed using the GetOptions( ) function of the Getopt::Long module. The following program shows how to parse the command arguments for connection parameters. If a password option is specified with no following argument, the script prompts the user for the password value.
#! /usr/bin/perl -w # cmdline.pl - demonstrate command-line option parsing in Perl use strict; use DBI; use Getopt::Long; $Getopt::Long::ignorecase = 0; # options are case sensitive $Getopt::Long::bundling = 1; # allow short options to be bundled # connection parameters - all missing (undef) by default my ($host_name, $password, $user_name); GetOptions ( # =s means a string argument is required after the option # :s means a string argument is optional after the option "host|h=s" => $host_name, "password|p:s" => $password, "user|u=s" => $user_name ) or exit (1); # no error message needed; GetOptions( ) prints its own # solicit password if option specified without option value if (defined ($password) && $password eq "") { # turn off echoing but don't interfere with STDIN open (TTY, "/dev/tty") or die "Cannot open terminal "; system ("stty -echo < /dev/tty"); print STDERR "Enter password: "; chomp ($password = ); system ("stty echo < /dev/tty"); close (TTY); print STDERR " "; } # construct data source name my $dsn = "DBI:mysql:database=cookbook"; $dsn .= ";host=$host_name" if defined ($host_name); # connect to server my $dbh = DBI->connect ($dsn, $user_name, $password, {PrintError => 0, RaiseError => 1}); print "Connected "; $dbh->disconnect ( ); print "Disconnected "; exit (0);
The arguments to GetOptions( ) are pairs of option specifiers and references to the script variables into which option values should be placed. An option specifier lists both the long and short forms of the option (without leading dashes), followed by =s if the option requires a following argument or :s if it may be followed by an argument. For example, "host|h=s" allows both --host and -h and indicates that a string argument is required following the option. You need not pass the @ARGV array because GetOptions( ) uses it implicitly. When GetOptions( ) returns, @ARGV contains any remaining arguments following the last option.
The Getopt::Long module's $bundling variable affects the interpretation of arguments that begin with a single dash, such as -u. Normally, we'd like to accept both -u cbuser and -ucbuser as the same thing, because that's how the standard MySQL clients act. However, if $bundling is zero (the default value), GetOptions( ) interprets -ucbuser as a single option named "ucbuser". By setting $bundling to nonzero, GetOptions( ) understands both -u cbuser and -ucbuser the same way. This happens because it interprets an option beginning with a single dash character by character, on the basis that several single-character options may be bundled together. For example, when it sees -ucbuser, it looks at the u, then checks whether or not the option takes a following argument. If not, the next character is interpreted as another option letter. Otherwise, the rest of the string is taken as the option's value. For -ucbuser, u does take an argument, so GetOptions( ) interprets cbuser as the option value.
One problem with GetOptions( ) is that it doesn't support -p without a password the same way as the standard MySQL client programs. If -p is followed by another option, GetOptions( ) correctly determines that there is no password value present. But if -p is followed by a non-option argument, it misinterprets that argument as the password. The result is that these two invocations of cmdline.pl are not quite equivalent:
% cmdline.pl -h localhost -p -u cbuser xyz Enter password: % cmdline.pl -h localhost -u cbuser -p xyz DBI->connect(database=cookbook;host=localhost) failed: Access denied for user: 'cbuser@localhost' (Using password: YES) at ./cmdline.pl line 40
For the first command, GetOptions( ) determines that no password is present and the script prompts for one. In the second command, GetOptions( ) has taken xyz as the password value.
A second problem with cmdline.pl is that the password-prompting code is Unix specific and doesn't work under Windows. You could try using Term::ReadKey, which is a standard Perl module, but it doesn't work under Windows, either. (If you have a good password prompter for Windows, you might consider sending it to me for inclusion in the recipes distribution.)
2.11.4.2 PHP
PHP provides little support for option processing from the command line because it is used predominantly in a web environment where command-line arguments are not widely used. Hence, I'm providing no getopt( )-style example for PHP. If you want to go ahead and write your own argument processing routine, use the $argv array containing the arguments and the $argc variable indicating the number of arguments. $argv[0] is the program name, and $argv[1] to $argv[$argc-1] are the following arguments. The following code illustrates how to access these variables:
print ("Number of arguments: $argc "); print ("Program name: $argv[0] "); print ("Arguments following program name: "); if ($argc == 1) print ("None "); else { for ($i = 1; $i < $argc; $i++) print ("$i: $argv[$i] "); }
2.11.4.3 Python
Python passes command arguments to scripts as a list in the sys.argv variable. You can access this variable by importing the sys module, then process its contents with getopt( ) if you also import the getopt module. The following program illustrates how to get parameters from the command arguments and use them for establishing a connection to the server:
#! /usr/bin/python # cmdline.py - demonstrate command-line option parsing in Python import sys import getopt import MySQLdb try: opts, args = getopt.getopt (sys.argv[1:], "h:p:u:", [ "host=", "password=", "user=" ]) except getopt.error, e: # print program name and text of error message print "%s: %s" % (sys.argv[0], e) sys.exit (1) # default connection parameter values host_name = password = user_name = "" # iterate through options, extracting whatever values are present for opt, arg in opts: if opt in ("-h", "--host"): host_name = arg elif opt in ("-p", "--password"): password = arg elif opt in ("-u", "--user"): user_name = arg try: conn = MySQLdb.connect (db = "cookbook", host = host_name, user = user_name, passwd = password) print "Connected" except MySQLdb.Error, e: print "Cannot connect to server" print "Error:", e.args[1] print "Code:", e.args[0] sys.exit (1) conn.close ( ) print "Disconnected" sys.exit (0)
getopt( ) takes either two or three arguments:
getopt( ) returns two values. The first is a list of option/value pairs, and the second is a list of any remaining arguments following the last option. cmdline.py iterates through the option list to determine which options are present and what their values are. Note that although you do not specify leading dashes in the option names passed to getopt( ), the names returned from that function do include leading dashes.
cmdline.py doesn't prompt for a missing password, because the getopt( ) module doesn't provide any way to specify that an option's argument is optional. Unfortunately, this means the -p and --password arguments cannot be specified without a password value.
2.11.4.4 Java
Java passes command-line arguments to programs in the array that you name in the main( ) declaration. The following declaration uses args for that array:
public static void main (String[ ] args)
A Getopt class for parsing arguments in Java is available at Install this class somewhere and make sure its installation directory is named in the value of your CLASSPATH environment variable. Then you can use Getopt as shown in the following example program:
// Cmdline.java - demonstrate command-line option parsing in Java import java.io.*; import java.sql.*; import gnu.getopt.*; // need this for the Getopt class public class Cmdline { public static void main (String[ ] args) { Connection conn = null; String url = null; String hostName = null; String password = null; String userName = null; boolean promptForPassword = false; LongOpt[ ] longOpt = new LongOpt[3]; int c; longOpt[0] = new LongOpt ("host", LongOpt.REQUIRED_ARGUMENT, null, 'h'); longOpt[1] = new LongOpt ("password", LongOpt.OPTIONAL_ARGUMENT, null, 'p'); longOpt[2] = new LongOpt ("user", LongOpt.REQUIRED_ARGUMENT, null, 'u'); // instantiate option-processing object, then // loop until there are no more options Getopt g = new Getopt ("Cmdline", args, "h:p::u:", longOpt); while ((c = g.getopt ( )) != -1) { switch (c) { case 'h': hostName = g.getOptarg ( ); break; case 'p': // if password option was given with no following // value, need to prompt for the password password = g.getOptarg ( ); if (password == null) promptForPassword = true; break; case 'u': userName = g.getOptarg ( ); break; case ':': // a required argument is missing case '?': // some other error occurred // no error message needed; getopt( ) prints its own System.exit (1); } } if (password == null && promptForPassword) { try { DataInputStream s = new DataInputStream (System.in); System.err.print ("Enter password: "); // really should turn off character echoing here... password = s.readLine ( ); } catch (Exception e) { System.err.println ("Error reading password"); System.exit (1); } } try { // construct URL, noting whether or not hostName // was given; if not, MySQL will assume localhost if (hostName == null) hostName = ""; url = "jdbc:mysql://" + hostName + "/cookbook"; Class.forName ("com.mysql.jdbc.Driver").newInstance ( ); conn = DriverManager.getConnection (url, userName, password); System.out.println ("Connected"); } catch (Exception e) { System.err.println ("Cannot connect to server"); } finally { if (conn != null) { try { conn.close ( ); System.out.println ("Disconnected"); } catch (Exception e) { } } } } }
As the example program demonstrates, you prepare to parse arguments by instantiating a new Getopt object to which you pass the program's arguments and information describing the options the program allows. Then you call getopt( ) in a loop until it returns -1 to indicate that no more options are present. Each time through the loop, getopt( ) returns a value indicating which option it's seen, and getOptarg( ) may be called to obtain the option's argument, if necessary. (getOptarg( ) returns null if no following argument was provided.)
When you create an instance of the Getopt( ) class, pass it either three or four arguments:
The example program uses null as the StringBuffer parameter for each long option object and the corresponding short option letter as the fourth parameter. This is an easy way to cause getopt( ) to return the short option letter for both the short and long options, so that you can handle them with the same case statement.
After getopt( ) returns -1 to indicate that no more options were found in the argument array, getOptind( ) returns the index of the first argument following the last option. The following code fragment shows one way to access the remaining arguments:
for (int i = g.getOptind ( ); i < args.length; i++) System.out.println (args[i]);
The Getopt class offers other option-processing behavior in addition to what I've described here. Read the documentation included with the class for more information.
One deficiency of Cmdline.java that you may want to address is that it doesn't disable character echoing while it's reading the password.
2.11.5 Getting Parameters from Option Files
If your API allows it, you can specify connection parameters in a MySQL option file and the API will read the parameters from the file for you. For APIs that do not support option files directly, you may be able to arrange to read other types of files in which parameters are stored, or to write your own functions that read option files.
The format of option files was described in Chapter 1. I'll assume that you've read the discussion there and concentrate here on how to use option files from within programs. Under Unix, user-specific options are specified by convention in ~/.my.cnf (that is, in the .my.cnf file in your home directory). However, the MySQL option file mechanism can look in several different files. The standard search order is /etc/my.cnf, the my.cnf file in the server's default data directory, and the ~/.my.cnf file for the current user. Under Windows, the search order is the my.ini file in the Windows system directory, C:my.cnf, and the my.cnf file in the server's default data directory. If multiple option files exist and a parameter is specified in several of them, the last value found takes precedence. However, it's not an error for any given option file not to exist.
MySQL option files will not be used by your own programs unless you tell them to do so. Perl and Python provide direct API support for reading option files; simply indicate that you want to use them at the time that you connect to the server. It's possible to specify that only a particular file should be read, or that the standard search order should be used to look for multiple option files. PHP and Java do not support option files. As a workaround for PHP, we'll write a simple option file parsing function. For Java, we'll adopt a different approach that uses properties files.
Although the conventional name under Unix for the user-specific option file is .my.cnf in the current user's home directory, there's no rule your programs must use this particular file. You can name an option file anything you like and put it wherever you want. For example, you might set up a file /usr/local/apache/lib/cb.cnf for use by web scripts that access the cookbook database. Under some circumstances, you may even want to create multiple files. Then, from within any given script, you can select the file that's appropriate for the type of permissions the script needs. For example, you might have one option file, cb.cnf, that lists parameters for a full-access MySQL account, and another file, cb-ro.cnf, that lists connection parameters for an account that needs only read-only access to MySQL. Another possibility is to list multiple groups within the same option file and have your scripts select options from the appropriate group.
2.11.5.1 Perl
Perl DBI scripts can use option files if you have DBD::mysql 1.21.06 or later. To take advantage of this, place the appropriate option specifiers in the third component of the data source name string:
The following example tells MySQL to use the standard option file search order to look for options in both the [cookbook] and [client] groups:
# basic DSN my $dsn = "DBI:mysql:database=cookbook"; # look in standard option files; use [cookbook] and [client] groups $dsn .= ";mysql_read_default_group=cookbook"; my $dbh = DBI->connect ($dsn, undef, undef, { PrintError => 0, RaiseError => 1 });
The next example explicitly names the option file located in $ENV{HOME}, the home directory of the user running the script. Thus, MySQL will look only in that file and will use options from the [client] group:
# basic DSN my $dsn = "DBI:mysql:database=cookbook"; # look in user-specific option file owned by the current user $dsn .= ";mysql_read_default_file=$ENV{HOME}/.my.cnf"; my $dbh = DBI->connect ($dsn, undef, undef, { PrintError => 0, RaiseError => 1 });
If you pass an empty value (undef or the empty string) for the username or password arguments of the connect( ) call, connect( ) uses whatever values are found in the option file or files. A nonempty username or password in the connect( ) call overrides any option file value. Similarly, a host named in the DSN overrides any option file value. You can use this behavior to allow DBI scripts to obtain connection parameters both from option files as well as from the command line as follows:
If a script follows this procedure, parameters given by the user on the command line are passed to connect( ) and take precedence over the contents of option files.
2.11.5.2 PHP
PHP has no native support for using MySQL option files, at least at the moment. To work around that limitation, use a function that reads an option file, such as the read_mysql_option_file( ) function shown below. It takes as arguments the name of an option file and an option group name or an array containing group names. (Group names should be named without square brackets.) Then it reads any options present in the file for the named group or groups. If no option group argument is given, the function looks by default in the [client] group. The return value is an array of option name/value pairs, or FALSE if an error occurs. It is not an error for the file not to exist.
function read_mysql_option_file ($filename, $group_list = "client") { if (is_string ($group_list)) # convert string to array $group_list = array ($group_list); if (!is_array ($group_list)) # hmm ... garbage argument? return (FALSE); $opt = array ( ); # option name/value array if (!($fp = fopen ($filename, "r"))) # if file does not exist, return ($opt); # return an empty list $in_named_group = 0; # set non-zero while processing a named group while ($s = fgets ($fp, 1024)) { $s = trim ($s); if (ereg ("^[#;]", $s)) # skip comments continue; if (ereg ("^[([^]]+)]", $s, $arg)) # option group line? { # check whether we're in one of the desired groups $in_named_group = 0; reset ($group_list); while (list ($key, $group_name) = each ($group_list)) { if ($arg[1] == $group_name) { $in_named_group = 1; # we are break; } } continue; } if (!$in_named_group) # we're not in a desired continue; # group, skip the line if (ereg ("^([^ =]+)[ ]*=[ ]*(.*)", $s, $arg)) $opt[$arg[1]] = $arg[2]; # name=value else if (ereg ("^([^ ]+)", $s, $arg)) $opt[$arg[1]] = ""; # name only # else line is malformed } return ($opt); }
Here are a couple of examples showing how to use read_mysql_option_file( ). The first reads a user's option file to get the [client] group parameters, then uses them to connect to the server. The second reads the system-wide option file and prints the server startup parameters that are found there (that is, the parameters in the [mysqld] and [server] groups):
$opt = read_mysql_option_file ("/u/paul/.my.cnf"); $link = @mysql_connect ($opt["host"], $opt["user"], $opt["password"]); $opt = read_mysql_option_file ("/etc/my.cnf", array ("mysqld", "server")); while (list ($name, $value) = each ($opt)) print ("$name => $value ");
If you're using the MySQL_Access interface that was developed in Recipe 2.10, you might think about how to extend the class by implementing a derived class that gets the username, password, and hostname from an option file. You could also give this derived class the ability to search multiple files, which is an aspect of the usual option file behavior that read_mysql_option_file( ) does not provide.
2.11.5.3 Python
The MySQLdb module for DB-API provides direct support for using MySQL option files. Specify an option file or option group using read_default_file or read_default_group arguments to the connect( ) method. These two arguments act the same way as the mysql_read_default_file and mysql_read_default_group options for the Perl DBI connect( ) method (see the Perl discussion earlier in this section). To use the standard option file search order to look for options in both the [cookbook] and [client] groups, do something like this:
try: conn = MySQLdb.connect (db = "cookbook", read_default_group = "cookbook") print "Connected" except: print "Cannot connect to server" sys.exit (1)
The following example shows how to use the .my.cnf file in the current user's home directory to obtain parameters from the [client]group:[8]
[8] You must import the os module to access os.environ.
try: option_file = os.environ["HOME"] + "/" + ".my.cnf" conn = MySQLdb.connect (db = "cookbook", read_default_file = option_file) print "Connected" except: print "Cannot connect to server" sys.exit (1)
2.11.5.4 Java
The MySQL Connector/J JDBC driver doesn't support option files. However, the Java class library provides support for reading properties files that contain lines in name=value format. This is somewhat similar to MySQL option file format, although there are some differences (for example, properties files do not allow [groupname] lines). Here is a simple properties file:
# this file lists parameters for connecting to the MySQL server user=cbuser password=cbpass host=localhost
The following program, ReadPropsFile.java, shows one way to read a properties file named Cookbook.properties to obtain connection parameters. The file must be in a directory named in your CLASSPATH variable, or else you must specify it using a full pathname (the example shown here assumes the file is in a CLASSPATH directory):
import java.sql.*; import java.util.*; // need this for properties file support public class ReadPropsFile { public static void main (String[ ] args) { Connection conn = null; String url = null; String propsFile = "Cookbook.properties"; Properties props = new Properties ( ); try { props.load (ReadPropsFile.class.getResourceAsStream (propsFile)); } catch (Exception e) { System.err.println ("Cannot read properties file"); System.exit (1); } try { // construct connection URL, encoding username // and password as parameters at the end url = "jdbc:mysql://" + props.getProperty ("host") + "/cookbook" + "?user=" + props.getProperty ("user") + "&password=" + props.getProperty ("password"); Class.forName ("com.mysql.jdbc.Driver").newInstance ( ); conn = DriverManager.getConnection (url); System.out.println ("Connected"); } catch (Exception e) { System.err.println ("Cannot connect to server"); } finally { try { if (conn != null) { conn.close ( ); System.out.println ("Disconnected"); } } catch (SQLException e) { /* ignore close errors */ } } } }
If you want getProperty() to return a particular default value when the named property is not found, pass that value as a second argument. For example, to use localhost as the default host value, call getProperty() like this:
String hostName = props.getProperty ("host", "localhost");
The Cookbook.class library file developed earlier in the chapter (Recipe 2.4) includes a propsConnect() routine that is based on the concepts discussed here. To use it, set up the contents of the properties file, Cookbook.properties, and copy the file to the same location where you installed Cookbook.class. Then you can establish a connection within a program by importing the Cookbook class and calling Cookbook.propsConnect() rather than by calling Cookbook.connect().