Apache Web-Serving With Mac OS X, Part 5
Pages: 1, 2, 3

Hello, MySQL!

If the above went smoothly, it's time to make a quick PHP script to make sure database communication is possible. Copy the following code into your favorite text editor (like BBEdit), and save the file as test.php within a Web site directory (either /Library/WebServer/Documents/ or /Users/morbus/Sites, for example).

<?
   print "<pre>";

   // log into our local server using the MySQL root user.
   $dbh = mysql_connect( "localhost", "root", "" );


   // select the 'test' database created during installation.
   mysql_select_db( "test" ) or die ( mysql_error() . "\n" );
   print "Connection to the database has been established.\n";


   // create a simplistic table.
   $table = "CREATE table wisdom (
             id int(4) PRIMARY KEY AUTO_INCREMENT,
             wisdom char(255), author char(125) );";

   $response = mysql_query( $table, $dbh );
   if ($response) { print "The table was created correctly!\n"; }
   else { print mysql_error () . "\n"; }


   // now, we'll add some data to our newly created table.
   // to add different wisdom, just change the 'values'.
   $insert_data = "INSERT into wisdom ( wisdom, author ) 
                   values ( 'Must... remain... awake!', 'Morbus' );";

   $response = mysql_query( $insert_data, $dbh );
   if ($response) { print "The data was inserted correctly!\n"; }
   else { print mysql_error () . "\n"; }


   // and read it back for printing purposes.
   $get_table_data = "SELECT * FROM wisdom;";
   $response = mysql_query( $get_table_data, $dbh );
   if ($response) { print "We successfully got all the table data.\n"; }
   else { print mysql_error () . "\n"; }


   // now print it out for the user.
   while ( $one_line_of_data = mysql_fetch_array( $response ) ) {
       extract ( $one_line_of_data );
       print "#$id: $author sez: \"$wisdom\"\n"; 
   }

   print "</pre>";
?>

Note: Again, we're not going to explore the syntax of the PHP script, or the SQL commands that are used. Suffice it to say that this script will create a table in the MySQL 'test' database, add some data, and then spit back the total contents of the 'wisdom' table. If you need a brush-up on PHP or MySQL, be sure to check out ONLamp.com.

After you've saved the file, load it in your Web browser. I saved my copy in /Users/morbus/Sites/test.php, so I loaded http://127.0.0.1/~morbus/test.php in my browser. After the first run, this is what I saw:

    Connection to the database has been established.
    The table was created correctly!
    The data was inserted correctly!
    We successfully got all the table data.
    #1: Morbus sez: "Must... remain... awake!"

If I continue running the script, changing the INSERT line each time, my output will start to look like:

    Connection to the database has been established.
    Table 'wisdom' already exists
    The data was inserted correctly!
    We successfully got all the table data.
    #1: Morbus sez: "Must... remain... awake!"
    #2: Morbus sez: "Sleeping makes Derrick angry!"
    #3: Morbus sez: "And I'm 23 minutes away from 3 o'clock!"

The above output certifies that our PHP-to-MySQL communication is working perfectly. With 23 minutes to spare before the boss and his goons come, we've got just enough time to chow down the last remnants of our ketchup potato chips and wash them down with a swig of Moxie.

Two minor additions

Previously in the Series Apache Web-Serving with Mac OS X, Part 6 Apache Web-Serving with Mac OS X, Part 4 Apache Web-Serving with Mac OS X: Part 3 Apache Web-Serving with Mac OS X: Part 2 Apache Web-Serving with Mac OS X: Part 1

When we turn on our Web server (through the Sharing preference panel), OS X will happily restart Apache if our machine ever needs a reboot. Out of the box, MySQL doesn't restart automatically. Thankfully, there's a double-clickable solution, again from Marc Liyanage. Upon installing this StartupItem, MySQL will be at your beck and call after every reboot.

With the above instructions, MySQL is woefully unsecured. Anyone can become the administrative MySQL user and wreak havoc with our data. This may not be an issue if you're using MySQL on a development machine, but publicly accessible servers need protection. Much like OS X has a root user with ultimate control over the machine, MySQL also has a root user that has ultimate control over the database server.

By default, the MySQL root user has no password assigned to it. If you take a gander back at our PHP script, you'll see that we connect to our database with that field blank:

   // log into our local server using the MySQL root user.
   $dbh = mysql_connect( "localhost", "root", "" );

The simplest step in beginning to secure our database server is to set a password for MySQL's root user. To do so, enter the following in a Terminal:

    mysqladmin -u root password new_password_here

Once we do that, we'll have to modify our PHP code as well:

   // log into our local server using the MySQL root user.
   $dbh = mysql_connect( "localhost", "root", "new_password_here" );

This is just the start of securing a MySQL installation. You can go much deeper, like restricting access to certain databases by host name, much like we restricted access to certain Web directories with Apache's Allow and Deny directives (see "Choosing Who Sees What" in Part 3 of this series).

After the demonstration . . .

Thankfully, the meeting only lasted half an hour. The wide-eyed wonder boys have left and the redheaded stepchildren have wandered off. You're sitting, once again, with a smug look on your face, complemented by the realization that worrying about job security is a thing of the past. (Just be careful of netslavery.)

You've got databases stored, features adored, and requests explored [1]. Yet you realize there's so much more to Apache lurking beneath the surface, yelling out, "Discover me!" with a glee normally unfounded in typical Mac software. Where would you like to be taken next?

---

[1] Along with the bosses abhorred, benefits scored, and welcome awards, mouths floored and derision ignored, nothing deplored and happiness poured, computer love has been restored, and with no principles discord! Hail the mighty Fnord! (Yes, I could go on.)

Kevin Hemenway is the coauthor of Mac OS X Hacks, author of Spidering Hacks, and the alter ego of the pervasively strange Morbus Iff, creator of disobey.com, which bills itself as "content for the discontented."

---

Return to the Mac DevCenter.