Learning MySQL and MariaDB (2015)
Part V. Administration and Beyond
Chapter 16. Application Programming Interfaces
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.
Creating API User Accounts
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:
CREATE USER 'public_api'@'localhost'
IDENTIFIED BY 'pwd_123';
GRANT SELECT
ON rookery.*
TO 'public_api'@'localhost';
GRANT SELECT
ON birdwatchers.*
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:
CREATE USER 'admin_members'@'localhost'
IDENTIFIED BY 'doc_killdeer_123';
GRANT SELECT, UPDATE, DELETE
ON birdwatchers.*
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.
C API
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.
Connecting to MySQL
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"
int main(int argc, char *argv[ ])
{
MYSQL *mysql;
MYSQL_RES *result;
MYSQL_ROW row;
...
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");
return 1;
}
...
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));
return 1;
}
...
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.
Querying MySQL
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);
return 0;
}
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 printfwith 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.
Complete Minimal C API Program
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"
int main(int argc, char *argv[ ])
{
MYSQL *mysql;
MYSQL_RES *result;
MYSQL_ROW row;
if(mysql_init(mysql) == NULL) {
fprintf(stderr, "Cannot Initialize MySQL");
return 1;
}
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));
return 1;
}
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);
return 0;
}
Compiling with C Includes
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.c
gcc -o mysql_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.
Perl DBI
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.
Installing
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
Connecting to 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 -w
use strict;
use DBI;
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.
Querying MySQL
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.
Selecting data
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_expiration
FROM humans
WHERE 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())
{
print "$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();
foreach my $member (@$members){
my ($human_id, $full_name, $membership_expiration) = @$member;
print "$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().
Updating data
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 humans
SET 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.
A Full Example with Perl DBI
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;
use DBI;
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_expiration
FROM humans
WHERE name_last LIKE ?";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute("%$search_parameter%");
my $members = $sth->fetchall_arrayref();
$sth->finish();
print "List of Members - '$search_parameter' \n";
foreach my $member (@$members){
my ($human_id, $full_name, $membership_expiration) = @$member;
print "$full_name ($human_id) - $membership_expiration \n";
}
}
if($human_id && $membership_expiration) {
$sth = $dbh->prepare($sql_stmnt);
$sql_stmnt = "UPDATE humans
SET membership_expiration = ?
WHERE human_id = ?";
$sth = $dbh->prepare($sql_stmnt);
my ($rc) = $sth->execute($email_address,$human_id);
$sth->finish();
if($rc) {
print "Membership Expiration Changed. \n";
}
else {
print "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-22
The program can be run again with a new expiration date for the member like so:
member_adjust_expiration.plx Hollar 4 2015-06-30
Notice 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.
More Information
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.
PHP API
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.
Installing and Configuring
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.
Connecting to MySQL
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 = new mysqli($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.
Querying MySQL
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_name
FROM birds
WHERE 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() ) {
print "$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.
A WEB FORM
A web user wouldn’t normally enter a variable name and a search value at the end of a web address in a web browser. Instead, this web page we’re building would be preceded by another web page containing an HTML form for the user to enter a search parameter. Here’s how that web form would look:
<h3>Search Birds Database</h3>
<form action="birds.html" method="post">
<p>Enter a parameter by which to search
the common names of birds in our database:</p>
<input type="text" name="birdname" />
<input type="submit" />
</form>
This form on the preceding page calls the web page we’re writing, passing the search parameter to it in the proper format.
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 whilestatement 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 = new mysqli($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_name
FROM birds
WHERE 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()) {
print "$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
More Information
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.
Python
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.
Installing
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
NOTE
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.
Connecting to MySQL
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/python
import mysql.connector
config = {
'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.
Querying MySQL
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:
for row incur.fetchall() :
user_name = row[0]
host_address = row[1]
user_account = "'" + user_name + "'@'" + host_address + "'"
print "%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.
Sample Python Program
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/python
import re
import mysql.connector
# connect to mysql
config = {
'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 accounts
sql_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 accounts
for user_accounts incur.fetchall() :
user_name = user_accounts[0]
host_address = user_accounts[1]
user_account = "'" + user_name + "'@'" + host_address + "'"
# display user account heading
print "\nUser Account: %s@%s" % (user_name, host_address)
print "------------------------------------------"
# query mysql for grants for user account
sql_stmnt = "SHOW GRANTS FOR " + user_account
cur.execute(sql_stmnt)
# loop through grant entries for user account
for grants incur.fetchall() :
# skip 'usage' entry
if re.search('USAGE', grants[0]) :
continue
# extract name of database and table
dbtb = re.search('ON\s(.*)\.+?(.+?)\sTO', grants[0])
db = dbtb.group(1)
tb = dbtb.group(2)
# change wildcard for tables to 'all'
if re.search('\*', tb) :
tb = "all"
# display database and table name for privileges
print "database: %s; table: %s" % (db,tb)
# extract and display privileges for user account
# for database and table
privs = re.search('GRANT\s(.+?)\sON', grants[0])
print "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.
More Information
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).
Ruby API
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.
Installing and Preparing MySQL/Ruby
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:
CREATE DATABASE server_admin;
CREATE TABLE backup_policies
(policy_id INT AUTO_INCREMENT KEY,
backup_name VARCHAR(100),
file_format_prefix VARCHAR(25),
frequency ENUM('daily','weekly'),
days ENUM('first','every'), start_time TIME,
secure TINYINT DEFAULT 0,
location ENUM('on-site','off-site','both'),
tables_include VARCHAR(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:
INSERT INTO backup_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:
CREATE TABLE backup_reports
(report_id INT AUTO_INCREMENT KEY,
report_date DATETIME,
admin_name VARCHAR(100),
report TEXT);
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:
GRANT SELECT, INSERT ON server_admin.*
TO 'admin_backup'@'localhost';
We’re now ready to create the program for the backup administrator.
Connecting to MySQL
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'
begin
con = Mysql.new host, user, password, database
# Database Queries Here
# ...
rescue Mysql::Error => e
puts e.errno
puts e.error
ensure
con.close if con
end
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.
Querying MySQL
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_name
FROM birds
WHERE common_name LIKE '%Avocet%'"
rows = con.query(sql)
rows.each do |row|
common_name = row[0]
scientific_name = row[1]
puts common_name + ' - ' + scientific_name
end
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.
Sample MySQL/Ruby Program
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 thebackup_reports table in the server_admin database in MySQL:
#!/usr/bin/ruby
require 'mysql'
# create date variables
time = Time.new
yr = time.strftime("%Y")
mn = time.strftime("%m")
mon = time.strftime("%b")
dy = time.strftime("%d")
# variables for connecting to mysql
user = 'admin_backup'
password = 'its_password_123'
host = 'localhost'
database = 'server_admin'
# create other initial variables
bu_dir = "/data/backup/rookery/"
admin_name = "Lena Stankoska"
bu_report = "Back-Up File Report\n"
bu_report += "-----------------------------------------------------\n"
puts bu_report
it = 0
num = 7
begin
# connect to mysql and query database for back-up policies
con = Mysql.new host, user, password, database
sql = "SELECT policy_id, backup_name, frequency,
tables_include, file_format_prefix
FROM backup_policies"
policies = con.query(sql)
policies.each_hash do |policy| # loop through each row, each policy
# capture fields in variables
bu_name = policy['backup_name']
bu_pre = policy['file_format_prefix']
bu_freq = policy['frequency']
# assemble header for policy
bu_header = "\n" + bu_name + " (performed " + bu_freq + ")\n"
bu_header += "(" + bu_pre + "yyyy-mmm-dd.sql) \n"
bu_header += "-----------------------------------------------------\n"
bu_report += bu_header
puts bu_header
until it > num do # iterate through 7 back-up files (i.e., days)
bk_day = dy.to_i - it
# assemble backup filename
bu_file_suffix = yr + "-" + mon.downcase + "-" + bk_day.to_s + ".sql"
bu_file = bu_pre + bu_file_suffix
bu_path_file = bu_dir + bu_file
# get info. on back-up file if it exists
if File::exists?(bu_path_file)
bu_size = File.size?(bu_path_file)
bu_size_human = bu_size / 1024
bu_file_entry = bu_file + " (" + bu_size_human.to_s + "k)"
bu_report += bu_file_entry + "\n"
puts bu_file_entry
end
it +=1
end
it = 0
end
end
begin
# insert report text accumulated in backup_reports table
con = Mysql.new host, user, password, database
sql = "INSERT INTO backup_reports
(report_date, admin_name, report)
VALUES (NOW(), ?, ?)"
prep_sql = con.prepare sql
prep_sql.execute(admin_name,bu_report)
rescue Mysql::Error => e
puts e.errno
puts e.error
ensure
con.close if con
end
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)
More Information
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.
SQL Injection
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_name
FROM birds
WHERE 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:
SELECT common_name, scientific_name FROM birds
WHERE common_name LIKE '%';
GRANT ALL PRIVILEGES ON *.* 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:
SELECT common_name, scientific_name FROM birds
WHERE common_name LIKE '%\';
GRANT ALL PRIVILEGES ON *.* 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.
Summary
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.
Exercises
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.
1. 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 thebirds 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.
2. 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.
3. 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.
4. 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.