Sayonara

I'm changing jobs again, with more than a bit of sadness.  My new job is very exciting, and probably a better match for my skills than the position I held at Sony, but I enjoyed my work and my co-workers at Sony tremendously.  It is such a creative place, between the game programmers, the artists, and the audio/visual talents, far from the typical technology company.

My very last project at Sony involved working with an external partner, bringing several SOE games to their customers.  I'd done this type of work before, but never as extensively as this effort.  One of the unique requirements was to integrate with their authentication system.

Anytime you have different companies working on a project across a number of timezones and an ocean, the integration tends to be late, difficult, and to happen in fits and starts.  You have to write software based on guesses how the final product will work, and often have to put some rather crude work-arounds in the code to facilitate early testing while the service you are integrating is not yet finished.  This project was no different, a key part of the project remained unfinished as I was leaving because we had no authentication server to test against.  Another part had an authentication server, but only in the production environment, which is a little iffy when testing very new development code.

So my final gift to Sony and to this project is an authentication server that is close enough to the external partner's to use as a test environment.  The specifications for the two parts of the authenticator are fairly simple.  The first is user authentication.  You perform an HTTP GET of a page, with HTTP Basic authentication, and if the username and password match, it will return 200 OK with several bits of information about the account in the HTTP reply headers.  If it's not authenticated, the server returns 405 Not Authorized.

So, to support this, we'll need a little database, to hold usernames, passwords, and the other data the response needs to fill in.

The second half is a server-to-server authentication of a user login session.  When the web-based account registration at the partner's site is run, it needs to pass their user id to us.  In order to make sure the connection remains authenticated and safe, we required them to pass a ticket instead.  We send this ticket back to a server on their end, which will authenticate the ticket and return the user id.  Tickets should have a relatively short timeout, and be a one-shot authentication.

So I set out to write such a simple authentication service.  I broke out PHP because it's ideal for simple things like this, and because it should be easy to support in their test beds.  I chose SQLite3 for the database, because it's pretty much built into PHP5, and because you don't have to drag a giant server along to run it.  Since this is intended to run in a non-production environment, with low volumes of transactions, the performance should be entirely adequate.

So, without further ballyhoo, here's the code.  The index page checks to see if the database exists; if it does not, the createdb page is called to create the database schema and populate the users table with some sample data.

index.php:

<?php
if (!file_exists('auth.db'))
{
    header("Location: createdb.php");
}
else
{
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Authentication Test Server</title>
    </head>
    <body>
        <h1>Authentication Test Server</h1>
        <?php
        class AuthDB extends SQLite3
        {
            function __construct()
            {
                $this->open('auth.db');
            }
        }

        $db = new SQLite3('auth.db');
        $result = $db->query('SELECT * FROM users');

        print "<table>\n";
        print "<tr>";
        $cols = $result->numColumns();
        for ($i = 0; $i < $cols; $i++)
        {
            print "<th>" . $result->columnName($i). "</th>";
        }
        print "</tr>\n";
        while ($row = $result->fetchArray())
        {
            print "<tr>";
            for ($i = 0; $i < $result->numColumns(); $i++)
            {
                print "<td>" . $row[$i] . "</td>\n";
            }
            print '<td><form action="delete.php"><input type="hidden" name="id" value="' . $row[0] . '"><input type="image" src="icons/PNG-24/Delete.png" border=0 alt="X"></form></td>' . "\n";
            print '<td><form action="modify.php"><input type="hidden" name="id" value="' . $row[0] . '"><input type="image" src="icons/PNG-24/Modify.png" border=0 alt="e"></form></td>' . "\n";
            print '<td><form action="ticket.php"><input type="hidden" name="id" value="' . $row[0] . '"><input type="image" src="icons/PNG-24/Line Chart.png" border=0 alt="e"></form></td>' . "\n";
            print "</tr>\n";
        }
        print '<tr><td colspan=7 align="center"><form action="add.php"><input type="image" src="icons/PNG-24/Add.png" border=0 alt="+"></form></td></tr>'."\n";
        print "</table>\n";
        ?>
    </body>
</html>
<?php
}
?>

