Script School Classroom

School Map:  Home / Enroll $ Student Records; Class  
$ News @ LIVE Tech Radio * Support/FAQ | Store | FORUMS


Script School - MySQL 101 - Week #5

Review and Preview

Well, we've come a long way since the first week of this course.  We have discussed: What databases are and what they're good for; How to set up a server on Your local workstation to create and test MySQL databases; How to design databases, Various types of relationships and using keys to link tables; And how to view, insert, update and delete data with browser interfaces through the server side scripting language PHP.  We've seen how to create error checks, and report them back to the user without loosing data. We've discussed populating drop down and select lists in HTML forms from Your database.  This week we will see how to encrypt and compare or recover sensitive data like email addresses, user names and passwords.  We'll take a quick 'starter' look at user authentication - which to cover in any realistic depth would really require an entire class.  We'll discuss how to span and navigate pages when You want to present access to more information than can comfortably fit on a single page.  We'll discuss the all important task of backing up Your database.  Finally, we'll consider the salability of MySQL to create mirror servers through real time replication.  Let's get started.

Encryption

There are two basic categories of encryption: recoverable and comparative.  In recoverable encryption, the original information can be extracted and reproduced.  In comparative, the original data isn't recovered - well - not very easily.  When You use comparative encryption, You use the same algorithm to encrypt the data, which may for example be a password, each time it is entered and compare the results to the encrypted results You have in storage. 

For recoverable encryption with PHP: You can integrate the Mcrypt library functions into PHP at compile time or via linked binaries in Windows.

For comparative encryption with PHP: You can use: md5, sha1, crypt, Mhash. crypt and Mhash allow You to provide, depending on the operating system the server is on, Your own 'seed' to alter the encryption.  Mhash provides many different encryption options.
md5 and sha1 return 'hash' strings that are always of the same fixed length...

MySQL provides the following Encryption functions:

For Comparative Encryption:

For Recoverable Encryption:

Comparative Encryption of Passwords for Authentication with PHP and MySQL 
<?php
// Note: This script handles $_POST data sent from challenge.php
// or signup.php forms (not shown - refer to examples in previous lessons)
// Add data screening and error handling as required.

// Start Session:
session_start();

$encrypted_password =  md5($_POST['password']);
$clean_username = mysql_escape_string($_POST['username']);

// Connect to Your MySQL server (use YOUR parameters):
$db = mysql_connect("host","username","password")
or die("Could not connect:". mysql_error());
print("Connected successfully");

     
// Connect to database:
mysql_select_db("ssMySQLclass",$db)
or die("Could not Connect to database:". mysql_error());
print("Connected to Database");

// To insert a new username & password:
if(isset($_POST['insert'])){
    // Write insert query to a variable:
  $query_insert_pswd = "INSERT INTO `usertable` 
                 SET name = '".$clean_username."';
                 password = '".$encrypted_password."'
                ";

    // Do insert query:
  $result_insert = mysql_query($query_insert_pswd,$db);
}

// To check if the user entered the right username and password combination:
if(isset($_POST['challenge'])){
    // Write select query to a variable:
    // Note: $username and $password after cleaning and encrypting
    // id is AUTO_INCREMENT and is the first or 0 field
  $query_select_pswd = "SELECT id, name, password
                        FROM `usertable`
                        WHERE name = '".$clean_username."'
                        AND password = '".$encrypted_password."'
                       ";

  $result_select = mysql_query($query_select_pswd, $db);
  $id = mysql_result($query_select_pswd, 0, 'id');
  if(mysql_num_rows($result_select) > 0){
        // set Session variable 'authorized' to TRUE
        // it is checked for on other pages:
        $_SESSION['authorized'] = 'TRUE';
        // id may be used in joins with other tables linked to usertable
     $_SESSION['id'] = $id;
     $_SESSION['username'] = $_POST['username'];
        // redirect to first secure page of Your site: 
        // $filepath is the relative path which may include directories
        $filepath = "/directory/welcome.php";
    header("Location:".$_SERVER['HTTP_HOST']$filepath."?".SID);
  }else{
     $_SESSION['authorized'] = 'FALSE';
     // $filepath is the relative path which may include directories
     $filepath = "/directory/challenge.php";
     // redirect with a header back into challenge.php form
        // (also see week #3 of this course)
     header("Location:".$_SERVER['HTTP_HOST']$filepath);
     // home.php and challenge.php also provide a link to signup.php
        
        // You may want to set a limit on number of attempts with a session variable,
        // and set time outs between attempts to foil brute force attacks.
        // You may also want to log the users IP address and send an attack alert
        // to administrators via email after repeated failures from the same IP.
  }
}
?>

