Registering users on a website

Dawsey Chatterbox, posting a comment on the page for my SL User Create WordPress plugin, asked for a simpler version of the code which would merely register an avatar name to a MySQL database. He has been patient, having been waiting for almost a year 🙂 Here goes the code for the three components: the in-world script (to be placed inside an item that you can touch), a very simple schema for a MySQL table, and a PHP script to be placed at the backend of your server. I’m assuming PHP 5.X with the PDO libraries active (you should have them on by default since it’s what PHP is now supposed to be supporting in exclusivity).

This merely registers an avatar name and its key to an external database. To illustrate a point, the location of the object is also extracted from the headers sent by the SL object. And to see that it is really updating the database if you touch on the same object repeatedly, a timestamp gets updated each time by the MySQL engine. There is no functionality to actually view the database; you can use phpMyAdmin for that.

I hope this might be useful for some.

In-world script

Just copy it to a script and place it inside an object in Second Life.

// Code by Gwyneth Llewelyn
// Touch to get your avatar name registered remotely

// Global Variables
key avatarKey;
string avatarName;
key registrationResponse; // to send the PermURL to the blog
key webResponse; // to send periodic updates to the blog
string http_host = "my.hostname.tld";
string url; 

default
{
  state_entry()
  {
    llSetText("Touch to get registered with " + http_host, <1.0,1.0,1.0>, 1.0);
  }
  on_rez(integer startParam) {
    llResetScript();
  }
  changed(integer what)
  {
    if (what & (CHANGED_OWNER | CHANGED_REGION | CHANGED_REGION_START | CHANGED_TELEPORT))
      llResetScript();
  }
  touch(integer howmany)
  {
    integer i; 

    for (i = 0; i < howmany; i++)
    {
      avatarName = llDetectedName(i);
      avatarKey = llDetectedKey(i);
      llSetText("Sending registration for " + avatarName + "...",
        <1.0,0.0,0.0>, 1.0); // call the site with data

      string message = "action=register" + "&avatarName=" + llEscapeURL(avatarName)
        + "&avatarKey=" + llEscapeURL(avatarKey);
      registrationResponse = llHTTPRequest("http://" + http_host + "/registerAvatar.php", 
        [HTTP_METHOD, "POST", HTTP_MIMETYPE, "application/x-www-form-urlencoded"], message);
    }
  }
  // Catching reply from web server
  http_response(key request_id, integer status, list metadata, string body)
  {
    if (request_id == registrationResponse)
    {
      if (status == 200)
      {
        llOwnerSay("Avatar registration sent to gateway! Msg. id is " + body);
      }
      else if (status == 499)
      {
        llOwnerSay("Timeout waiting for gateway! Your PermURL might still be sent, please be patient");
      }
      else
      {
        llOwnerSay("PermURL NOT sent. Status was " + (string)status + "; error message: " + body);
      }
      llSetText("Touch to get registered with " + http_host, <1.0, 1.0, 1.0>, 1.0);
    }
  }
}Code language: LSL (Linden Scripting Language) (lsl)

Database setup

Create a database table on your MySQL server with the following code:

CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE IF NOT EXISTS avatars (
  avatarKey char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  avatarName varchar(128) NOT NULL,
  location varchar(128) NOT NULL,
  timeWhen timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (avatarKey)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;Code language: SQL (Structured Query Language) (sql)

Webserver setup

On your webserver, create a file named registerAvatar.php (you need to place the code between the PHP tags, because unfortunately I cannot get these to display properly inside WordPress):

<?php
// change these to match your database
define('DB_HOSTNAME', "localhost");
define('DB_DATABASE', "test");
define('DB_USERNAME', "myusername");
define('DB_PASSWORD', "mypassword"); 

// Code starts here
if (isset($_SERVER['HTTP_X_SECONDLIFE_OBJECT_KEY']))
{
  // store entry on MySQL database
  try
  {
    $db = new PDO('mysql:host=' . DB_HOSTNAME . ';dbname=' . DB_DATABASE,
        DB_USERNAME, DB_PASSWORD);
  }
  catch(PDOException $e)
  {
    header("HTTP/1.0 503 Service Unavailable");
    printf("Connect failed: %s\n", $e->getMessage());
    exit();
  }
  if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'register')
  {
    $query = $db->prepare("REPLACE INTO avatars (avatarKey, avatarName, location) VALUES (:avatarKey, :avatarName, :secondlifeRegion);");
    try
    {
      $query->execute(array(':avatarKey' => $_REQUEST['avatarKey'],
        ':avatarName' => $_REQUEST['avatarName'],
        ':secondlifeRegion' => $_SERVER['HTTP_X_SECONDLIFE_REGION']));
    }
    catch(PDOException $e)
    {
      header("HTTP/1.0 503 Service Unavailable");
      printf("REPLACE query failed: '%s'\n", $e->getMessage());
      exit();
    }
    // The following will send info back to SL
    header("HTTP/1.0 200 OK");
    header("Content-type: text/plain; charset=utf-8");
    echo "Avatar " . $_REQUEST['avatarName'] . " (" . $_REQUEST['avatarKey'] .
      ") updated at location " . $_SERVER['HTTP_X_SECONDLIFE_REGION'];
  }
}
else
{
  header("HTTP/1.0 405 Method Not Allowed");
  echo "Request did not come from Second Life/OpenSimulator";
}Code language: HTML, XML (xml)

Thanks to Aleena for some important security fixes and to waters for catching a typo.