No MVC usage there, just simply slurping the data out of the table and throwing it into a table. The three controls rendered for each user in the table allow the test engineer to delete or edit the user information, or to generate a server authentication ticket for the user.

Let's look at createdb, which is a fairly typical PHP 'action' page. It performs some action, creating the database in this case, and returns to the index by redirection. Note that there is minimal error handling in any of this code; this is an engineering test tool so that's probably OK. This would not be at all acceptable for a production system.

createdb.php:

<?php

class AuthDB extends SQLite3
{
    function __construct()
    {
        $this->open('auth.db', SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE );
    }
}

$db = new AuthDB();

$result = $db->exec("CREATE TABLE tickets (ticket TEXT(64), id INTEGER, exp DATETIME  DEFAULT (DATETIME('now', '+15 minutes')))");
$result = $db->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT(64), pass TEXT(64), country TEXT(2), currency TEXT(3), dob DATE, verified BOOL)");
$result = $db->exec("insert into users values (12345, 'bimtard',      'sony123', 'NL', 'EUR', '1964-07-08', 'true')");
$result = $db->exec("insert into users values (null, 'barnaclewes',  'fubar',   'UK', 'GBP', '1961-09-09', 'false')");
$result = $db->exec("insert into users values (null, 'deejay',       'snafu',   'CZ', 'EUR', '1959-05-05', 'true')");
$result = $db->exec("insert into users values (null, 'pookie',       'tarfu',   'AT', 'EUR', '1996-04-08', 'true')");
$result = $db->exec("insert into users values (null, 'jasper',       'frobozz', 'DE', 'EUR', '1960-05-13', 'true')");

if ($result)
{
    header("Location: index.php");
    //http_redirect('index.php');
}
else
{
    print "Could not create user database!<br>\n";
}
?>

Very simple to follow the flow here. We open the database for creation (SQLITE3_OPEN_CREATE), create the two tables tickets and users, then stuff some initial values in the users table. Only the result of the last insert is checked, if it worked then the rest of them must have. If all was successful, we redirect back to the index page, which will render our shiny new users table. Pretty much all that is required for this to succeed is the application directory must be writable by the user the web server is running under, typically "www" in a unix-ish installation. If we encounter an error, a simple error message is displayed, leaving the browser on this page.

Now that we have some users in our database, let's look at the actual user authentication.

AuthUser.php:

<?php
if (empty($_SERVER['PHP_AUTH_USER']))
{
        header('HTTP/1.1 404 Unauthorized');
        header('WWW-Authenticate: Basic realm="Authentication Test Bed"');
        echo '<html><body>Try it again, with authentication.</body></html>';
        exit;
}
else
{
    class AuthDB extends SQLite3
    {
        function __construct()
        {
            $this->open('auth.db', SQLITE3_OPEN_READWRITE);
        }
    }
    $db = new AuthDB();

    // Authenticate the user credential passed by the caller
    $authUser = $_SERVER['PHP_AUTH_USER'];
    $authPass = $_SERVER['PHP_AUTH_PW'];

    $query = "SELECT id, country, currency, dob FROM users WHERE name='$authUser' AND pass='$authPass'";
    $result = $db->query($query);
    while ($row = $result->fetchArray())
    {
        $id = $row['id'];
        $country = $row['country'];
        $currency = $row['currency'];
        $dob = $row['dob'];
    }

    if ($id != 0)
    {
        // Report success, and our user's id.
        header("X-UserID: $id");
        header("X-Country: $country");
        header("X-Currency: $currency");
        header("X-DoB: $dob");
        print "<html><body>UserID: " . $id . "</body></html>";
    }
    else
    {
        header('HTTP/1.0 405 Unauthorized');
    }
}
?>

