How to configure and access MySQL using the Stanford Web Application Toolkit

From Web Services Wiki

Jump to: navigation, search

Contents

Problem

You want to add database capabilities to your PHP application and connect to Stanford's MySQL database using the Stanford Web Application Toolkit.

Solution

Register for the MySQL service

Register your group, department, or service website for MySQL access. Move on to the next step once you have acquired the name of your database and your account information. Please note that at this time Stanford does not support MySQL accounts for individual users.

Configure StanfordDatabase

There are two ways to configure StanfordDatabase -- using a configuration file or manually changing each setting.

Using a configuration file

Using a YAML-formatted configuration file is the recommended way of storing database credentials. Store the configuration file outside of your web root and ensure that your CGI principal has read access to its directory in AFS. In the next step, you will point StanfordApp to the location of this file.

---
 
# StanfordApp YAML configuration file
 
database:
  name: my_database
  username: my_username
  password: my_password
  use encryption: no

Initialize the StanfordApp class using the configuration file to load the settings. StanfordApp configures a member variable called db, which may be accessed by referencing $app->db.

// Include StanfordApp
include("stanford.app.php");
 
// Load configuration
$app = new StanfordApp("/path/to/config/file.yaml");
 
// Get the preconfigured database connection object (StanfordDatabase) from StanfordApp
$db = $app->db;

Manual configuration

You may also configure StanfordDatabase without using a configuration file. We strongly suggest storing the following script as a separate file in a non-web accessible location and including it on pages that need database access.

// Include StanfordDatabase
include_once("stanford.database.php");
 
// Initialize StanfordDatabase
$db = new StanfordDatabase();
 
// Set the connection information
$db->set_database("my_database");
$db->set_username("my_username");
$db->set_password("my_password");
$db->use_encryption(false);

Connecting to the database server

Before making any queries, call the connect method of StanfordDatabase to log into the server and gain access to the database. When a connection fails, an exception is thrown, which should be caught and handled appropriately.

try {
  // Connect to the database
  $db->connect();
}
catch(Exception $e) {
  // Handle the error gracefully -- usually, you want to display a friendly error message and alert the admin of the problem
  // $e->getMessage() contains the error message associated with the exception thrown by StanfordDatabase
 
  echo $e->getMessage();
  exit(0);
}

Using StanfordDatabase

Call is_connected to check whether a connection has been established. The query method is native to MySQLi -- you may use any of the MySQLi functions with StanfordDatabase. Close the connection by calling close.

// Check if connected to database
if ($db->is_connected()) {
 
  // Perform test query
  $result = $db->query("SHOW TABLES");
 
  // Print the result
  while($row = $result->fetch_assoc()) {
    print_r($row);
  }
 
  // Disconnect from database
  $db->close();
}

Discussion

Prevent others from accessing your database credentials

Since the MySQL credentials are stored in plain text in these examples, we strongly urge you to store the database connection code in a non-web accessible location and use AFS ACLs to protect the directory it resides in.

Visit AFS at Stanford for more information about using AFS ACLs. Read How to protect your cgi-bin directory in AFS to see an example of how to prevent others from accessing your files.

Deciding between a standard connection and stunnel (encryption)

Stunnel is a GNU program that encrypts arbitrary TCP connections inside Secure Sockets Layer (SSL). Applications which handle highly private information may use this connection method to encrypt all traffic. For most applications, a direct connection is sufficient and encouraged, as it is less resource-intensive. Using either method, no traffic that connects to the database from your application passes through the web, and login credentials are always encrypted.

phpMyAdmin

  • phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the web. You may find it useful in administering and debugging your application.

References

Personal tools