Spring Semester 2005

By Aaron Michael (James) Bond


Project Notes Two: Or How the MySQL was Won.
Date: Sun, 27 Feb 2005 10:05:58 -0500
From: Aaron M. Bond
To: Adrian German
Cc: "Hursh, Mark Howard", "Baechle, Josh Michael", "Hardy, Christopher L", "Cash, Derrick James", "Snetiker, Adam", "Czoch, Bryon E"
Subject: My Meeting with Adrian (Chapter Two): or How the MySQL Was Won

Hello again,

Yesterday's meeting with Adrian was quite exciting. We laughed, we cried, it was better than "Cats." You no doubt have all looked at the notes he prepared on the "what's new" page, so I'm going to try to keep this as relevant and non-redundant as possible.

Adrian and I had hoped yesterday to emulate the way the site should behave in a very simple manner just to get an idea of where our stumbling blocks were going to be. The first step in this process is giving people the ability to donate. So, we're going to need a table which keeps track of users and their data. So, we implemented a table with four fields: username, password, last name, and first name (later we'd have addresses and phone numbers and contact information, but this is a simple test version). In this table, the username has to be unique, so that is our primary key. Next, we'd have to keep track of what they do (how much they give, how many times, and when). We created a table to keep track of transactions with three fields: username, timestamp, and amount. Notably, it is conceivable that people might donate twice. In turn, each transaction is not uniquely identified by just the username, but a combination of the username and timestamp. When I have MySQL describe the fruits of our labor, here's what we get:


mysql> describe users_0226_draft;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| username  | varchar(32) |      | PRI |         |       |
| password  | varchar(20) |      |     |         |       |
| lastName  | varchar(20) |      |     |         |       |
| firstName | varchar(20) |      |     |         |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

mysql> describe transactions_0226_draft;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| username  | varchar(32)   |      | PRI |         |       |
| timestamp | timestamp(14) | YES  | PRI | NULL    |       |
| amount    | double        |      |     | 0       |       |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

Looks good, no?

Alrighty, now, in a perfect world, we'd have PHP programs that people can use to register and thereby populate our users table. Sadly, we had limited time, so test-world is far from perfect. We had to insert our own values in to the users table just to test the functionality. When I select everything I have in the users table, here is the result of that query:

mysql> select * from users_0226_draft;
+--------------------+----------+----------+-----------+
| username           | password | lastName | firstName |
+--------------------+----------+----------+-----------+
| lbird@pacers.com   | dribl    | Bird     | Larry     |
| mjordan@bulls.com  | bulls    | Jordan   | Michael   |
| tkukoc@bulls.com   | abc      | Kukoc    | Toni      |
| rartest@pacers.com | fight    | Artest   | Ron       |
| rmiller@pacers.com | reggie   | Miller   | Reggie    |
+--------------------+----------+----------+-----------+
5 rows in set (0.07 sec) 
Notably, members of the NBA are very generous people.

There are problems with this that Adrian hinted at and I'll address at the end of the mail; but, for now, it works well for our testing purposes.

Now, looking at the scripts Adrian gave you, the scripts on the test site (except for two.php) are the same except for a couple of trivialities:

1) This is the session save path method call used in our script:

session_save_path('/nfs/cust/2/42/37/573242/web/php_sessions/');
Why is it different? The sessions on the burrowww are placed in a different place than the sessions on our Value Web server. So, we have to specify different directories for either machine.

2) The connection to the database server:

$result = mysql_connect("mysql.2wp.org", "zwp0org", "xxxxxxxx");
and the selection of which database:
if (mysql_select_db("zwp0org")) {
are different. Why? Because, on the burrowww, our database is the same machine as the one we are working on. Thereby, we can get away with saying 'localhost.' Here, we're working on one server for HTML, but the MySQL server has a completely different name: mysql.2wp.org. So, we have to explicitly state it. The username, password, and database selection are different simply because those are different account settings. Got it? No? Ask Adrian or myself about this, because it will be very important to understand the differences between burrowww tests and Value Web implementations.

Okay, I said that two of the scripts were trivial differences, right? Well, two.php has some added functionality. If you log in, you may want to see how much you've already donated before you give away more of your savings, right? This newer version of two.php displays a table with your prior transactions before you give more money. Tres nice. Since this differs in a significant way, I'll just post the code to my whole two.php here:

<---
<? session_save_path('/nfs/cust/2/42/37/573242/web/php_sessions/');
   session_start();

   $result = mysql_connect("mysql.2wp.org", "zwp0org", "xxxxxxxx");

  if ($result) {
    if (mysql_select_db("zwp0org")) {
      echo "I can select the database.";
      $query = "select username, password from users_0226_draft where username = '$uname' and password = '$pword'";
      $result = @mysql_query($query);
      if ($row = mysql_fetch_row($result)) {
        echo "(" . $row[0] . "," . $row[1] . ")";
        session_register("username");
        $username = $row[0];
       ?>

<form method="POST" action="./three.php">
   Type the amount : <input type="text" name="pledge">
<p> Please press <input type="submit" value="proceed"> when ready.
</form>


<?
      } else {
        echo "I am sorry, can't find the record.";
      }
      $query = "select timestamp, amount from transactions_0226_draft where username = '$uname'";
      $result = @mysql_query($query);
      echo "<p>Transaction history:<p><table><tr><th>Date (yyyymmddhhmmss)<th>Amount";
      while ($row = mysql_fetch_row($result)) {
        echo "<tr><td>" . $row[0] . "<td>\$" . $row[1];
      }
      echo "</table>";
    } else {
      echo "I cannot connect but I can select the database.";
    }
  } else {
    echo "I cannot connect.";
  }

?>
--->
Whoo, boy. All this typing and I'm pooped. So, if you want to try out our script:
http://www.2wp.org/draft/php/0226/one.php
Log in there with one of the basketball players' usernames and passwords (see the above MySQL query of the users table) and run through it. So far, only Tony Kukoc has donated, so the others need to get on the 'ball.' Ha! I kill me!

As previously mentioned, the users table has a major issue. That issue is that the passwords of users is available to anyone who should want to look at the MySQL. This is a pretty significant security risk. We should encrypt these passwords using MD5 so that people can't see them. Alas, last night I wanted to sleep, so it will have to wait.

Any questions? Tons of questions? Ask me at the meeting if anything is unclear. Have a good one and see you all tonight!

                                                                                               Aaron


Last updated: Feb 27, 2005 by Adrian German for A348/A548