Welcome to the Second Life Forums Archive

These forums are CLOSED. Please visit the new forums HERE

Discussion: llHTTPRequest <-> mySQL (PHP)

Lasivian Leandros
Hopelessly Obsessed
Join date: 11 Jul 2005
Posts: 238
08-25-2006 23:18
Here's a freebie for you, tho it's probably not for newbies.

UPDATE: I have gotten this to work, I was sending commands in the wrong format. So it was apparently my fault.

I paid someone to write this for me, but i've never been able to get it to work on my server for wahtever reason, so I figure maybe someone else can get some good out of it. :P

It's supposed to move data back and forth from a mySQL database using llHTTPRequest.

Have fun

CODE
<?php

/**
* Date: Aug 24th, 2006
* Written for: (Lasivian)
* Description: A simple HTTP request storage bin for external Second Life storage.
**/

/**
MySQL 4.0 Database Schema

CREATE TABLE `lslstore` (
`data_key` varchar(64) NOT NULL,
`data_group` varchar(64) default NULL,
`data_value` varchar(255) NOT NULL,
`agent_id` varchar(36) NOT NULL,
`access_time` timestamp NOT NULL,
UNIQUE KEY `dedupe` (`data_key`,`agent_id`),
KEY `group_agent` (`data_group`,`agent_id`),
KEY `key_agent` (`data_key`,`agent_id`)
) TYPE=MyISAM;

**/


/**
* Example Usage:
* Store 1: http://localhost/ls.php?command=store&key=name&value=Josh&group=profile&owner_key=1
* Store 2: http://localhost/ls.php?command=store&key=nick&value=Skiz&group=profile&owner_key=1
* Get: http://localhost/ls.php?command=get&key=name&owner_key=1
* Get Group: http://localhost/ls.php?command=getgroup&group=profile&owner_key=1
* Delete: http://localhost/ls.php?command=delete&key=name&owner_key=1
* Delete Group: http://localhost/ls.php?command=deletegroup&group=profile&owner_key=1
**/

// Database Settings
define('DB_HOST','localhost'); // MySQL Server Host
define('DB_USER','USERNAME'); // MySQL User
define('DB_PASS','PASSWORD'); // MySQL Password
define('DB_NAME','DBNAME'); //The name of the database

//Connect to the database or throw a nasty error
$db = mysql_connect(DB_HOST,DB_USER,DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME,$db) or die(mysql_error());

/**
* Here is the guts of the script. We are going to validate the request
* that was made to see if it was a valid action then call a function
* that will insert or return the data to the user.
*
* If you only want to allow GET or POST methods you can change the
* parameter that is being passed to the command. REQUEST covers
* nearly all request types including get, post, cookies and sessions.
*
* ex: change cmd_store($_REQUEST) to cmd_store($_POST)
**/

switch(strtoupper($_REQUEST['command'])){
case 'STORE':
cmd_store($_REQUEST);
break;
case 'GET':
cmd_get($_REQUEST);
break;
case 'GETGROUP':
cmd_get_group($_REQUEST);
break;
case 'DELETE':
cmd_delete($_REQUEST);
break;
case 'DELETEGROUP':
cmd_delete_group($_REQUEST);
break;
case 'DELETEALL':
cmd_delete_all($_REQUEST);
break;
default:
echo ("Invalid Command Requested");
}

//close the database connection
mysql_close($db);

//stop execution
die();

/**************************************************************************************/

/**
* Database Functionality
* The following functions are passed the full request above and will use that
* request to manage the database and process requests.
**/

/**************************************************************************************/

/**
* Command: STORE
* Usage: Stores a string in the database
* Required Parameters: key, value, group, agent
**/
function cmd_store($params){
// Check for required parameters
$req = array('key','value','group');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "REPLACE INTO lslstore (data_key,data_value,data_group,agent_id, access_time) VALUES ('$key', '$value', '$group', '$owner_id',
NOW())";
$result = mysql_query($sql) or die(mysql_error());
echo 'Store successful.';
}

/**
* Command: GET
* Usage: Retrieves a string from the database
* Required Parameters: key
**/
function cmd_get($params){
// Check for required parameters
$req = array('key');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "SELECT data_value FROM lslstore WHERE data_key = '$key' AND agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
//spit out the results
while($row = mysql_fetch_assoc($result)){
echo $row['data_value']."\n";
}
}