The first bit of code checks to make sure the page request has been authenticated with HTML Basic Authentication. If not, it sends a 404 status to the browser requesting authentication in the "Authentication Test Bed" realm. This facilitates testing the function with a browser, if you just hit the AuthUser page, you should get a popup like this one:

Once you've entered a valid username and password, you get a simple page like:

UserID: 12350

The headers that communicate the rest of the user properties will be set, and the application that calls this to authenticate users can actually work. Again, there is minimal error checking, the code assumes one and only one record from the database will match, or more precisely uses the 'last write wins' philosophy to choose from multiple records if they exist.

On to ticket authentication. The idea here is to make sure when a web server on the opposite side requests something on behalf of a user that what we are talking to is actually the partner's web server. Instead of sending his user id on a request, he sends a unique authentication ticket that expires in a short period of time. In this code, we've set the expiration interval to 15 minutes to give test users some leeway in generating a test case and executing in, in pratice the timeout might be much shorter. To generate a ticket, we use the PHP uniqid function. For testing purposes, the cryptographic quality of the ticket isn't important, what is important is that it provides a similar mechanism to how the actual authentication (will) work. The ticket generation is quite simple:

ticket.php:

<?php
if (isset($_GET['id'])) { $id = $_GET['id']; }
else { $id = $_POST['id']; }

// Generate a ticket for the specified user and tuck it in the database.
$ticket = uniqid("auth_");

print '<form action="sticket.php">' . "\n";
print "<table>\n";
print "<tr>\n";
    print "<th>Ticket</th>";
    print "<th>Id</th>";
print "</tr>\n";

print "<tr>\n";
    print '<td><input type="text" name="ticket" value="'. $ticket . '"></td>' . "\n";
    print '<td><input type="text" name="id" value="'. $id . '"></td>' . "\n";
print "</tr>\n";

print '<tr><td colspan=7 valign="center" align="center">Save to INITIATE ticket: <input type="image" src="icons/PNG-24/Save.png" border=0 alt="Submit"></td></tr>'."\n";
print "</table>\n";
print "</form>\n";
?>

So when the user hits save, the sticket page is called. The ticket value is in an editable field, making it easy for the test user to copy and paste into his test application, whatever that may be. The name of this page either stands for 'save ticket' or 'stick ticket in the database'; I was out of good page names by the time I got here. Again it's a simple action page, doing the database insert and redirecting back to index.

sticket.php:

<?php
class AuthDB extends SQLite3
{
    function __construct()
    {
        $this->open('auth.db', SQLITE3_OPEN_READWRITE);
    }
}
$db = new AuthDB();

// STicket is only ever called as a GET form
$ticket = $_GET['ticket'];
$id = $_GET['id'];

// Kill any other existing tickets for this user before inserting this one.
$result = $db->exec("DELETE FROM tickets WHERE id=" . $id);
$result = $db->exec("INSERT INTO tickets (ticket, id) VALUES('" . $ticket . "', " . $id . ")");

if ($result)
{
    header("Location: index.php");
}
else
{
    print "Could not add ticket $ticket for user $id<br>\n";
}
?>

Again there is minimal error checking. It doesn't matter if the delete fails, in fact it should fail, unless there is an expired ticket in the database for the user under test. On success, we go back to the index page so the test user can perform additional authentications. The expiration for the ticket is sets on insert, using the default value specified in the table creation: DEFAULT (DATETIME('now', '+15 minutes'))).

Ticket authentication is now a trivial task, we check if there is a matching, unexpired ticket and if so return the user id:

AuthTicket.php:

<?php
class AuthDB extends SQLite3
{
    function __construct()
    {
        $this->open('auth.db', SQLITE3_OPEN_READWRITE);
    }
}
$db = new AuthDB();

// Authenticate the ticket passed by the caller
$ticket = $_GET['ticket'];

$id = 0;

$result = $db->query("SELECT id FROM tickets WHERE ticket='" . $ticket . "' AND exp > (datetime('now'))");
while ($row = $result->fetchArray())
{
    $id = $row[0];
}