You will need to pass the Session id from page to page using SID,
 or configure PHP to use trans SID 
 or rewrite to use $_COOKIE to pass the Session id 
 and explain to users that it is necessary for them to accept cookies.
 It should be noted that cookies which expire at the end of a 
 session are a more secure way to pass the Session id, 
 since they are not retained in the browsers history and are 
 deleted when the browser is closed. 
 Consider the frequency of Session 'garbage collection' 
 or expunging expired Sessions as a security concern.

Place the following script at the TOP of every secured page of Your site: 
 (NOT the home, challenge & sign up pages!)

<?php
// Continue the Session: (also see week #3 of this course)
// Using Sessions:
// GET Session id:
if(isset($_GET["PHPSESSID"])){
    // Assign it:
  $Sess_ID = $_GET["PHPSESSID"];
  session_id("$Sess_ID");
}
// Start Session:
session_start();

// Check authorization: 
if(empty($_SESSION['authorized']) 
|| $_SESSION['authorized'] != 'TRUE'){
    // home.php and challenge.php also provide a link to signup.php
        $filepath = "/directory/challenge.php";  
    header("Location:".$_SERVER['HTTP_HOST'].$filepath);
    // Stop the server from wasting resources processing the rest of the script:
    exit;
}

// Page Content Goes Here



?>

Sending Passwords over Insecure Transmissions No matter how careful You are within Your scripts, sending data over insecure transmissions leaves a gaping hole in Your application's overall security.  You may choose to use Secure Socket Layers or similar transport.  A 'halfway' measure that does not involve the added costs of SSL is to use a free JavaScript md5 encryption algorithm which is available at: http://pajhome.org.uk/crypt/md5/  To use it You would have PHP generate a random number and store it in a session variable, and also send this number to the client. The client uses JavaScript to send back the results of hex_md5(password + random) which PHP can also generate for comparison purposes with it's md5() function.  The password has not been sent in the clear, and each time a new random number is generated and used, thus preventing replay attacks.  Recall that md5 and similar comparative encryption methods are not easily reversible to obtain the original data. Script School is providing a backup mirror of the essential core of the JavaScript MD5 library as a JavaScript include file.
To download it, click here. 

Save it into the same directory as your page.

To include the library, insert in Your page :

<script language="JavaScript" src="md5.js"></script>

To get a result, use it like any other JavaScript function:

<script language="JavaScript">
   result = hex_md5("input string");
</script>

Testing Password Strength: Within PHP You can test the worthiness of passwords using crack functions if the crack library module has been compiled into PHP. 

Spanning and Navigating Large Result Sets from MySQL

Often You will have large amounts of information in Your database which You will need to display in a fashion which is easy to navigate. Depending on how much space each record requires on a page, You may want to display 1, 5, 20 or more records per page. 

Spanning And Navigation of Large Result Sets 
<?php

// BEGIN Connection to Database Sequence =======---  
if(!($db = @mysql_connect("host","username","password")))
   die("Could not connect to MySQL"); 
if(!(@mysql_select_db("database",$db)))   
   die("Could not connect to database");
//  END Connection to Database Sequence  ====================--- 

