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);
    }
  }
}

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;

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):

// 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";
}

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

About Gwyneth Llewelyn

I'm just a virtual girl in a virtual world...

  • Aleena

    Please never concatenate your SQL strings. Even if you feel it doesn’t matter because your url is not supposed to be called by 3rd parties, it’s a terrible habit to have. Using prepared statements is 100% safer and completely trivial to do. pdo.prepare documentation.

    
    $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)
    {
    }
    
  • Thanks, Aleena! You’re quite right; before PDO, I used to place a gazillion checks along the strings, but, after PDO, I tend to be careless…

    I have edited the code as per your suggestions.

  • dawsey chatterbox

    Dawsey Chatterbox just signing in to say “thanks!”. Here I was reusing some old code I had been playing with and now I’ll be able to use your example to add users to a forum all being well …Thanks!

  • waters

    You have an error .. The In-World script calls for registerAvatar.php .. yet you named it avatarRegister above. An easy fix, but a problem none the less.
    Also, how about a page to read that stuff i just added to the database?

  • Nicely spotted, @waters 🙂 Thank you very much! I’ve updated the text.

    As for a page to show what’s on the database, just use phpMyAdmin 🙂

  • waters33637

    OK … Better question …. I would like to location to be clickable on the web page. So it loads a slurl or something …. What would i change? The $_SERVER[‘HTTP_X_SECONDLIFE_REGION’]

  • This is the code I use on my WP plugins to create a SLURL to an object. There might be easier/more efficient ways of accomplishing the same thing…


    // parse name of the region and location coordinates to create a link to maps.secondlife.com

    $regionName = substr($_SERVER['HTTP_X_SECONDLIFE_REGION'], 0, strpos($_SERVER['HTTP_X_SECONDLIFE_REGION'], "(") - 1);

    $coords = trim($_SERVER['HTTP_X_SECONDLIFE_LOCAL_POSITION'], "() tnr");

    $xyz = explode(",", $coords);

    printf('<a href="http://maps.secondlife.com/secondlife/%s/%F/%F/%F?title=%s" target="_blank">%s (%d,%d,%d)</a>', $regionName, $xyz[0], $xyz[1], $xyz[2], $_SERVER['HTTP_X_SECONDLIFE_OBJECT_NAME'], $regionName, $xyz[0], $xyz[1], $xyz[2]);

  • Estelle Pienaar

    I think that the php script isn’t complete. I don’t see the opening php-statement and no logging into the server. Maybe it got cut when you updated it? Could you have a look?

    I was also wondering if the for-loop in the touch event of the lsl script is necessary? I don’t see an additional benefit?

    Thanks a lot for posting the php code. There are no examples in the net with recent php code, only outdated php4. As I am not usually coding in php, this is super helpful for me.

  • Thank you, @Estelle Pienaar 🙂 The stupid code-formatting plugin I use sometimes eats up the start/finish tags. Worse than that, when there is a WP update, it tends to eat up those tags again. I thought I had fixed it, but I didn’t. In fact, I don’t even see the code correctly coloured, it shows only as black on a criss-crossed background. Oh well. Something to do when I’ve got time!

    The for-loop is just necessary if more than one person touches the cube simultaneously. Catching that case is supposed to be standard LSL practice. But you can simplify the code if you’re sure that it will never happen that two people touch the cube exactly at the same time.

    It’s funny that you noticed that this wasn’t PHP4, I was thinking to myself, ‘uh, but it should work on PHP4 too’ when I noticed that I’m using try/catch and PDO, which are certainly the thing to do under PHP5+ 🙂 Hehe…

  • Gah. This needs fixing again. Sorry. It’s really hard to stop WordPress from adding a lot of garbage on the code… and I’m in a hurry now and need to leave!

  • My apologies while trying to fix the code so that it can be properly published. WordPress is misbehaving dramatically, adding all sorts of tags and doing replacements where they don’t exist in the original article! I might need to go deep into the database and add the code by hand, since clearly the WordPress visual editor is seriously messing this up…

    If you’re a seasoned LSL/PHP programmer, you should easily spot where WordPress added the garbage and remove it manually. If not, well, be patient, or contact me in-world or through my email address (see my about page for the contact).

  • Estelle Pienaar

    No problem, I am in no hurry with my project. it’s great that you have reacted so fast on a Sunday! I tried to fix the html tags in the code myself, but I didn’t get it running yet. Will check back in the next days. 🙂

  • It seems that I managed to get one plugin correctly formatting the code. Yay o/

    Unfortunately, the PHP tags are something I cannot place inside a WordPress post…