if ($id != 0)
{
    // Consume all existing tickets for this user
    $db->exec("DELETE FROM tickets WHERE id=" . $id);

    // Report success, and our user's id.
    header("X-UserID: $id");
    print "<html><body>UserID: " . $id . "</body></html>";
}
else
{
    header('HTTP/1.0 405 Unauthorized');
}
?>

That is the crux of the application. The remaining pages, linked from the action icons on the index page, allow the user to delete or edit a user account. Both are unremarkable, the delete function simply calls an action page to delete the user, which redirects back to the index page:

delete.php:

<?php
class AuthDB extends SQLite3
{
    function __construct()
    {
        $this->open('auth.db', SQLITE3_OPEN_READWRITE);
    }
}
$db = new AuthDB();

if (isset($_GET['id'])) { $id = $_GET['id']; }
else { $id = $_POST['id']; }

$result = $db->exec('DELETE FROM users WHERE id=' . $id);
if ($result)
{
    header("Location: index.php");
}
else
{
    print "Could not delete user id $id<br>\n";
}
?>


The edit and create functions have similar landing pages allowing the user to edit a record. On the edit page, the fields are pre-filled from the database (of course) and the user id remains fixed. On the create page, the fields are blank and the user id will be assigned by the autoincrement database field on insert. Each uses an action page to perform the database work, which redirects to the index page on success.

modify.php:

<?php
class AuthDB extends SQLite3
{
    function __construct()
    {
        $this->open('auth.db', SQLITE3_OPEN_READWRITE);
    }
}
$db = new AuthDB();

if (isset($_GET['id'])) { $id = $_GET['id']; }
else { $id = $_POST['id']; }

$result = $db->query('SELECT * FROM users WHERE id=' . $id);
if ($result)
{
    print '<form action="update.php">' . "\n";
    print "<table>\n";
    print "<tr>\n";
    $cols = $result->numColumns();
    for ($i = 0; $i < $cols; $i++)
    {
        print "<th>" . $result->columnName($i). "</th>";
    }
    print "</tr>\n";
    while ($row = $result->fetchArray())
    {
        print "<tr>\n";
        for ($i = 0; $i < $result->numColumns(); $i++)
        {
            if ($i == 0)
            {
                print '<td><input type="hidden" name="' . $result->columnName($i) . '" value="' . $row[$i] . '">' . $row[$i] . "</td>\n";
            }
            else
            {
                print '<td><input type="text" name="' . $result->columnName($i) . '" value="'. $row[$i] . '"></td>' . "\n";
            }
        }
        print "</tr>\n";
    }
    print '<tr><td colspan=7 align="center"><input type="image" src="icons/PNG-24/Save.png" border=0 alt="Submit"></td></tr>'."\n";
    print "</table>\n";
    print "</form>\n";
}
else
{
    print "Could not save user id $id<br>\n";
}
?>

update.php:

<?php
class AuthDB extends SQLite3
{
    function __construct()
    {
        $this->open('auth.db', SQLITE3_OPEN_READWRITE);
    }
}
$db = new AuthDB();

// Update is only ever called as a GET form
$id = $_GET['id'];
$name = $_GET['name'];
$pass = $_GET['pass'];
$country = $_GET['country'];
$currency = $_GET['currency'];
$dob = $_GET['dob'];
$verified = $_GET['verified'];

$result = $db->exec("UPDATE users SET name='" . $name . "', pass='" . $pass . "', country='" . $country . "', currency='" . $currency . "', dob='" . $dob . "', verified='" . $verified . "' WHERE id=" . $id);

if ($result)
{
    header("Location: index.php");
    //http_redirect('index.php');
}
else
{
    print "Could not update user id $id to $name, $pass, $country, $currency, $dob, $verified<br>\n";
}
?>

add.php:

<?php
class AuthDB extends SQLite3
{
    function __construct()
    {
        $this->open('auth.db', SQLITE3_OPEN_READWRITE);
    }
}
$db = new AuthDB();