// BEGIN Data Row Count =================================---
// Note that backticks ` ` are used around the field name `show` since it has special meaning in SQL:
$query = "SELECT COUNT(`show`) AS `NumEntries` 
        FROM `list`
        WHERE `show` = '1'";
$result = @mysql_query($query, $db);
if($result){
  $row = mysql_fetch_array($result);
  $NumEntries = $row["NumEntries"];
} else {
  // ERROR in query1
}
// END Data Row Count ====================================---

// BEGIN Obtain GET Variables included with URL,  if any =====---        
if(!empty($_GET["rows"])){
  // gets rows sent from get
  $ROWS = htmlentities(stripslashes((int)$_GET["rows"]), ENT_QUOTES);
  // sets cookie for next 'bare' page visit
  $rows_string = (string)$ROWS;
  setcookie('rows',$rows_string); 
} else {
  $ROWS = htmlentities(stripslashes($_COOKIE["rows"]), ENT_QUOTES);
}
// END Obtain GET Variables included with URL, COOKIE Variables if any =========---
if(empty($ROWS) || $ROWS == '' || $ROWS == NULL || $ROWS > 20 || $ROWS < 1){
  $ROWS = 3;
}
$num_pages = ceil($NumEntries/$ROWS);
$page = htmlentities(stripslashes((int)$_GET["pg"]), ENT_QUOTES);
if(empty($page) || $page == '' || $page == NULL 
                || $page > $num_pages || $page < 1){
  $page = 1;
}
$begin_at = $page * $ROWS - $ROWS; 
?>
<html>
<head>
<title>Span Pages of list Table</title> 
<head>
<body>
<table width="700">
<tr><td width="150">Name</td><td width="550">Description</td><tr>
<?php
// BEGIN Retrieval of Data to be Displayed =======---
$query = "SELECT name, description FROM `list` 
          WHERE `show` = '1' 
          LIMIT $begin_at, $ROWS
         ";
$result = mysql_query($query, $db);
while($row = mysql_fetch_array($result)){
  echo "<tr><td>".$row['name']."</td><td>".$row['description']."</td><tr>";
}
?>
</table>
<!-- // END Retrieval of Data to be Displayed =========- -->
       
<!-- // BEGIN Navigation ====================- -->
<!-- // Create a Dropdown list to select the number of ROWS -->
<form action="<?php echo $_SERVER['PHP_SELF']; ?>"
 method="get" enctype="application/x-www-form-urlencoded"
 name="rows_form">
 <input name="pg" type="hidden" value="<?php echo $page ?>">
 <select name="rows" >
   <option value="3" selected>Records per Page</option>
   <option value="1">1</option>
   <option value="3">3</option>
   <option value="5">5</option>
   <option value="10">10</option>
   <option value="20">20</option>
   </select>
   <input name="sr" type="submit" value="Go">
</form><br><br>
<?php
if($page > 1){
  echo '<a href="'.$_SERVER['PHP_SELF'].
'?rows='.$ROWS.'&pg=1"
onmouseover="window.status=\'Go to the First Page\'; return true"
onmouseout="window.status=\'\';return true">First Page</a> &nbsp;';
} else {
  echo 'First Page &nbsp;';
}
if($page > 1){
  $go_to = $page - 1;  
  echo '<a href="'.$_SERVER['PHP_SELF'].
'?rows='.$ROWS.'&pg='.$go_to.'"
onmouseover="window.status=\'Go to the Previous Page\'; return true" 
onmouseout="window.status=\'\';return true">Previous Page</a> &nbsp;';
}else {
  echo 'Previous Page &nbsp;';
}	

/* This produces an unlimited number of page references!!!
for($x=1;$x<=$num_pages;$x++){
  if($x != $page){
       echo '<a href="'.$_SERVER['PHP_SELF'].'?pg='.$x.'">'.$x.'</a> &nbsp;';
  } else {
      echo ''.$x.' &nbsp;';  
  }
}
*/
// Rather than the above, create a series of 7
// references, with the current page in the middle: 