/**
* Command: GET_GROUP
* Usage: Retrieves a set of keys and values for a group
* Required Parameters: key, group
**/
function cmd_get_group($params){
// Check for required parameters
$req = array('group');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "SELECT data_key, data_value FROM lslstore WHERE data_group = '$group' AND agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
//spit out the results in format: key=value
while($row = mysql_fetch_assoc($result)){
echo $row['data_key'].'='.$row['data_value']."\n";
}
}

/**
* Command: DELETE
* Usage: Deletes a key from the database
* Required Parameters: key
**/
function cmd_delete($params){
// Check for required parameters
$req = array('key');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "DELETE FROM lslstore WHERE data_key = '$key' AND agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
echo 'Deleted...';
}

/**
* Command: DELETE_GROUP
* Usage: Deletes a group from the database
* Required Parameters: group
**/
function cmd_delete_group($params){
// Check for required parameters
$req = array('group');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "DELETE FROM lslstore WHERE data_group = '$group' AND agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
echo 'Deleted...';
}

/**
* Command: DELETE_ALL
* Usage: Deletes all entries for a agent/owner
* Required Parameters: (none)
**/
function cmd_delete_all($params){
$owner_id = get_owner_id();
//create and execute the query
$sql = "DELETE FROM lslstore WHERE agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
echo 'Deleted...'; // Tell the world we're deleting data
}

//returns which owner id to use (modify as needed, uses ENV then owner_key if not set)
function get_owner_id(){
if(isset($_SERVER["HTTP_X_SECONDLIFE_OWNER_KEY"])){
return mysql_real_escape_string($_SERVER["HTTP_X_SECONDLIFE_OWNER_KEY"]);
}else{
if(isset($_REQUEST['owner_key'])){
return mysql_real_escape_string($_REQUEST['owner_key']);
}else{
die('You must specify an owner_key parameter');
}
}
}
?>
_____________________
From: someone
"SL is getting to be like a beat up old car with a faulty engine which keeps getting a nice fresh layer of paint added on, while the engine continues to be completely unreliable." - Kex Godel
Nada Epoch
The Librarian
Join date: 4 Nov 2002
Posts: 1,423
Original Thread
08-27-2006 16:59
/15/d1/132811/1.html
_____________________
i've got nothing. ;)
Osgeld Barmy
Registered User
Join date: 22 Mar 2005
Posts: 3,336
08-27-2006 17:33
um im not shure why a broken script is in the library, kinda thought thats the point of having them approved ...
Xixao Dannunzio
Owner, X3D Enterprises
Join date: 20 Mar 2006
Posts: 114
08-27-2006 20:36
From: Lasivian Leandros
...so I figure maybe someone else can get some good out of it.


If someone can fix it and repost, then I see this as being a great addition.
Kalel Venkman
Citizen
Join date: 10 Mar 2006
Posts: 587
08-28-2006 10:18
He didn't say it was broken - he just said HE couldn't get it to work. It might have been a problem with the LSL he was using to talk to it.

The code may be unproven, but not necessarily broken. But I agree that whether unproven or broken, it may not qualify as a library addition.
Lasivian Leandros
Hopelessly Obsessed
Join date: 11 Jul 2005
Posts: 238
08-29-2006 08:17
The code has always "functioned", but as I noted it had not worked on "my" server. Worked on everyone elses just fine.