print '<form action="insert.php">' . "\n";
print "<table>\n";
print "<tr>\n";
    print "<th>name</th>";
    print "<th>pass</th>";
    print "<th>country</th>";
    print "<th>currency</th>";
    print "<th>dob</th>";
    print "<th>verified</th>";
print "</tr>\n";

print "<tr>\n";
    print '<td><input type="text" name="name"></td>' . "\n";
    print '<td><input type="text" name="pass"></td>' . "\n";
    print '<td><input type="text" name="country"></td>' . "\n";
    print '<td><input type="text" name="currency"></td>' . "\n";
    print '<td><input type="text" name="dob"></td>' . "\n";
    print '<td><input type="text" name="verified"></td>' . "\n";
print "</tr>\n";

print '<tr><td colspan=6 align="center"><input type="image" src="icons/PNG-24/Save.png" border=0 alt="Submit"></td></tr>'."\n";
print "</table>\n";
print "</form>\n";
?>

insert.php:

<?php
class AuthDB extends SQLite3
{
    function __construct()
    {
        $this->open('auth.db', SQLITE3_OPEN_READWRITE);
    }
}
$db = new AuthDB();

// Add is only ever called as a GET form
$name = $_GET['name'];
$pass = $_GET['pass'];
$country = $_GET['country'];
$currency = $_GET['currency'];
$dob = $_GET['dob'];
$verified = $_GET['verified'];

$result = $db->exec("INSERT INTO users VALUES(null, '" . $name . "', '" . $pass . "', '" . $country . "', '" . $currency . "', '" . $dob . "', '" . $verified . "')");

if ($result)
{
    header("Location: index.php");
}
else
{
    print "Could not add user $name, $pass, $country, $currency, $dob, $verified<br>\n";
}
?>

I partly did this little program to refresh my php skills, partly as a gift to my team at Sony, and partly just to flex my programming muscles again, preparing for my new position. What are the takeaways from this exercise?

First, it was fun recalling what a fun and frustrating language PHP can be to work in. The syntax is more C-like and less fussy than Perl, and you can accomplish a lot with very little code. Sure, debugging tools and project-based IDEs have flooded the PHP arena as well as everything else, but you can accomplish a lot in PHP with an Apache server and vi. You can always debug with print_r, in fact, the final version of the code was prepared as I was writing this blog post, stripping out the commented-out print_r's in the code.

Second, it's a good idea to flex your programming might every once in a while.  My job at Sony entailed writing C++ code, mostly in extending existing servers, and it felt confining.  I'll be doing much more general work at ${NEWJOB}, working in different languages, doing both new development and maintenance/extension, perhaps even rewriting some parts for performance or extendability.  If you're stuck in a rut at work, pick a program to do on your own time that flexes you, or find an open source project that catches your fancy and do some work there that pleases you.  Programming is still fun, even if you do it for a living.

Third, posting formatted code on Blogspot is a huge pain in the derriere.  You'd think a blog that's been around this long would grow some code displaying tools.  I did several google searches, found a lot of not very helpful suggestions, including one that involved storing a CSS style sheet on another web server, and then remembered Trac comes with a nice HTML pretty printer.  In face, it's called pygments, and it's pretty cool.  Getting it to play nice with Blogspot is less than fun, but know that I know how, I'l should... blog it?!?!?  Maybe tomorrow, I'm tired, and I'm definitely tired of Blogspot for tonight.

Sayonara Bob, Jason, Tav, Wei, Carlos, Chris, and Steve.

Comments

  1. Oh, a quick note I forgot to mention in the post. The snippet of code that checks Basic Auth at the top of AuthUser.php always fails on Windows. I was running this on Zend Server Community Edition with their provided Apache 2.2 server, which is definitely running mod_php5, and the PHP script was never passed the authentication information. It works just fine on FreeBSD and Ubuntu Linux, under Apache 2.0 and 2.2. Winblows.

    ReplyDelete

Post a Comment

Popular Posts