for($y = -3; $y <= 3; $y++){
  $x = $page + $y; 
  if( ($page + $y >= 1) && ($page + $y != $page) 
&& ($page + $y <= $num_pages)){
   echo '<a href="'.$_SERVER['PHP_SELF'].
'?rows='.$ROWS.'&pg='.$x.'" 
onmouseover="window.status=\'Go to Page '.$x.'\'; return true" 
onmouseout="window.status=\'\';return true">'.$x.'</a> &nbsp;'; 
  } elseif (($page + $y >= 1)&&($page + $y <= $num_pages)) {
   echo $x.' &nbsp;'; 
  }
}
if($page < $num_pages){
  $go_to = $page + 1; 
  echo '<a href="'.$_SERVER['PHP_SELF'].
'?rows='.$ROWS.'&pg='.$go_to.'" 
onmouseover="window.status=\'Go to the Next Page\'; return true" 
onmouseout="window.status=\'\';return true">Next Page</a> &nbsp;';
} else {
  echo 'Next Page &nbsp;';
}
if($page < $num_pages){
 echo '<a href="'.$_SERVER['PHP_SELF'].
'?rows='.$ROWS.'&pg='.$num_pages.'" 
onmouseover="window.status=\'Go to the Last Page\'; return true" 
onmouseout="window.status=\'\';return true">Last Page</a>';
} else {
  echo 'Last Page';
}
// END Navigation ====================--- 
?>
</body>
</html>

Save as pagespan.php  
You can use phpMyAdmin to create the list Table from a datadump provided below.

Database Dumping and Backup

It is important to do database backups and securely store copies of the structure and recent data in Your database, in case a disaster occurs.  If You are working with critical data, a full disaster recovery plan is essential.  In conjunction with a transaction log it is usually possible to accomplish a complete recovery.   See the MySQL manual on backups for the details of the sequential procedures and the required preparations for such crucial demands.  We will introduce, review and discuss various methods of creating backups and using them to restore databases.

list Table Structure and Data Dump from phpMyAdmin

The list Table which was used in the page spanning and navigation example above, has the following structure and sample data. It is configured as SQL queries which can be used to (re)create the Table and fill it with data. This 'dump' was exported with phpMyAdmin.  Within phpMyAdmin, paste the contents below into a SQL query box in the ssMySQLclass database and execute the query to create the list Table and fill it with sample data.  This is the procedure You can use to restore each Table in a database. You can also load from data exported into files.

# Database : `ssMySQLclass`
# --------------------------------------------------------

#
# Table structure for table `list`
#

CREATE TABLE `list` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`description` text,
`show` enum('0','1') NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

#
# Dumping data for table `list`
#