I was just disclosing that fact so if anyone had trouble they wouldn't pitch a fit.
_____________________
From: someone
"SL is getting to be like a beat up old car with a faulty engine which keeps getting a nice fresh layer of paint added on, while the engine continues to be completely unreliable." - Kex Godel
Kalel Venkman
Citizen
Join date: 10 Mar 2006
Posts: 587
08-30-2006 15:42
It sounds like your server didn't support the necessary version of PHP to run the script you were provided, or certain default permissions for PHP support weren't set. I've run into this problem before, wherein, for example, environment variables were accessible by default in one version but not in the next. Something to look at, anyway.
Tuach Noh
Ignorant Knowlessman
Join date: 2 Aug 2006
Posts: 79
09-04-2006 12:38
This code is very insecure. All I need to know is the URL (which may be guessable and certainly isn't treated like secure information) and your owner key (which is trivial to obtain), and I can destroy your database (or insert/modify damaging info that you will be unable to detect).

At a minimum, the caller should provide a nonce and an MD5 of the salient command parameters concatenated with a "shared secret" using llMD5String() and the PHP side should check it. That's easy to do. Not doing it is lazy and depends on "security through obscurity" (aka the worst kind of security).

Since it uses $_REQUEST, that further means I can put one thing in the query string (which is hopefully logged) and something completely different in a fake cookie (which almost certainly isn't). The cookie will override the query string, and you will never know what really happened. Since the SL HTTP client doesn't allow setting cookies, using this "added flexibility" is all downside with no benefit.

Although it is trivially easy to forge a request with a X-SecondLife-Owner-Key: header, why make it even easier to exploit this by ignoring its absence (even though it should always be present coming from an llHTTPRequest() ) and happily swallowing a fake owner_key=pwned-losers-key from the query string? Why not die an immediate, painful and loud death if the expected headers are missing?

Speaking of loud, where does it log potentially malicious attempts? Nowhere. How does it let you know if there's a problem? It doesn't. There is not one error_log() call anywhere in this script, no matter how badly a potential exploiter is trying to attack it. It doesn't email you if there's something amiss or write a file of IP addresses that maybe it shouldn't be taking requests from. Nothing.

So, sure, use this script if you're just playing around and don't have any serious work to do. But if you care about your data, throw this script away.
Lasivian Leandros
Hopelessly Obsessed
Join date: 11 Jul 2005
Posts: 238
09-06-2006 15:28
From: Tuach Noh
But if you care about your data, throw this script away.


I don't recall ever saying this was a spit-and-polished finished product.

Perhaps you can do the fixes you're mentioning for the good of the users?

Thanks
_____________________
From: someone
"SL is getting to be like a beat up old car with a faulty engine which keeps getting a nice fresh layer of paint added on, while the engine continues to be completely unreliable." - Kex Godel
Tuach Noh
Ignorant Knowlessman
Join date: 2 Aug 2006
Posts: 79
09-07-2006 21:42
I hate to say it but I do not feel that this script is not worth the effort of fixing.

See, however, this post.

The server end of the LSL client framework provided there performs the validation checks I discussed with the exception of MD5'ing the data with a secret to control access. (Currently, only owner and object IDs can be used to control access, and the assumption is made that LL's servers cannot be subverted to falsify this information.)

If I have the opportunity, I will extend this to support arbitrary scopes, and access to those will be controlled by an MD5-hashed shared secret in a manner similar to the one I described here.

In the mean time, my version provides essentially the same functionality as the script above, but with a free server, better use of the HTTP protocol, and the ability to be extended in the future.

Also, if someone wants to provide an alternative server before I get around to releasing the one I wrote (which might be a long time), the provided LSL client library and request format is not dependent on a particular server implementation or technology.
Lasivian Leandros
Hopelessly Obsessed
Join date: 11 Jul 2005
Posts: 238
09-15-2006 00:07
Tuach, I appreciate the effort, however my end result was to have a script that was "plug and play" for people who have zero PHP knowledge :/
_____________________
From: someone
"SL is getting to be like a beat up old car with a faulty engine which keeps getting a nice fresh layer of paint added on, while the engine continues to be completely unreliable." - Kex Godel
Tuach Noh
Ignorant Knowlessman
Join date: 2 Aug 2006
Posts: 79
09-15-2006 16:42
Since the script I posted involves no PHP, you should be home free then. :)
shiney Sprocket
Registered User
Join date: 24 Jan 2006
Posts: 254
09-20-2006 23:45
Just wanted to pipe up and thank you for posting this. It has saved me time as a base code to work from. It does what I want, but just needs the added security.

In regards to throwing this script away, what a bad idea! I'm sure others will find this useful even though unsecure.

As stated, add some md5, sprinkle a little salt and tada! Much more secure :P
Tuach Noh
Ignorant Knowlessman
Join date: 2 Aug 2006
Posts: 79
09-22-2006 17:51
From: shiney Sprocket
In regards to throwing this script away, what a bad idea! I'm sure others will find this useful even though unsecure.


It's learning from a bad example that's a bad idea.

If you want a turnkey solution that includes PHP source that you can learn from and extend, use Zero Linden's Silo. That's what it's for and unlike this example, it's well-written.
Clayton Cinquetti
Registered User
Join date: 17 May 2005
Posts: 38
12-31-2006 15:31
Lasivian Leandros has posted a very interesting and useful piece of code. At first I didn't pay much attention to it because it got so much grief above, but once I realized what it was trying to do I realized it was pretty clever.

