There are lots of ways to do this. Take your pick from the alternatives in the following section.
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 of the techniques you can use and 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 technique is convenient because users need not enter the values themselves. The flip side 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 application 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 enable you to easily recall commands from your history list for re-execution.)
The most common way to use this method is to set the appropriate environment variables in one of your shell’s startup files (such as .profile for sh, bash, ksh; or .login for csh or tcsh). 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 technique 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 enables 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 restrict access 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 give users the flexibility 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. They then check the command-line arguments for further parameters. This enables users to specify connection parameters in an option file or on the command line.
These methods of obtaining connection parameters do involve some security concerns. Here is a brief summary of the issues:
Any method that stores connection parameters in a file may compromise your system’s security unless the file is protected against access by unauthorized users. This is true whether parameters are stored in a source file, an option file, or a script that invokes a command and specifies the parameters on the command line. (Web scripts that can be read only by the web server don’t qualify as secure if other users have administrative access to the server.)
Parameters specified on the command line or in environment
variables are not particularly secure. While a program is
executing, its command-line arguments and environment may be
visible to other users who run process status commands such as
ps
-e
. In
particular, storing the password in an environment variable
perhaps is best limited to those situations in which you’re the
only user on the machine or you trust all other users.
The rest of this section shows how to process command-line arguments to get connection parameters and how to read parameters from option files.
The usual MySQL convention for command-line arguments (that is,
the convention followed by standard clients such as
mysql and
mysqladmin) 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 either of the password options (-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.
To enable a script to use other options, such as
--port
or --socket
, you can use
the code shown but extend the option-specifier arrays to include
additional options. You’ll also need to modify the
connection-establishment code slightly to use the option values if
they are given.
For those APIs shown here (Perl, Ruby, Python), the programs
presented use a getopt()
-style function. For
Java, look under the api
directory in the recipes
distribution for sample code that is not shown here, as well as
instructions for using it.
Insofar as possible, the examples mimic the option-handling
behavior of the standard MySQL clients. An exception is that
option-processing libraries may not allow for making the password
value optional, and they provide no way of prompting the user for
a password interactively if a password option is specified without
a password value. Consequently, the example scripts are written so
that if you use -p
or --password
, you must provide the
password value following the option.
Perl. Perl passes command-line arguments to scripts via
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.
#!/usr/bin/perl # cmdline.pl - demonstrate command-line option parsing in Perl use strict; use warnings; 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; my $password; my $user_name; GetOptions ( # =s means a string value is required 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 # any remaining nonoption arguments are left # in @ARGV and can be processed here as necessary # construct data source name my $dsn = "DBI:mysql:database=cookbook"; $dsn .= ";host=$host_name" if defined ($host_name); # connect to server my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1); my $dbh = DBI->connect ($dsn, $user_name, $password, \%conn_attrs); print "Connected "; $dbh->disconnect (); print "Disconnected ";
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 value. For example, "host|h=s"
allows both
--host
and -h
and indicates that a
following string value is required. You need not pass the @ARGV
array because GetOptions()
uses it implicitly.
When GetOptions()
returns,
@ARGV
contains any remaining
nonoption arguments.
Ruby. Ruby programs access command-line arguments via the
ARGV
array, which you can process
with theGetoptLong.new()
method. The
following program uses this method to parse the command arguments
for connection parameters:
#!/usr/bin/ruby -w # cmdline.rb - demonstrate command-line option parsing in Ruby require "getoptlong" require "dbi" # connection parameters - all missing (nil) by default host_name = nil password = nil user_name = nil opts = GetoptLong.new( [ "--host", "-h", GetoptLong::REQUIRED_ARGUMENT ], [ "--password", "-p", GetoptLong::REQUIRED_ARGUMENT ], [ "--user", "-u", GetoptLong::REQUIRED_ARGUMENT ] ) # iterate through options, extracting whatever values are present; # opt will be the long-format option, arg is its value opts.each do |opt, arg| case opt when "--host" host_name = arg when "--password" password = arg when "--user" user_name = arg end end # any remaining nonoption arguments are left # in ARGV and can be processed here as necessary # construct data source name dsn = "DBI:Mysql:database=cookbook" dsn << ";host=#{host_name}" unless host_name.nil? # connect to server begin dbh = DBI.connect(dsn, user_name, password) puts "Connected" rescue DBI::DatabaseError => e puts "Cannot connect to server" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" exit(1) end dbh.disconnect() puts "Disconnected"
To process the
ARGV
array, use
the GetoptLong.new()
method, and pass information to it that indicates which options to
recognize. Each argument to this method is an array of three
values:
The long option name.
The short option name.
A flag that indicates whether the option requires a value.
The allowable flags are GetoptLong::NO_ARGUMENT
(option takes
no value), GetoptLong::REQUIRED_ARGUMENT
(option
requires a value), and GetoptLong::OPTIONAL_ARGUMENT
(option
value is optional). For the example program, all options require
a value.
Python. Python passes command arguments to scripts as a list via the
sys.argv
variable. You can access
this variable and process its contents by importing the sys
and getopt
modules. 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: # for errors, print program name and text of error message print "%s: %s" % (sys.argv[0], e) sys.exit (1) # default connection parameter values (all empty) 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 # any remaining nonoption arguments are left in # args and can be processed here as necessary 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"
getopt()
takes either two or three arguments:
A list of command arguments to be processed. This should
not include the program name, sys.argv[0]
, so use sys.argv[1:]
to refer to the list of
arguments that follow the program name.
A string naming the short option letters. In cmdline.py, each of these is followed
by a colon character (:
) to
indicate that the option requires a following value.
An optional list of long option names. In cmdline.py, each name is followed by
=
to indicate that the option
requires a following value.
getopt()
returns two
values. The first is a list of option/value pairs, and the second is
a list of any remaining nonoption 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.
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.
Specifying Connection Parameters Using Option Files describes the format of MySQL option files. 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 if they exist (no option file is required to exist). The standard search order is /etc/my.cnf, the my.cnf file in the MySQL installation directory, and the ~/.my.cnf file for the current user. Under Windows, the option files you can use are the my.ini file in your MySQL installation directory (for example, C:Program FilesMySQLMySQL Server 5.0), my.ini in your Windows directory (this is something like C:Windows or C:WINNT), or the my.cnf file.
If multiple option files exist and a given parameter is specified in several of them, the last value found takes precedence.
MySQL option files are not used by your own programs unless you tell them to do so:
Perl, Ruby, 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 that 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 named
mcb.cnf and install it in the
/usr/local/lib/mcb directory
for use by scripts that access the cookbook
database. Under some
circumstances, you might even want to create multiple option 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, mcb.cnf, that lists parameters for a
full-access MySQL account, and another file, mcb-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.
Perl. Perl DBI scripts can use option files. To take advantage of this, place the appropriate option specifiers in the third component of the data source name string:
To specify an option group, use mysql_read_default_group=
.
This tells MySQL to search the standard option files for options
in the named group and in the groupname
[client]
group. The
groupname
value should
be written without the
square brackets that are part of the line that
begins the group. For example, if a group in an option file
begins with a [my_prog]
line,
specify my_prog
as the
groupname
value. To search the
standard files but look only in the [client]
group,
groupname
should be client
.
To name a specific option file, use mysql_read_default_file=
in the DSN. When you do this, MySQL looks only in that file and
only for options in the filename
[client]
group.
If you specify both an option file and an option group,
MySQL reads only the named file, but looks for options both in
the named group and in the [client]
group.
The following example tells MySQL to use the standard option
file search order to look for options in both the [cookbook]
and [client]
groups:
my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1); # 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, \%conn_attrs);
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:
my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1); # 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, \%conn_attrs);
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 enable DBI scripts to
obtain connection parameters both from option files as well as from
the command line as follows:
Create $host_name
,
$user_name
, and $password
variables and initialize
them to undef
. Then parse the
command-line arguments to set the variables to non-undef
values if the corresponding
options are present on the command line. (The cmdline.pl Perl script shown earlier
in this recipe for processing command-line arguments
demonstrates how to do this.)
After parsing the command arguments, construct the DSN
string, and call connect()
. Use mysql_read_default_group
and mysql_read_default_file
in the DSN to
specify how you want option files to be used, and, if $host_name
is not undef
, add host=$host_name
to the DSN. In
addition, pass $user_name
and
$password
as the username and
password arguments to connect()
. These will be
undef
by default; if they
were set from the command-line arguments, they will have
non-undef
values that
override any option file values.
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.
Ruby. Ruby DBI scripts can access option files by using a mechanism analogous to that used for Perl DBI, and the following examples correspond exactly to those shown in the preceding Perl discussion.
This example use the standard option file search order to look
for options in both the [cookbook]
and [client]
groups:
# basic DSN dsn = "DBI:Mysql:database=cookbook" # look in standard option files; use [cookbook] and [client] groups dsn << ";mysql_read_default_group=cookbook" dbh = DBI.connect(dsn, nil, nil)
The following example uses the .my.cnf file in the current user’s home
directory to obtain parameters from the [client]
group:
# basic DSN 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" dbh = DBI.connect(dsn, nil, nil)
PHP. PHP
has no native support for using MySQL option files. To work around
that limitation, use a function that reads an option file, such as
theread_mysql_option_file()
function
shown in the following listing. 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.) It
then 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. (Note that quoted option values and
trailing #
-style comments
following option values are legal in MySQL option files, but this
function does not handle those constructs.)
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 nonzero 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; foreach ($group_list as $key => $group_name) { if ($arg[1] == $group_name) { $in_named_group = 1; # we are in a desired group 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 and then uses
them to connect to the server. The second reads the system-wide
option file, /etc/my.cnf, 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"); $dsn = array ( "phptype" => "mysqli", "username" => $opt["user"], "password" => $opt["password"], "hostspec" => $opt["host"], "database" => "cookbook" ); $conn =& DB::connect ($dsn); if (PEAR::isError ($conn)) print ("Cannot connect to server "); $opt = read_mysql_option_file ("/etc/my.cnf", array ("mysqld", "server")); foreach ($opt as $name => $value) print ("$name => $value ");
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:
conn = MySQLdb.connect (db = "cookbook", read_default_group = "cookbook")
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:
option_file = os.environ["HOME"] + "/" + ".my.cnf" conn = MySQLdb.connect (db = "cookbook", read_default_file = option_file)
You must import the os
module to access os.environ
.
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 [
lines). Here is a simple properties file:groupname
]
# 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 some directory that is named in your
CLASSPATH
variable,
or else you must specify it using a full pathname (the example shown
here assumes that 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 127.0.0.1
as the default host
value, call getProperty()
like this:
String hostName = props.getProperty ("host", "127.0.0.1");
The Cookbook.java library
file developed earlier in the chapter (Writing Library Files) includes an extra library call in
the version of the file that you’ll find in the lib directory of the recipes
distribution: 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. You can then establish a
connection within a program by importing the Cookbook
class and calling Cookbook.propsConnect()
rather than
by calling
Cookbook.connect()
.
18.216.171.107