INSERT INTO `list` VALUES (1, 'Red', 'Red is a very bright hot color. It is the color of apples, strawberries, and some rocks.', '1');
INSERT INTO `list` VALUES (2, 'Blue', 'Blue is the color of the sky. It is a cool color. It is the color of some very strange brews.', '1');
INSERT INTO `list` VALUES (3, 'Green', 'Green is the color of plants. It is a vibrant color. Lawns are green.', '1');
INSERT INTO `list` VALUES (4, 'Yellow', 'Yellow is the color of the sun. It is a bright color. It is also considered the color of cowards, for some unknown reason.', '1');
INSERT INTO `list` VALUES (5, 'Black', 'Black is not a color. Black is the absence of color. It is a hot \'color\' to wear in the sun.', '1');
INSERT INTO `list` VALUES (6, 'White', 'White is the mix of all colors of light. It is a very bland mix. It is a very vanilla mix.', '1');
INSERT INTO `list` VALUES (7, 'Purple', 'Purple is a mix of Blue and Red.', '1');
INSERT INTO `list` VALUES (8, 'Gray', 'Gray' is between Black and White.', '1');
INSERT INTO `list` VALUES (9, 'Blank', NULL, '0');

phpMyAdmin  Is a free program which is often provided by web hosts and which You were recommended to include in Your workstation setup. It can EXPORT structure and data from Tables, as shown in the example above.   When exporting, it is a good habit to enclose table and field names in backticks, in case any words that have special meaning within SQL or MySQL are used as names.  Data which is exported this way can be used to reconstruct Tables, by pasting the 'dump' into a SQL query box within the program, and executing the query. Try this to create the list Table, for the pagespan.php script. Drop the Table first, if it currently exists, perhaps as a corrupted version.  You can also run a restore by browsing to a text file version of the dump.  This is the simplest and easiest way to do a backup, and may be the only way with some hosting company configurations running in full 'safe mode' - which prohibits file access in the directory where MySQL is located.  Other similar programs, like SQLyog are also available at low cost.

You may be able to use the SQL commands: "SELECT INTO OUTFILE ... " and it's syntactically matching reverse "LOAD DATA INFILE ... " to dump and load custom formatted data files on the 'main' server.  You can only use this method if You have lenient server conditions that permit file access privileges, which are often blocked by hosting companies running in 'safe mode'.  You cannot overwrite files with this method, the file must not already exist. It is also possible to use SELECT INTO DUMPFILE for individual BLOB values, since DUMPFILE does not escape characters the way OUTFILE does. You may also be able to upload files from a remote "LOCAL" client if mysqld was not started with --local-infile=0, and Your local client is set up with a properly configured MySQL server. There are many configuration issues involved with these methods.  A common problem involves how PHP is linked to MySQL.  If PHP has been set up to use it's own internal (older) MySQL libraries, there are often problems. This is resolved in PHP 5 by linking it to MySQL's libraries (see below).  However, there are security vulnerabilities to consider if remote "LOCAL" access methods are enabled. 

"BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'" and "RESTORE TABLE tbl_name[,tbl_name...] FROM '/path/to/backup/directory'" are similar to SELECT INTO OUTFILE and LOAD DATA INFILE but copy and use complete duplicates of the structure and data Table files. Index files will be recreated during a RESTORE.  Individual Tables are locked to only allow reads while they are being backed up. If you want to back up several tables as a snapshot, you must first issue LOCK TABLES.  These backups are written to the server's harddrive, from which You may be able to copy the files to CD's or DVD's.  The MySQL user must have file write privileges (see GRANT) in the appropriate directories on the server and the directories must already exist before they can be written to. It is important to read the MySQL Manual on Database Backups before using these commands.

If You have direct shell access on the server, or can run a shell script remotely by telnet or through a script, You can use programs which come with MySQL:

These shell programs can also be automatically run on a regular routine through cron jobs.

Using PHP to Execute a Shell Backup Command 
<?php
     $host = 'database-host';          
     $user = 'database-username';                
     $pass = 'database-password'; 
     $dbn   = 'database-name';
     $bkupdir = 'backup-directory-name'; 
     // Note getenv('DOCUMENT_ROOT') adds a trailing slash:
     $root = getenv('DOCUMENT_ROOT');
     $on = date("Y-m-d-H-i");
     $cmdfrom = 'mysqldump --opt -h'.$host.'-u'.$user.'-p'.$pass.' '.$dbn.' > ';
     $cmdto = ''.$root$bkupdir.'/'.$dbn.'-'.$on.'.sql';
     $cmd = ''.$cmdfrom.''.$cmdto.'';
     exec($cmd);    
 ?> 


Save as: database-name_bkup.php
This script can be run through a cron job.

Another option is to copy the table files *.frm, *.myd, *.myi for each table and then use them to overwrite these files in case of data corruption or loss, but only if You can read and write files in the MySQL directory. 

You can also write and run a script that can analyze and return Your table structures and do a row by row data dump, and then run another script to restore Your Tables from Your backup through multiple CREATE and INSERT queries. 

For most smaller sites, installed programs like phpMyAdmin and SQLyog are the easiest, and probably the most practical approach to backing up Your database. 

Replication - Scaling MySQL onto many Mirror Servers

A useful aspect of MySQL is it's ability to use REPLICATION so that databases are mirrored - copied in real time to multiple servers.  In effect You wind up with non-intefering (no table locks necessary) 'backups' on the harddrives of other machines. Of course these are NOT a substitute for doing a proper backup and storing that data on non-volatile media such as a CD or a DVD, and storing it offsite.  REPLICATION is one way - A main server is designated as the 'master' and any number of 'slave' servers get their copies from it.  These can be accessed as regular servers by scripts, and can be 'cascaded' to provide access when the site gets busy.  A script would try to log into a server, and if it's busy and times out, the script attempts to log on to the next server and so on until it finds one which is available. Data writes can only take place on the 'master'.  Typically of course, reads are the most frequent query.  See the manual for complete details on setting up REPLICATION.

On Using MySQL with PHP 5:

Earlier in this course it was mentioned that as of PHP 5 MySQL will no longer be bundled as a native library.  However, PHP will continue to support MySQL by linking to it's library when building under UNIX, and including the appropriate .dll's under Windows.  In fact a whole new set of MySQL functions are being developed to take advantage of advances in the latest versions of MySQL.  If You run Your site on a hosted server offering MySQL, this should not affect You.  If You need to load the libraries on Your own server the PHP Manual states:

UNIX users, ... build PHP against their system's libmyqlclient library simply by doing --with-mysql=/usr when building PHP. Windows users may enable the extension php_mysql.dll inside php.ini. Also, copy libmySQL.dll into the appropriate %SYSTEMROOT% directory, just like you do with every other bundled DLL from the dll directory.

For the full story see: http://us3.php.net/manual/en/faq.databases.php#faq.databases.mysql.php5

SQLite and Other Database Systems

PHP 5 will embed the SQLite precompiled C library.  It is a fast Relational Database that uses standard SQL, although it is less robust than MySQL as far as built in functionality.  Depending on the requirements of a site, one system may be more appropriate than another.  For example, PostgreSQL while slower than MySQL for websites, has a fully developed scripting language that enables very sophisticated data mining.  Other systems may be suitable for traffic control or mapping DNA analysis. 

If You have any questions, please use the Script School Forum, the LIVE CHAT Tuesday workshops, or Chat or call during the LIVE Radio Show on Fridays.

Week #5 Assignments

Basic Assignment: Create a backup copy of the structure and data in the database You created for the Website-Sponsor application, and use it to create a duplicate database called ssMySQL101. Alter the program to use ssMySQL101 and delete ssMySQLclass.

Advanced Assignment: Add user authentication to the Website-Sponsor application.  Enable a multi page span to display all of the data in all of the Websites or through a user controlled switch, all of the data in all of the Sponsors. For each one, display all of it's URL's and relationships as links.

Submit Your assignments to the Script School Forum for comments and credit.

Script Library

The following links will enable You to obtain all of the script examples in this course:

Final Thoughts The author hopes You have gained insight into one of the most powerful tools computers offer - Databases.  In this course we have only been able to touch the surface of the functionality of Databases.  Their use and usefulness grows daily.  Whether to track and help predict the weather, or aid scientific investigation, or to provide dynamic websites honed to individual users needs - databases are a fascinating tool that extend the ability of the Human mind.  Applied with wisdom, they can enhance life.

Principal note: Logspirit was paid for writing this course material, as all authors have been. If you enjoyed the course material and found it particularly useful, perhaps you could give the author a tip? You can do that easily through PayPal by clicking the button to the left of this text.

If you are interested in writing and running a course at Script School, please contact the principal with your course ideas and outline and telephone contact number.

Need help with your assignment or have questions about the first week's course?

School Map:  Home / Enroll $ Student Records; Class  
$ News @ LIVE Tech Radio * Support/FAQ | Store

Advertising | Link To Us | Privacy 
Copyright 2000-2002 Script School Productions / KMR Enterprises 
No part of this website may be reproduced, copied and/or distributed in any medium 
without express written permission