The complaints are about it not being secure -- which is entirely true. This is php code that allows you to completely access and modify any data in the created mysql table just by typing in different values into your URL section of your web browser. Obviously this would be dangerous for production code, but that is not what this is. This is demo code which provides an example of how to parse responses sent from a client and use that parsed data to store, retreive, and delete data from a database.

If you understand this code and the LSL code that works with it. You pretty much have the tools you need to do most basic LSL to database applications. All you have to do is make the application more secure.

I have step by step instructions on my experimentation with this code on my blog at www.secondlifehowto.com. I use it to set the record the state of an in-world object on the server so that any one with a web browser can view the state of that object. I also have the in world object retrieve a status from the server and display it in world on hovering text. Here's the lsl portion of the code. The PHP side is pretty much the same as above:

CODE

// Demo of lsl -> php -> mySQl by Clayton Cinquetti 12/31/06
// Built on wiki code by Keknehv Psaltery

key requestOfServerStatusRequestid;
key saveOfObjectStatusRequestid;
string myStatus="status0";
string serverStatus="no_status_received";
string serverId = "936dc6c6-0b8c-6521-d1b9-1dc845c394b9"; // Any number will do here.
// it doesn't have to be linked to a second life key, I just chose one because it's a
// nice unique number.
key agentId;

