An API allows a programming language to interface easily with a computer software system. The advantage of an API is that you can customize user interfaces to MySQL and MariaDB to suit your needs. Huge websites use APIs to allow the public to interact with their MySQL and MariaDB databases, without the user needing to know anything about the databases they’re using or SQL statements.
This chapter covers several APIs that may be used to interface with MySQL and MariaDB, so that you may write customized applications to interface with databases. There are sections for the C API, the Perl DBI, the PHP API, the Connector/Python, and the Ruby API. Many other programming languages have APIs for connecting to MySQL; these are just some of the more popular ones. The section on each API and related libraries includes a basic tutorial on how to connect to MySQL and MariaDB, and how to query a database with the API.
It’s unlikely you will need to know more than one API. Instead, you may want to read the section for the programming language you know and use. My preference is the Perl language and the Perl DBI. It’s most in line with natural languages such as English and Italian. If you have no preference and would like to learn a MySQL API, though, the PHP API is very popular and has many functions for interacting with MySQL. Plus, PHP is a fairly easy language to learn and you can use snippets of code within web pages and content management systems like Wordpress and Drupal.
It’s beyond the scope of this book to include a tutorial on any programming language. I assume you can learn the basics of the language you choose among the many books and online resources available. These examples use basic features of the languages to show you how database access works.
Before skipping ahead to a section about a particular API, you should create a couple of API user accounts that you may use in the examples and in the exercises. The exercises at the end of the chapter are suited to whichever API you prefer, not to one in particular.
Assuming that the programs that we’ll write may be executed by the public,
let’s create a user account specifically for them (creating users was
covered in Chapter 13). We’ll call this user
account public_api and give it
only the SELECT privilege for the
rookery and birdwatchers databases. Execute the
following on your server:
CREATEUSER'public_api'@'localhost'IDENTIFIEDBY'pwd_123';GRANTSELECTONrookery.*TO'public_api'@'localhost';GRANTSELECTONbirdwatchers.*TO'public_api'@'localhost';
This creates the public_api@localhost user account with the
password pwd_123. You can give it a
more secure and different password. It has access just to our two
databases from the localhost. It can only execute SELECT
statements and can’t change or delete data or do anything else. We’ll use
this user account for the API programs that we’ll create, which retrieve
data through a public web page.
For some of the API programs we will write, we’ll need another administrative user account, admin_members. It will be designated for administering information on members of our site. Create that user account by executing the following SQL statements:
CREATEUSER'admin_members'@'localhost'IDENTIFIEDBY'doc_killdeer_123';GRANTSELECT,UPDATE,DELETEONbirdwatchers.*TO'admin_members'@'localhost';
This administrative user account can select, update, and delete data
only on the birdwatchers database. It mostly needs access to
the humans table, but may sometimes need access to the other
tables in the database. It won’t use the rookery database, so
we’re not giving it access to that database.
The C language isn’t as popular as it once was, but it’s still a standard. In fact, the core software of MySQL is written in C. The C API is provided by MySQL. This section provides a basic tutorial on how to connect to a database and how to query it with C and the C API, the basic components and tasks you need to know to use this API.
When writing a C program to interact with a database, first we need to prepare variables that will store data on the database connection and the results of a query we intend to execute. Then we will need to establish a connection to the server. To do this easily, we’ll include a couple of C header files: stdio.h for basic C functions and variables, and mysql.h for special MySQL functions and definitions (these two files come with C and MySQL, as well as MariaDB; you shouldn’t have to download them if C and MySQL were installed properly on your server):
#include <stdio.h>#include "/usr/include/mysql/mysql.h"intmain(intargc,char*argv[]){MYSQL*mysql;MYSQL_RES*result;MYSQL_ROWrow;...
The < and > symbols surrounding
stdio.h tells C to look for the
file in the default location for C header files (e.g., /usr/include), or in the user’s path. Because
mysql.h may not be in the default
locations, the absolute path is given within double quotes. An
alternative here would have been <mysql/mysql.h>,
because the header file is in a subdirectory of the default C header
file directory.
The standard main function begins by preparing
variables needed for the connection to MySQL. The first line creates a
pointer to the MYSQL structure stored in the
mysql variable. The next line defines and names a results
set based on the definitions for MYSQL_RES in mysql.h. The results are to be stored in the
result array, which will be an array of rows from MySQL.
The third line of main uses the definition for
MYSQL_ROW to establish the row variable, which will be used
later to contain an array of columns from MySQL.
Having included the header files and set the initial variables, we
can now set up an object in memory for interacting with the MySQL server
using the mysql_init() function:
...if(mysql_init(mysql)==NULL){fprintf(stderr,"Cannot Initialize MySQL");return1;}...
The if statement here is testing whether a MySQL
object can be initialized. If the initialization fails, a message is
printed and the program ends. The mysql_init() function
initializes the MySQL object using the MYSQL structure
declared at the beginning of the main function, which is
called by convention, mysql . If C is successful in
initializing the object, it will go on to attempt to establish a
connection to the MySQL server:
...if(!mysql_real_connect(mysql,"localhost","public_api","pwd_123","rookery",0,NULL,0)){fprintf(stderr,"%d: %s\n",mysql_errno(mysql),mysql_error(mysql));return1;}...
The elements of the mysql_real_connect() function here are
fairly obvious: first the MySQL object is referenced; next the hostname
or IP address; then the username and password; and finally the database
to use. For this example, we’re using the public_api@localhost user account we created in
the beginning of this chapter. The three remaining items are the port
number, the socket filename, and a client flag, if any. Passing 0 and
NULL values tells the function to use the defaults for these.
If the program cannot connect, it prints the error message
generated by the server to the standard error stream, along with the
MySQL error number (%d), and finally a string
(%s) containing the MySQL error message and then a newline
(\n). It will get the error number from the
mysql_errno() function and the error message from the mysql_error() function. If the program
can connect without an error, though, it will return 1 to indicate
success and continue with the program.
The program so far only makes a connection to MySQL. Now let’s look at how you can add code to the program to run an SQL statement with the C API.
If the API program has connected to MySQL, it can query the MySQL
server with a query function such as mysql_query(). Let’s use
SELECT to get a list of birds from the birds
table. The code for doing this and displaying the results is as
follows:
...if(mysql_query(mysql,"SELECT common_name, scientific_name FROM birds")){fprintf(stderr,"%d: %s\n",mysql_errno(mysql),mysql_error(mysql));}else{result=mysql_store_result(mysql);while(row=mysql_fetch_row(result)){printf("\%s - \%s\n",row[0],row[1]);}mysql_free_result(result);}mysql_close(mysql);return0;}
Within the if statement here, we’re using
mysql_query() to query MySQL. You could use the
mysql_real_query() function instead. It allows the retrieval of binary data, which can
be safer, but isn’t necessary for this simple example. The
mysql_query() function returns 0 if it’s successful and
nonzero if it’s not. So if the SQL statement contained within it doesn’t
succeed in selecting data from MySQL, an error message will be printed.
However, if the query is successful, the else statement
will then be executed, because the if statement will have
received a value of 0.
In the else statement block, the first line uses
the mysql_store_result() function to store
the results of the query in the result variable.
Before letting go of the data, using while, the code
loops through each row of the results set. We’re using the mysql_fetch_row() function to fetch each
row and store it temporarily in the row variable. Because
we know how the data is organized from the SELECT
statement, we can use printf with its formatting codes to
display each column. Notice that each column is extracted with standard
array syntax (i.e., array
[).n]
Once C has gone through each row of the results, it will stop
processing and use mysql_free_result() to free the memory for result, concluding the
else statement.
We end this brief program with the mysql_close() function to end the
MySQL session and to disconnect from MySQL. The final closing curly
brace ends the main function.
It’s easier to explain the components of a program step by step as I have done here, but seeing even a small program in pieces can be confusing. So here it is again in its entirety:
#include <stdio.h>#include "/usr/include/mysql/mysql.h"intmain(intargc,char*argv[]){MYSQL*mysql;MYSQL_RES*result;MYSQL_ROWrow;if(mysql_init(mysql)==NULL){fprintf(stderr,"Cannot Initialize MySQL");return1;}if(!mysql_real_connect(mysql,"localhost","public_api","pwd_123","rookery",0,NULL,0)){fprintf(stderr,"%d: %s\n",mysql_errno(mysql),mysql_error(mysql));return1;}if(mysql_query(mysql,"SELECT common_name, scientific_name FROM birds")){fprintf(stderr,"%d: %s\n",mysql_errno(mysql),mysql_error(mysql));}else{result=mysql_store_result(mysql);while(row=mysql_fetch_row(result)){printf("\%s - \%s\n",row[0],row[1]);}mysql_free_result(result);}mysql_close(mysql);return0;}
You can use any compiler to compile the program we wrote, but I’ll show the GNU C Compiler (gcc) here because it’s free software and automatically loaded on some systems. To compile and link the program, enter something like the following from the command line:
gcc -c `mysql_config --cflags`mysql_c_prog.cgcc -omysql_c_prog mysql_c_prog.o`mysql_config --libs`
When the compiler attempts to compile the program (mysql_c_prog.c), it will check for syntax errors in the code. If it finds any, it will fail to compile and will display error messages. If it’s successful, the resulting compiled program (mysql_c_prog) will be ready to be executed.
The easiest method of connecting to MySQL with the Perl programming language is to use the Perl DBI module. This section assumes that you have a basic knowledge of the Perl language. We’ll focus on how to connect to MySQL, run SQL statements, and retrieve data with Perl, rather than the idiosyncrasies of Perl itself. This is meant to be a simple tutorial for a Perl programmer to get started with the Perl DBI.
For the example in this section, suppose we want to write a program
for one of the administrators to get a list of members and to optionally
change the expiration of their membership. For this, we’ll use the
admin_members user account that’s designated for
administering information on members. We created that user account at the
start of this chapter.
The Perl DBI module is part of the core Perl installation. You can download both Perl and the DBI module from CPAN.
If your server already has Perl installed on it, which most do, you can execute the following from the command line to install the DBI module:
perl-MCPAN-e'install DBI'
If you don’t have Perl installed already on your server, you can use an installation utility like yum to install the DBI module. If you have yum on your server, enter the following from the command line while logged in as root or an administrative filesystem user:
yum install perl perl-mysql
To interface with MySQL, you must first call the DBI module and then connect to MySQL. To make a connection to a database using the Perl DBI, only the following lines are needed in a Perl program to connect to the database:
#!/usr/bin/perl -wusestrict;useDBI;my$user='admin_members';my$password='doc_killdeer_123';my$host='localhost';my$database='birdwatchers';my$dbh=DBI->connect("DBI:mysql:$database:$host",$user,$password)||die"Could not connect to database: ".DBI->errstr;...
The first two lines start Perl and set a useful condition for
reducing programming errors (i.e., use strict). The next
line calls the DBI module. Then we create a set of variables containing
values for logging into MySQL. The next statement, which is spread over
two lines, sets up a database handle ($dbh) that specifies
the database engine (mysql). We give it the login
variables. The rest of the statement relates to what to do if the
program is unable to connect to MySQL. If the connection is successful,
though, the program will continue on.
Making a connection to MySQL does little good unless an SQL
statement is executed. Any SQL statement can be executed through an API.
The only restrictions are those imposed by the MySQL server on the user
account executing the SQL statements within the application. If the user
account can execute only SELECT statements, that’s all that
the application may execute. Let’s look at some examples here of how to
select and insert data in MySQL through an application.
Continuing the previous example, let’s execute a
SELECT to get a list of birds from the birds
table. Let’s allow the user of the Perl program to specify a common
name of birds to select, when executing it from the command line. For
instance, the user might enter Avocet to get a
list of Avocet birds. We’ll use a LIKE operator in the
WHERE clause to allow for some flexibility. Here’s how
the code for that would look:
...my$search_parameter=shift;my$sql_stmnt="SELECT human_id,CONCAT(name_first, SPACE(1), name_last) AS full_name,membership_expirationFROM humansWHERE name_last LIKE ?";my$sth=$dbh->prepare($sql_stmnt);$sth->execute("%$search_parameter%");...
The first line here sets up a variable,
$search_parameter, to store a value from
shift, which loads into that variable the value given by
the user when executing the program. On the next line of code, we
create the $sql_stmnt variable to store the SQL
statement. Notice that where we would specify the last name of the
member in the WHERE clause, we entered instead a question
mark. This is known as a placeholder. We will
replace the placeholder with
$search_parameter two lines later. Placeholders are a
good security precaution. For more information on this, see SQL Injection.
After creating the $sql_stmnt variable, we use
the prepare() function of the database
handle in order to prepare the SQL statement to form an SQL statement
handle ($sth). Then we use the execute() function to execute the
statement handle, with the $search_parameter to replace
the placeholder. To replace multiple placeholders, you would list them
in a comma-separated list within the parentheses of
execute().
Having connected to MySQL and invoked an SQL statement, what
remains is to capture the data results and to display them to the
administrator. The fetchrow_array() function can be used
to fetch the data one row at a time. We’ll use that with a
while statement. Here’s how that would look:
...while(my($human_id,$full_name,$membership_expiration)=$sth->fetchrow_array()){"$full_name ($human_id) - $membership_expiration \n";}$sth->finish();$dbh->disconnect();
The while statement executes its block of code
repeatedly so long as there are rows to process. The value of each
element of each array (i.e., each row) is stored in the two variables
$common_name and $scientific_name—and
overwritten by each loop of while. Then the variables are
printed to the screen with a newline character after each pair.
The second to last line uses finish() to end the SQL statement
handle. The last line disconnects the database handle with disconnect(). Alternatively,
you can leave open the connection to MySQL so that you can create and
execute more statement handles to interface with MySQL.
A better method of retrieving data from MySQL perhaps would be
to capture all of the data in memory for later use in the Perl
program, thus allowing the connection to MySQL to end before
processing the results. Putting MySQL on hold while processing each
row as shown earlier can slow down a program, especially when dealing
with large amounts of data. It’s sometimes better to create a complex
data structure (i.e., an array of arrays) and then leave the data
structure in memory until needed. To do this, you’d use the fetchall_arrayref() method. It
will return the starting location in memory of the array. Here’s an
example of this:
...my$members=$sth->fetchall_arrayref();$sth->finish();foreachmy$member(@$members){my($human_id,$full_name,$membership_expiration)=@$member;"$full_name ($human_id) - $membership_expiration \n";}$dbh->disconnect();
The fetchall_arrayref() fetches all of the rows,
stores them in an array in memory, and returns a reference to its
location. This is stored in $members. Using a
foreach, we extract each array within the
@$members array and store it in $member.
With the block of the foreach, we extract each element of
the $member array and store those values in
$human_id, $full_name, and
$membership_expiration. We then display them using
print.
Notice that we executed the finish() to end the
statement handle and to free MySQL resources. We could have also put
disconnect() immediately after it if we didn’t intend to
create and execute more SQL statement handles. This would have had no
effect on the foreach processing the results fetched by
fetchall_arrayref().
In the previous examples, we saw how to select data from a
table. Let’s now look at an example that updates data in a table.
We’ll change the $sql_statement to include an
UPDATE statement that will update the date of
membership_expiration for a member in the
humans table. We can do that like this:
...my($human_id,$membership_expiration)=(shift,shift);$sql_stmnt="UPDATE humansSET membership_expiration = ?WHERE human_id = ?";$sth=$dbh->prepare($sql_stmnt);$sth->execute($membership_expiration,$human_id);...
Here we’re using shift twice to capture two values
entered by the user and store them in the $human_id and
$membership_expiration variables. The
$sql_statement is given two placeholders. We replace
those placeholders with the two variables, in the proper order, when
we execute the SQL statement through the statement handle
($sth) using execute().
The end result of this bit of code is to update the row related
to the given $human_id in the humans table.
Because this UPDATE privilege is one to which you might not want the public to have
access, it would be best to use this program just internally from a
known IP address, and to require a password.
It’s easier to explain the components of a program step by step as I have done here, but seeing a program in pieces can be confusing. Combinig these Perl program snippets, we’ll create a program and call it member_adjust_expiration.plx. Here’s how it looks:
#!/usr/bin/perl -w use strict;useDBI;my$search_parameter=shift||'';my$human_id=shift||'';my$membership_expiration=shift||'';my$user='admin_members';my$password='doc_killdeer_123';my$host='localhost';my$database='birdwatchers';my$dbh=DBI->connect("DBI:mysql:$database:$host",$user,$password)||die"Could not connect to database: ".DBI->errstr;if($search_parameter&&!$membership_expiration){my$sql_stmnt="SELECT human_id,CONCAT(name_first, SPACE(1), name_last) AS full_name,membership_expirationFROM humansWHERE name_last LIKE ?";my$sth=$dbh->prepare($sql_stmnt);$sth->execute("%$search_parameter%");my$members=$sth->fetchall_arrayref();$sth->finish();"List of Members - '$search_parameter' \n";foreachmy$member(@$members){my($human_id,$full_name,$membership_expiration)=@$member;"$full_name ($human_id) - $membership_expiration \n";}}if($human_id&&$membership_expiration){$sth=$dbh->prepare($sql_stmnt);$sql_stmnt="UPDATE humansSET membership_expiration = ?WHERE human_id = ?";$sth=$dbh->prepare($sql_stmnt);my($rc)=$sth->execute($email_address,$human_id);$sth->finish();if($rc){"Membership Expiration Changed. \n";}else{"Unable to change Membership Expiration. \n";}}$dbh->disconnect();exit();
If this program is executed from the command line, adding the last
name of the Hollar after the name of the program,
it will return the name of Lexi Hollar with her human_id in
parentheses, along with the date her membership expires. The following
example shows how a user might execute the program, and the results
returned from running it with this user value:
member_adjust_expiration.plx Hollar
List of Members - 'Hollar'
Lexi Hollar (4) - 2013-09-22The program can be run again with a new expiration date for the member like so:
member_adjust_expiration.plx Hollar 4 2015-06-30Notice that the program is expecting three values. If it receives
only one value, the member’s last name, it executes the
SELECT statement and displays the user information. If it
receives three values, it will execute the UPDATE
statement. Values must be in the correct order and format. The program
will display a message indicating whether it’s successful in changing
the membership expiration date.
You could write this program in more elaborate ways. You could allow the user to select a date, or the number of months or years to add to the expiration date using date functions. You could change it to run through a web interface using the CGI Perl module so that the user can click choices instead of typing them at the command line. However, this simple program gives you a good idea of how to get started writing a Perl API to interface with MySQL.
To learn about Perl, see Learning Perl (O’Reilly) by Randal Schwartz, brian d foy, and Tom Phoenix. For more details on using the Perl DBI with MySQL, see Alligator Descartes and Tim Bunce’s Programming the Perl DBI (O’Reilly). To learn more about Perl references and other advanced Perl topics, see Intermediate Perl (O’Reilly) by Randal Schwartz.
One of the most popular programming language and database engine combinations for the Web is PHP with MySQL. This combination works well for many reasons, but primarily the speed, stability, and simplicity that both offer. In addition, PHP scripts can be used easily with HTML to generate web pages. This section provides a basic tutorial on how to connect to MySQL and how to query MySQL with PHP using the PHP API, all within a web page.
There are actually three popular APIs that may be used to connect to MySQL with PHP. It’s
recommended that you use the mysqli (MySQL
Improved) extension, which replaces the older
mysql extension. We’ll use the mysqli API for
the programming examples in this section.
On many Linux systems, PHP is already installed. However, you can
use an installation utility like yum
to install PHP, as well as the PHP API, mysqli. You would
do that like this:
yum install php php-mysql
If you’ll be executing PHP code within web pages, which is a very
nice feature, you may have to make an adjustment to your web server
configuration. If you’re using Apache, you may have to add the
AddType directive to your Apache configuration to tell the web server to
execute code snippets with PHP. You can either put the following line in
the web server’s configuration file (httpd.conf) to make it global, or add it to a
.htaccess file in the directory
where the HTML pages containing the PHP code snippets are
located:
AddType application/x-httpd-php .html
If you add this directive to the httpd.conf configuration file, you’ll have to restart the Apache web service for it to take effect. You won’t have to do that with the .htaccess file.
To use PHP with MySQL, you may also have to enable MySQL with PHP
by configuring PHP with the
--with-mysql=/path_to_mysql
option. That won’t be necessary, though, if you installed the PHP API
using yum.
For PHP code to interface with MySQL, it must first make a connection to MySQL to establish a MySQL client session. This bit of code will do that:
<?php$host='localhost';$user='public_api';$pw='pwd_123';$db='rookery';$connect=newmysqli($host,$user,$pw,$db);if(mysqli_connect_errno()){printf("Connect failed: %s\n",mysqli_connect_error());exit();}?>
We’ve enclosed the code within <?php...?> tags
so that it may be embedded within an HTML web page. If you wanted to
create a program that is executed from the command line and not by a web
browser, it would have to start with #!/usr/bin/php. For
our examples, though, we’ll stay with writing code for use in a web
page.
The PHP code contained within the <?php...?>
tags creates variables containing information the application will need
to connect to MySQL and select the default database. After those
variables, we’re using the mysqli() function to connect to MySQL
with those variables. We’ll refer to that connection with the variable
we named $connect. If it’s unsuccessful, the script dies
with an error message. If the connection is successful, though, we can
then query the database. The connection will stay open until we close
it.
Let’s continue with our script by retrieving a list of birds from the
birds table. The following snippet would be placed after
the previous snippet that connects to MySQL, but within the same web
page. It will query the database, fetch rows from the birds
table, and display them to the user:
<?php$sql_stmnt="SELECT common_name, scientific_nameFROM birdsWHERE LOWER(common_name) LIKE LOWER(?)";$sth=$connect->prepare($sql_stmnt);$search_parameter=$_REQUEST['birdname'];$search_parameter="%".$search_parameter."%";$sth->bind_param('s',$search_parameter);$sth->execute();$sth->bind_result($common_name,$scientific_name);while($sth->fetch()){"$common_name- <i>$scientific_name</i> <br/>";}$sth->close();$connect->close();?>
The first piece of this creates a variable
($sql_stmnt) containing the SQL statement we want to
execute. We then prepare that statement with the prepare() function in relation to
$connect, thus creating a statement handle
($sth).
A user would execute the program we’re creating through a query at the end of the web address. For instance, they would add ?birdname=Avocet to the web address to query for a list of Avocet birds.
In the next pair of lines in the example, we’re capturing the
query request value in a variable we named
$search_parameter. Because we intend to use this variable
with a LIKE operator, we need to put the %
wildcard before and after the variable.
The next line uses bind_param() to bind the prepared
statement to the $search_parameter, specifying first that
it’s a string value with the 's'. Then we use the
execute() function to execute the completed statement handle.
The bind_result() prepares the variables that will be used to parse the array elements,
or fields of the results. Calling on the statement handle again, a
while statement loops through the results using the
fetch() function to fetch data one row at a time from the
results. Within the while statement block, we’re printing
the values with HTML tags. When it’s finished, we close the statement
handle and the connection.
The output of this script is a line for each bird based on the
search criteria in the birds table. In this simple example,
only a few of the many PHP functions for MySQL are used to get and
display data. These snippets are shown here together within a very basic
web page:
<html><body>
<?php$search_parameter=$_REQUEST['birdname'];$host='localhost';$user='public_api';$pw='pwd_123';$db='rookery';$connect=newmysqli($host,$user,$pw,$db);if(mysqli_connect_errno()){printf("Connect failed: %s\n",mysqli_connect_error());exit();}?>
<h3>Birds -<?php echo $search_parameter ?></h3><p>Below is a list of birds in our database based on your search criteria:</p>
<?php$sql_stmnt="SELECT common_name, scientific_nameFROM birdsWHERE common_name LIKE ?";$sth=$connect->prepare($sql_stmnt);$search_parameter="%".$search_parameter."%";$sth->bind_param('s',$search_parameter);$sth->execute();$sth->bind_result($common_name,$scientific_name);while($sth->fetch()){"$common_name- <i>$scientific_name</i> <br/>";}$sth->close();$connect->close();?>
</body></html>
This example is almost the same as the two major sections shown previously. We’ve added opening and closing HTML tags and some with text in between the two PHP code snippets. We also positioned a couple of the lines in different places, but it flows the same. Here’s the text returned to the web user when searching for Avocet birds:
Birds - "Avocet" Below is a list of birds in our database based on your search criteria: Pied Avocet - Recurvirostra avosetta Red-necked Avocet - Recurvirostra novaehollandiae Andean Avocet - Recurvirostra andina American Avocet - Recurvirostra americana Mountain Avocetbill - Opisthoprora euryptera
If you would like to learn more about using the PHP API mysqli,
there’s an extensive manual on the PHP site, including a MySQL Improved Extension
manual. You might also like to read Robin Nixon’s book, Learning
PHP, MySQL & Javascript (O’Reilly) to learn more
about using PHP within web pages to access MySQL.
To use Python with MySQL, you can you use the MySQL Connector/Python. It’s written in Python and needs only the Python libraries to function. It doesn’t require any Python modules besides what’s already part of the Python standard library. Nor does it require the MySQL client library.
The first thing you will need to do is install the MySQL Connector/Python on your server. You can do this by using an installation utility like yum on a Linux system. Python and its libraries are probably already installed on your server, but you can try installing them at the same time to be sure. Execute this from the command line:
yum install python python-libs mysql-connector-python
This section uses Version 2 of Python, which is still the most common one installed on Linux and Mac systems at the time of this writing. Version 3 is becoming popular, and requires minor syntax changes, but you can read about it elsewhere. If you want to use Version 3, and perhaps another library for connecting Python to MySQL, you will probably need only minor changes to the code shown in this section.
Once you have the connector installed on your server, you can then write and run a Python program to connect to MySQL and query databases. For the example in this section, suppose the database administrator in charge of managing MySQL users has asked us to write a program that would give him a list of user accounts and privileges for each. Let’s go through a very simple program to do this.
To query a database with Python, we will need to establish a connection with MySQL. Here is the beginning part of a Python program to do this:
#!/usr/bin/pythonimportmysql.connectorconfig={'user':'admin_granter','password':'avocet_123','host':'localhost','database':'rookery'}cnx=mysql.connector.connect(**config)cur=cnx.cursor(buffered=True)
The first line is the required line invoking Python. Next we
import mysql.connector, the MySQL Connector/Python. We then
create a hash to store the login information we will need for connecting
to MySQL. We’re using the admin_granter@localhost user account because it
has the privileges to execute the SHOW GRANTS statement and
to query the mysql database, which contains user account
information. We created this user in User Account to Grant Privileges.
The final pair of lines of the previous code snippet establishes
the connection to MySQL. The first uses the connect() call for the MySQL
Connector/Python using the values in the config hash,
loading its results into the cnx variable. The second
creates a cursor object (cur) to use for executing queries
on the database.
Because there is no SHOW USERS statement, we’ll have to query the mysql database to
select a list of user accounts from the user table. To do
this, we’ll first create a variable to store the SELECT
statement we want to execute. Then we’ll use the execute() call to execute it. Here’s how
this part of the program would look:
sql_stmnt=("SELECT DISTINCT User, Host FROM mysql.db ""WHERE Db IN('rookery','birdwatchers') ""ORDER BY User, Host")cur.execute(sql_stmnt)
So as to fit the SELECT statement on the page, we’ve
broken it onto multiple lines. We pass that variable to the
execute() function to execute the SQL statement. We’re now
ready to fetch the rows, parse the fields from the results, and display
them:
forrowincur.fetchall():user_name=row[0]host_address=row[1]user_account="'"+user_name+"'@'"+host_address+"'""%s@%s"%(user_name,host_address)cur.close()cnx.close()
We’re using a for statement here to loop through the
results of a fetchall() call for the cur
cursor object. It takes the values from each row fetched and stores it
in an array we named row. Within the statement block of the
for statement, we extract each array element and store the
values temporarily in string variables, in user_name and
host_address. Then we assemble them with some text for
nicer formatting and store them in a variable we named
user_account. Its contents will look like
lena_stankoska@localhost.
We end this program by displaying the user_account
values to the administrator, and then closing the cursor object and the
connection to MySQL.
It’s easier to discuss a program by breaking it into its components as we’ve just done, but it can be confusing to understand how it all comes together. The following listing combines the preceding snippets, but with some additions that make it a bit more elaborate:
#!/usr/bin/pythonimportreimportmysql.connector# connect to mysqlconfig={'user':'admin_granter','password':'avocet_123','host':'localhost','database':'rookery'}cnx=mysql.connector.connect(**config)cur=cnx.cursor(buffered=True)# query mysql database for list of user accountssql_stmnt="SELECT DISTINCT User, Host FROM mysql.db "sql_stmnt+="WHERE Db IN('rookery','birdwatchers') "sql_stmnt+="ORDER BY User, Host"cur.execute(sql_stmnt)# loop through list of user accountsforuser_accountsincur.fetchall():user_name=user_accounts[0]host_address=user_accounts[1]user_account="'"+user_name+"'@'"+host_address+"'"# display user account heading"\nUser Account:%s@%s"%(user_name,host_address)"------------------------------------------"# query mysql for grants for user accountsql_stmnt="SHOW GRANTS FOR "+user_accountcur.execute(sql_stmnt)# loop through grant entries for user accountforgrantsincur.fetchall():# skip 'usage' entryifre.search('USAGE',grants[0]):continue# extract name of database and tabledbtb=re.search('ON\s(.*)\.+?(.+?)\sTO',grants[0])db=dbtb.group(1)tb=dbtb.group(2)# change wildcard for tables to 'all'ifre.search('\*',tb):tb="all"# display database and table name for privileges"database:%s; table:%s"%(db,tb)# extract and display privileges for user account# for database and tableprivs=re.search('GRANT\s(.+?)\sON',grants[0])"privileges:%s\n"%(privs.group(1))cur.close()cnx.close()
This program does much more than the previous snippets. As a result, I’ve annotated it at various points to help you understand it. Still, let’s go through the key points, especially the additions.
First, the program gets a list of user accounts, storing them in
an array named user_accounts. Using a for
statement, it goes through each row of user_accounts to
extract each user_account. For each, it prints a heading to
display the user account to the administrator. This part is similar to
the previous excerpts.
We then put a new SQL statement, SHOW GRANTS, in
sql_stmnt for each user_account. We execute
and then use another for statement to go through the
results of a fetchall(), which we store in a variable
we’ve named grants. If a row from grants
contains the word USAGE, we skip displaying that. We then
parse out the database and table name, store them in variables named
db and tb, and display them. The last pair of
lines extracts the list of privileges and displays them.
Some of the results of running this Python program on my system follow:
User Account: lena_stankoska@localhost ------------------------------------------ database: `rookery`; table: all privileges: SELECT, INSERT, UPDATE, DELETE database: `birdwatchers`; table: all privileges: SELECT, INSERT, UPDATE User Account: public_api@localhost ------------------------------------------ database: `birdwatchers`; table: all privileges: SELECT database: `rookery`; table: all privileges: SELECT
This is a nice way for the administrator to get a list of users and see what privileges they have for particular databases and tables, especially because there isn’t a built-in function to do what we want.
If you would like more information on MySQL Connector/Python, there’s an extensive manual on MySQL’s site, including a MySQL Connector/Python Developer Guide. You might also like to read Mark Lutz’s book, Learning Python (O’Reilly).
The Ruby language has become very popular and can be used to create programs to access a database. There are two MySQL modules for Ruby. The MySQL/Ruby module is built on the MySQL C API. As such, it has the same functions in Ruby as the C API. This is a nice feature if you already know the C API. The other module is the Ruby/MySQL module—this pairing and reverse pairing of the names can be confusing. The Ruby/MySQL module is written in Ruby and is included in Ruby on Rails. For the examples in this section, we will use the former, the MySQL/Ruby module.
Before writing a Ruby program to interface with MySQL, let’s install the MySQL/Ruby module, which uses the same functions as the MySQL C API. You can do this by using an installation utility like yum on a Linux system. Execute the following from the command line, while logged in as the root or some other administrative filesystem user:
yum install ruby ruby-mysql
If you can’t use yum on your server, you can check MySQL’s website to download Ruby modules and to find instructions on installing them.
Once you have Ruby and the MySQL/Ruby module installed on your
server, you can then write and run a Ruby program to connect to MySQL
and query the databases. Let’s go through a very simple program to do
this. For this example program, we’ll use the admin_backup@localhost user account. We created
this user account in Username and Host. We
will be selecting and inserting data in a database we’ll call
server_admin. One of the tables in this database will be
backup_policies. We’ll then insert data into this table
related to our backup policies as a reference. We’ll log information
about the backups, and other server information in that database.
To prepare for the program we’re about to write, let’s create the
server_admin database and the tables we need for it. Create
the database and the backup_policies table by executing the
following SQL statements:
CREATEDATABASEserver_admin;CREATETABLEbackup_policies(policy_idINTAUTO_INCREMENTKEY,backup_nameVARCHAR(100),file_format_prefixVARCHAR(25),frequencyENUM('daily','weekly'),daysENUM('first','every'),start_timeTIME,secureTINYINTDEFAULT0,locationENUM('on-site','off-site','both'),tables_includeVARCHAR(255));
Now that we’ve created the backup_policies table,
let’s insert data in it related to our backup policies shown in Table 14-2. We’ll execute
the following INSERT statement:
INSERTINTObackup_policies(backup_name,file_format_prefix,frequency,days,start_time,secure,location,tables_include)VALUES('rookery - full back-up','rookery-',2,1,'08:00:00',0,2,"all tables"),('rookery - bird classification','rookery-class-',1,2,'09:00:00',0,1,"birds, bird_families, bird_orders"),('birdwatchers - full back-up','birdwatchers-',2,1,'08:30:00',1,2,"all tables"),('birdwatchers - people','birdwatchers-people-',1,2,'09:30:00',1,1,"humans, birder_families, birding_events_children"),('birdwatchers - activities','birdwatchers-activities-',1,2,'10:00:00',0,1,"bird_sightings, birding_events, bird_identification_tests,prize_winners, surveys, survey_answers, survey_questions");
In addition, we will need another table in the
server_admin database. We’ll call it
backup_reports and store reports in it that will be
generated by the program that we’ll create. The SQL statement to create
this table is as follows:
CREATETABLEbackup_reports(report_idINTAUTO_INCREMENTKEY,report_dateDATETIME,admin_nameVARCHAR(100),reportTEXT);
This is a simple table containing a key, the date of the report,
the name of the administrator generating the report, and a
TEXT column to store the report, which will be generated by
the program we’ll create in this section. Because we will be using the
admin_backup user account, we will
need to give that account user privileges to access the
server_admin database. We can do that by executing this SQL
statement:
GRANTSELECT,INSERTONserver_admin.*TO'admin_backup'@'localhost';
We’re now ready to create the program for the backup administrator.
To query a database with Ruby, we will need to establish a connection with MySQL. Here’s the beginning part of a Ruby program to do this:
require'mysql'user='admin_backup'password='its_password_123'host='localhost'database='server_admin'begincon=Mysql.newhost,user,password,database# Database Queries Here# ...rescueMysql::Error=>eputse.errnoputse.errorensurecon.closeifconend
This excerpt of a Ruby program shows how to connect and disconnect from MySQL. The first line is the usual line to invoke Ruby. The next line calls the MySQL module. Then there is a list of variables that we’ll use for connecting to the server. The names of these variables are not important.
This is followed by a begin statement that will
include all of the interactions with the database server. The first line
establishes a new connection to MySQL. It includes the variables we
created for connecting to the server. These variables, or values for
these parameters, must be in the order shown here.
Once you have successfully connected to the database server, you can execute SQL statements. I left out the lines for querying the database to keep this part simple. We’ll look at that in a bit.
If the program is not successful in connecting to MySQL, the
rescue block will handle the errors and display them to the
user using puts. Regardless of whether the processing of
the queries is successful, the ensure will make sure that
the connection to MySQL is closed at the end of the program.
In the previous section, we examined the process for starting a simple Ruby program and connecting to a MySQL server, and looked at how to disconnect from it. Let’s now see how to query a database while connected to MySQL or MariaDB with the Ruby API.
We’ll do a very simple query to get a list of Avocet birds from
the birds table. To do this, we’ll first create a variable
to store the SELECT statement we want to execute. Then
we’ll execute it with a query() call. Here’s how that part of the program would look:
sql="SELECT common_name, scientific_nameFROM birdsWHERE common_name LIKE '%Avocet%'"rows=con.query(sql)rows.eachdo|row|common_name=row[0]scientific_name=row[1]putscommon_name+' - '+scientific_nameend
After the query(), you can see that we’re using an
each statement to go through each of the rows
of the results, storing each row in an array called row.
Then we’re temporarily storing each element of the row
array in the common_name and scientific_name
variables. We’re using puts to display each variable with a
hyphen between them and a newline at the end.
Although it’s easier to discuss the components of a program in separate
pieces, it can be confusing to see how they come together. A complete
Ruby program follows that uses the MySQL/Ruby module. This program has a
very different purpose from the snippets we showed earlier. It will
check the backup directory for backup files in accordance with our
backup policy (this task was discussed in Developing a Backup Policy). The program will display to
the administrator a list of backup files for the past several days. It
will also store a report of its findings in the
backup_reports table in the server_admin
database in MySQL:
#!/usr/bin/rubyrequire'mysql'# create date variablestime=Time.newyr=time.strftime("%Y")mn=time.strftime("%m")mon=time.strftime("%b")dy=time.strftime("%d")# variables for connecting to mysqluser='admin_backup'password='its_password_123'host='localhost'database='server_admin'# create other initial variablesbu_dir="/data/backup/rookery/"admin_name="Lena Stankoska"bu_report="Back-Up File Report\n"bu_report+="-----------------------------------------------------\n"putsbu_reportit=0num=7begin# connect to mysql and query database for back-up policiescon=Mysql.newhost,user,password,databasesql="SELECT policy_id, backup_name, frequency,tables_include, file_format_prefixFROM backup_policies"policies=con.query(sql)policies.each_hashdo|policy|# loop through each row, each policy# capture fields in variablesbu_name=policy['backup_name']bu_pre=policy['file_format_prefix']bu_freq=policy['frequency']# assemble header for policybu_header="\n"+bu_name+" (performed "+bu_freq+")\n"bu_header+="("+bu_pre+"yyyy-mmm-dd.sql)\n"bu_header+="-----------------------------------------------------\n"bu_report+=bu_headerputsbu_headeruntilit>numdo# iterate through 7 back-up files (i.e., days)bk_day=dy.to_i-it# assemble backup filenamebu_file_suffix=yr+"-"+mon.downcase+"-"+bk_day.to_s+".sql"bu_file=bu_pre+bu_file_suffixbu_path_file=bu_dir+bu_file# get info. on back-up file if it existsifFile::exists?(bu_path_file)bu_size=File.size?(bu_path_file)bu_size_human=bu_size/1024bu_file_entry=bu_file+" ("+bu_size_human.to_s+"k)"bu_report+=bu_file_entry+"\n"putsbu_file_entryendit+=1endit=0endendbegin# insert report text accumulated in backup_reports tablecon=Mysql.newhost,user,password,databasesql="INSERT INTO backup_reports(report_date, admin_name, report)VALUES (NOW(), ?, ?)"prep_sql=con.preparesqlprep_sql.execute(admin_name,bu_report)rescueMysql::Error=>eputse.errnoputse.errorensurecon.closeifconend
This Ruby program has comments throughout it to explain the various sections of the code. However, I’d like to summarize it and highlight a few parts.
First, we get the current date to create variables that we’ll use to determine the name of back-up files. These are based on the backup policies shown in Table 14-2.
Skipping ahead, you can see that we create a variable,
bu_report, for storing text for a report. This report is
displayed on the screen for the user as it goes along and will in the
end be inserted into the backup_reports table.
Going back to the first begin block, we execute a
SELECT to get a list of backup policies from the
backup_policies table. This table includes the file format
prefix (e.g., rookery-class-) used to
make each backup file. This is followed by the date format that each
filename uses (yyyy-mm-dd.sql). We
store these policies in a hash named policies. Using an
each statement, we go through each policy to
form a header for each and then execute an until statement
to check for the backup files on the server for the past week. For each
backup file found, the bu_report is appended with the name
of the file and its size.
The next begin block executes an INSERT
statement to save the contents of bu_report, along with the
date and the administrator’s name in the backup_reports
table. The results for one sample row in that table follow:
*************************** 62. row ***************************
report_id: 62
report_date: 2014-10-20 14:32:37
admin_name: Lena Stankoska
report: Back-Up File Report
-----------------------------------------------------
rookery - full back-up (performed weekly)
(rookery-yyyy-mmm-dd.sql)
-----------------------------------------------------
rookery-2014-oct-20.sql (7476k)
rookery-2014-oct-13.sql (7474k)
rookery - bird classification (performed daily)
(rookery-class-yyyy-mmm-dd.sql)
-----------------------------------------------------
rookery-class-2014-oct-20.sql (2156k)
rookery-class-2014-oct-19.sql (2156k)
rookery-class-2014-oct-18.sql (2156k)
rookery-class-2014-oct-17.sql (2154k)
rookery-class-2014-oct-16.sql (2154k)
rookery-class-2014-oct-15.sql (2154k)
rookery-class-2014-oct-14.sql (2154k)
rookery-class-2014-oct-13.sql (2154k)
birdwatchers - full back-up (performed weekly)
(birdwatchers-yyyy-mmm-dd.sql)
-----------------------------------------------------
birdwatchers-2014-oct-20.sql (28k)
birdwatchers-2014-oct-13.sql (24k)
birdwatchers - people (performed daily)
(birdwatchers-people-yyyy-mmm-dd.sql)
-----------------------------------------------------
birdwatchers-people-2014-oct-20.sql (6k)
birdwatchers-people-2014-oct-19.sql (6k)
birdwatchers-people-2014-oct-18.sql (6k)
birdwatchers-people-2014-oct-17.sql (4k)
birdwatchers-people-2014-oct-16.sql (4k)
birdwatchers-people-2014-oct-15.sql (4k)
birdwatchers-people-2014-oct-14.sql (4k)
birdwatchers-people-2014-oct-13.sql (4k)
birdwatchers - activities (performed daily)
(birdwatchers-activities-yyyy-mmm-dd.sql)
-----------------------------------------------------
birdwatchers-activities-2014-oct-20.sql (15k)
birdwatchers-activities-2014-oct-19.sql (15k)
birdwatchers-activities-2014-oct-18.sql (15k)
birdwatchers-activities-2014-oct-17.sql (15k)
birdwatchers-activities-2014-oct-16.sql (15k)
birdwatchers-activities-2014-oct-15.sql (13k)
birdwatchers-activities-2014-oct-14.sql (13k)
birdwatchers-activities-2014-oct-13.sql (13k)If you would like to learn more about using Ruby with MySQL, there’s a manual provided by Tomita Masahiro, the creator of the MySQL Ruby module. You might also find Learning Ruby (O’Reilly) by Michael Fitzgerald useful.
An API program that accesses MySQL or MariaDB and is available to the public, on the Web or from some other public access point, could be used to attack the database server. Someone could maliciously manipulate the data given to the web page containing a script, or the application that sends data to the server through an API. Specifically, a hacker could embed an SQL statement in the data to be injected into the database. This is known as SQL injection. The purpose could be to destroy data, retrieve sensitive or valuable information, or create a user with all privileges and then access the server to steal information.
The vulnerability is related to the fact that string values are contained in quotes. To inject SQL into a string value, a hacker just needs to close the open quote, add a semicolon, and then start a new SQL statement. With numeric values, one can add an extra clause without a quote and get at data.
For an example of an SQL injection, let’s look the SQL statement
used in the PHP API section, but without a placeholder. Suppose we
embedded the $search_parameter variable inside the SQL
statement like this:
$sql_stmnt="SELECT common_name, scientific_nameFROM birdsWHERE common_name LIKE '%$search_parameter%'"
Instead of entering a common name of a bird, suppose that a hacker entered the following when using the API program, including the single quotes:
'; GRANT ALL PRIVILEGES ON *.* TO 'bad_guy'@'%'; '
That will change our SQL statement to read like this:
SELECTcommon_name,scientific_nameFROMbirdsWHEREcommon_nameLIKE'%';GRANTALLPRIVILEGESON*.*TO'bad_guy'@'%';'%';
This results in three SQL statements instead of just the one
intended. The hacker would receive a blank list of birds for the first.
More important, based on the second SQL statement, the system might create
for him a user account with all privileges, accessible from anywhere and
without a password. If the user account within the API program has GRANT TO and ALL privileges
for all of the databases, the bad_guy user account would be
created and have unrestricted access and privileges. The last bit of the
malicious SQL statement would just return an error because it’s incomplete
and doesn’t contain an SQL statement.
One method of preventing SQL injection with a MySQL API is to use placeholders instead of literal values. We used these in previous examples in this chapter. This method will isolate the data that will be added to the SQL statement. It does this by escaping single and double quotes. It may not seem like much, but it’s fairly effective.
The previous SQL statements intended by the hacker will look instead as follows if placeholders are used:
SELECTcommon_name,scientific_nameFROMbirdsWHEREcommon_nameLIKE'%\';GRANTALLPRIVILEGESON*.*TO\'bad_guy\'@\'%\';%';
Because the quote marks the hacker entered are escaped, MySQL will
treat them as literal values and won’t see them as the end of string
values. Therefore, it won’t start a new SQL statement when it encounters
the semicolons he entered. It won’t return the names of any birds, because
the value won’t equal any rows in the table. More important, a
bad_guy user won’t be created.
An API is very useful to create programs for users who don’t know how to use MySQL, or users for whom you don’t want to access MySQL directly. It provides you a much higher level of security and control over users, especially unknown users accessing your databases through the Web. Additionally, when MySQL doesn’t have a function to get information you want from a database, you can write an API program to accomplish what you want and to supplement MySQL. As a result, the APIs are very powerful tools for customizing MySQL and MariaDB.
The API programs we reviewed in this chapter select data from a database, and some insert or update data in a database. Some were very simple and some were much more involved. We did very little error checking and performed only simple tasks. Despite how basic and minimal some of the examples were, they should be sufficient to give you an idea of how to write an API program to connect with MySQL and MariaDB and to query a database. The rest is a matter of knowing the related programming language and MySQL well, and using the many API functions available to make better applications. To that end, at the end of each section, you were given suggestions on learning more about each API.
For the exercises in this chapter, use the API for whichever language you prefer. If you have no preference, use PHP for the exercises. It’s the most popular and probably the easiest to learn.
Write an API program that connects to MySQL and queries the
rookery database. Have the program execute a
SELECT statement to get a list of birds. Use a
JOIN to access the birds,
bird_families, and bird_orders tables to
select the bird_id, common_name, and
scientific_name from the birds table, as
well as the scientific_name from both the
bird_families and bird_orders tables. Joins
were covered in Joining Tables. Use the
LIMIT clause to limit the results to 100 birds. When
you’re finished, execute the program from the command line, or a web
browser if using the PHP API.
Write an API program that accepts data from the user of the
program. It may be from the command line or from a web browser, if
using the PHP API. Design the program to connect to MySQL and the
birdwatchers database. Have it execute an
INSERT statement to add data given by the user to the
humans table, just data for the
formal_title, name_first, and
name_last columns. Set the value for
join_date by using the CURDATE() function,
and set the membership_type column to
basic.
After you write this program, use it to enter the names of a few fictitious people. Then log into MySQL with the mysql client to verify that it worked.
Log into MySQL and use the CREATE TABLE statement
to create a table named backup_logs in the
server_admin database (the CREATE TABLE
statement was covered in Creating Tables). We created the
server_admin database at the beginning of this chapter.
Design the backup_logs table however you want, but be
sure to include columns to record the date and time, and the name of a
backup file.
Use the GRANTS statement to give the
admin_backup user account the INSERT and
SELECT privileges (at a minimum) for this new table (this
was covered extensively in SQL Privileges).
An example of a backup shell script was included in Creating Backup Scripts. Try writing an API program that can be executed from the command line, not from a web browser, to perform the same tasks as the shell script shown in that section. Have it call the mysqldump utility—don’t try to develop your own backup utility. When you’re finished, test the program to see whether it makes a backup file and gives it the correct name based on the data. This exercise may be beyond your abilities, though. If it is, skip this exercise and try again in the future when you’re much more experienced in using the API.
After you’ve verified that this API program makes backups
correctly, have it connect to MySQL to record that it has run
successfully. Use the INSERT statement to insert a row
with the date the program ran and the name of the backup file it
created. When finished, run the program again and check the table in
MySQL to make sure it logged the information.
Once you’re sure the API program works properly, add a line to cron or another scheduling program to automatically execute the backup program you wrote. Set it to run some time soon so you can verify it works with cron. You can remove it from cron when you’re finished.
Write an API program that will select a list of bird families to display to the user. Devise a way for the user to select a bird family from the results to get a list of birds in the family. If you’re using an API program like PHP that may be used in a web browser, create links for the bird families to take them to the same API program to list the birds in the family selected.
If you’re writing an API program that will be executed from the
command line, provide the user with the family_id next to
the name of each bird family. Instruct the user to run the program
again, but with the family_id entered after the command
to get a list of the birds for a family chosen. Create the program in
such a way that if no family_id is entered, the user gets
a list of families, but if a family_id is entered, the
user gets a list of birds in the family. Try running the program to
make sure it works properly.