default
{
// On startup this object will display hovering text that will indicate it's status
// and the server status.
state_entry()
{
llSetText("My status: "+myStatus+" Server Status: "+serverStatus, <1,0,0>, 1.0);
agentId = llGetKey();
llSay(0, "Object Key is:"+(string)agentId);
llSetTimerEvent(30);

}

// When touched it updates the status and sends it to the database on the server.
// It also requests the server status
touch_start(integer number)
{
// Change the status on each touch
if(myStatus == "status0")
{
myStatus = "status1";
}
else
{
myStatus = "status0";
}
llSetText("My status: "+myStatus+" Server Status: "+serverStatus, <1,0,0>, 1.0);

// Now send the new status to the server
// Create the url command that stores the status of this object into database under the heading of this
// objects key
string urlForStoringStatus = "http://www.secondlifehowto.com/MySqlExample1.php?command=store&key=objectStatus&value="
+myStatus+"&group=objectState&owner_key="+(string)agentId;

saveOfObjectStatusRequestid = llHTTPRequest(urlForStoringStatus,[HTTP_METHOD,"GET"],"");

// To view the data just stored open up a browser and enter the following url.
// replace the owner key value with the value of the key of the object this
// script is placed in (the one said on script startup)
// http://www.secondlifehowto.com/MySqlExample1.php?command=get&key=objectStatus&owner_key=REPLACE_THIS_WITH_OBJECT_KEY
// make sure you refresh your browser view of the page each time or it will be out of date


}

// The responses to our http requests are processed here
http_response(key request_id, integer status, list metadata, string body)
{
if (request_id == saveOfObjectStatusRequestid)
{
// The response to save of status should be "Store successful."
llWhisper(0, "Response to store attempt: " + body);
}
else if (request_id == requestOfServerStatusRequestid)
{
// The response to server status request is the server status
llWhisper(0, "Response to server status request: " + body);
serverStatus = body;
llSetText("My status: "+myStatus+" Server Status: "+serverStatus, <1,0,0>, 1.0);
}
else
{
llSay(0,(string)status+" error");
}
}

// Every time the timer expires check the status of the server
timer()
{
llWhisper(0,"requesting server status");

// Now Create an url command that will retreive the server status which is saved under key 123456789
string urlForRetreivingServerStatus = "http://www.secondlifehowto.com/MySqlExample1.php?command=get&key=serverStatus&owner_key="+
(string) serverId;

requestOfServerStatusRequestid = llHTTPRequest(urlForRetreivingServerStatus,[HTTP_METHOD,"GET"],"");

// Note: To change the status of the server to for example "server_is_functioning" enter the
// following from a web browser
// http://www.secondlifehowto.com/MySqlExample1.php?command=store&key=serverStatus&value=server_is_functioning&group=serverState&owner_key=936dc6c6-0b8c-6521-d1b9-1dc845c394b9
// Note: The above is a real link, so only use this as an example.
// If you want to use my server and experiment with setting status, change to owner_key
// to something unique.

}

}
Breen Whitman
Registered User
Join date: 10 Jan 2007
Posts: 20
1 chromosone too many?
03-25-2007 22:58
From: Tuach Noh
This code is very insecure. All I need to know is the URL (which may be guessable and certainly isn't treated like secure information) and your owner key (which is trivial to obtain), and I can destroy your database (or insert/modify damaging info that you will be unable to detect).


Tuach, you are a cretin.

You assume one is sending to a server that performs no input checking at all.
I suggest you read up on performing such input checking on the php and mysql sites.

Going by your logic, one only needs the hostname of a database to destroy it.

LOL.
Sys Slade
Registered User
Join date: 15 Feb 2007
Posts: 626
03-26-2007 05:21
Did you read the code Breen?

CODE
     function cmd_delete_all($params){
$owner_id = get_owner_id();
//create and execute the query
$sql = "DELETE FROM lslstore WHERE agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
echo 'Deleted...'; // Tell the world we're deleting data
}

function get_owner_id(){
if(isset($_SERVER["HTTP_X_SECONDLIFE_OWNER_KEY"])){
return mysql_real_escape_string($_SERVER["HTTP_X_SECONDLIFE_OWNER_KEY"]);
}else{
if(isset($_REQUEST['owner_key'])){
return mysql_real_escape_string($_REQUEST['owner_key']);
}else{
die('You must specify an owner_key parameter');
}
}
}

All anyone need do where this code is running is call the address like so:
http://whatever.com/ls.php?command=deleteall&owner_key=xxxx

Getting the owner key is trivially simple, using scanners, touches or something like the w-hat name2key service.

We know for a fact that this script does not perform proper validation, because we have the source code. How exactly do you propose that PHP does input validation if not in the source code?

All one needs is the hostname and owner key to destroy the database associated with this script. Your logic is flawed. Just because this script is insecure does not mean all scripts everywhere are insecure.

For everyone else, here's some code that should help verify that the call is at least coming from in world:

CODE
<?php
$dom=gethostbyaddr_timeout($_SERVER['REMOTE_ADDR']);
$ip=gethostbyname($dom);
if(!preg_match('`^.*\.lindenlab\.com$`',$dom) || $ip!=$_SERVER['REMOTE_ADDR'])
echo 'invalid address';
else
echo 'valid address';

function gethostbyaddr_timeout($ip,$timeout=2){
$host=`host -W $timeout $ip`;
if(preg_match('`in-addr.arpa domain name pointer (.*)\.\n$`i',$host,$matches))
$host=$matches[1];
else
$host=$ip;
return $host;
}

?>


This basically checks that the script is being called by a server that identifies itself as *.lindenlab.com, then does a lookup on that name to verify the IP is the correct one.
A spoofer could alter their reverse dns to give a lindenlab.com subdomain, but performing a hack on the DNS records for lindenlab.com would be a lot harder (but not impossible).

gethostbyaddr_timeout is used to guarantee a return within a set time (useful for hosts with broken reverse DNS).
If your server does not support the host command, replace it with gethostbyaddr($_SERVER['REMOTE_ADDR']);
The host version on your server may also return results slightly differently, so the function may need adjustments. This is what works on my debian servers.

Replace the "valid address" line with whatever code you want to do.
Manifold Destiny
Registered User
Join date: 16 Nov 2007
Posts: 27
01-30-2008 15:03
From: Tuach Noh
It's learning from a bad example that's a bad idea.

If you want a turnkey solution that includes PHP source that you can learn from and extend, use Zero Linden's Silo. That's what it's for and unlike this example, it's well-written.


I suppose learning from bad code is better than learning from a URL that doesn't exist (anymore?) Got a URL that works?

BTW: thanks to the person who posted the original script here!
Alicia Sautereau
if (!social) hide;
Join date: 20 Feb 2007
Posts: 3,125
01-30-2008 15:20
i personally like the xtea encryption, encrypt everything and with some tinkering it really gets like a second nature to send vast amounts of data trough it :)

/15/68/185634/1.html

hmm d`oh, this thread is ancient :p
_____________________
Anti Destiny
Registered User
Join date: 4 Jan 2008
Posts: 2
Zero Linden's Silo - Data storage via llHTTPRequest
01-30-2008 17:21
From: Manifold Destiny
I suppose learning from bad code is better than learning from a URL that doesn't exist (anymore?) Got a URL that works?


Muhahaha.. you so funny. =^.^=

You know, bad habits are hard to break.

Zero Linden's Silo - Data storage via llHTTPRequest

/54/69/119570/1.html

http://www.notabene-sl.com/misc/silo-README.txt