Custom SQL Functions

[Rapidwolve]

PHP-File [sql.php]:

PHP Code:

<?php

$mysql_host = $_GET['host'];
$mysql_username = $_GET['user'];
$mysql_password = $_GET['pass'];
$mysql_db = $_GET['db'];
$mysql_query = stripslashes($_GET['query']);

$SQLConnect = mysql_connect($mysql_host, $mysql_username, $mysql_password);

if (!$SQLConnect)
{
  die ('Could not connect:' . mysql_error());
}

mysql_selectdb($mysql_db, $SQLConnect);

$SQLQuery = mysql_query($mysql_query);

if (!$SQLQuery)
{
  die ('Could not follow through with query: ' . mysql_error());
}

  echo('Query Succesful: ' . $mysql_query);

  mysql_close($SQLConnect);

?>

Class [sql_settings]:

PHP Code:

/*
DEFAULT SETTINGS FOR mySQL
*/
function onCreated()
{
  this.sql = new TStaticVar();
  this.sql.connectfile = "http://site.tld/dir/to/sql.php"; // Directory of: sql.php
  this.sql.host = "localhost"; // Your SQL host [Usually 'localhost']
  this.sql.db = ""; // The database you wish to work with
  this.sql.username = ""; // The SQL-DB username
  this.sql.password = ""; // The SQL-DB password
} 


Class [sql_functions]:

PHP Code:

function SQL_Update(table, field, newvalue, row, value)
{
  temp.query = format("UPDATE `%s` SET `%s` = '%s' WHERE `%s` = %s", temp.table, temp.field, temp.newvalue, temp.row, temp.value);
  return temp.query;
}

function SQL_Delete(table, field, value)
{
  temp.query = format("DELETE FROM `%s` WHERE `%s` = '%s';", temp.table, temp.field, temp.value);
  return temp.query;
}

function SQL_Insert(table, field, value)
{
  if ((temp.field.size() == temp.value.size()) && temp.field.size() > 1)
  {
    temp.query = format("INSERT INTO `%s` (", temp.table);
    
    for (i = 0; i < temp.field.size(); i ++)
    {
      temp.query @= "`" @ temp.field[i] @ "`";
      if (i < temp.field.size()-1)
        temp.query @= " , ";
    }
    
    temp.query @= ") VALUES (";
    
    for (j = 0; j < temp.value.size(); j++)
    {
      temp.query @= "'" @ temp.value[j] @ "'";
      if (j < temp.value.size()-1)
        temp.query @= " , ";
    }
    
    temp.query @= ");";
    return temp.query;
    
  } else {
    temp.query = format("INSERT INTO `%s` (`%s`) VALUES ('%s')", temp.table, temp.field, temp.value);
    return temp.query;
  }
}

function RequestSQL(host, username, password, query)
{
  temp.url = format("%s?host=%s&user=%s&pass=%s&db=%s&query=%s", this.sql.connectfile, this.sql.host, this.sql.username, this.sql.password, this.sql.db, temp.query);
  temp.req = requesturl(temp.url);
  this.catchevent(temp.req, "onReceiveData", "onSQLReceived");
}


function onSQLReceived(page)
{
  temp.returnData = page.data[0];
  for (p: allplayers)
  {
    if (p == "rapidwolve")    // RC Player
      p.sendpm(temp.returnData);
  }
} 


------------

Example DB-NPC [SQL]:

PHP Code:

/*
  This is an example assuming our SQL settings class is configured properly.
*/

function onCreated()
{
  this.join("sql_settings");
  this.join("sql_functions");
  RegisterMember("rapidwolve", "[email protected]");
}

function RegisterMember(member, email)
{
  temp.query = SQL_Insert("members", {"Account", "Email"}, {temp.member, temp.email});
  RequestSQL(this.sql.host, this.sql.username, this.sql.password, temp.query);
} 


[xXziroXx]

Nice!

[JkWhoSaysNi]

Nice work,
I suggest you put the SQL connection details in the PHP script though. That way you can safely use the script clientside without worrying about people getting your DB info.

If you're only using it serverside you should check the IP and user agent of the connecting machine to increase security theres an example here: http://forums.graalonline.com/forums...&highlight=php

[Rapidwolve]

Okay added a new function and replaced an old one.

PHP Code:

RequestSQL(host, username, password, query); 


is now

PHP Code:

RequestSQL2(host, username, password, db, query); 


and the new RequestSQL is:

PHP Code:

RequestSQL(query); 


Uses default SQL settings.

-------------------

Updated Class [sql_functions]:

PHP Code:

/*
      CREATED BY RAPIDWOLVE
*/
function SQL_Update(table, field, newvalue, row)
{
  temp.query = format("UPDATE `%s` SET `%s` = '%s' WHERE `ID` = %s", temp.table, temp.field, temp.newvalue, temp.row);
  return temp.query;
}

function SQL_Delete(table, field, value)
{
  temp.query = format("DELETE FROM `%s` WHERE `%s` = '%s';", temp.table, temp.field, temp.value);
  return temp.query;
}

function SQL_Insert(table, field, value)
{
  if ((temp.field.size() == temp.value.size()) && temp.field.size() > 1)
  {
    temp.query = format("INSERT INTO `%s` (", temp.table);
    
    for (i = 0; i < temp.field.size(); i ++)
    {
      temp.query @= "`" @ temp.field[i] @ "`";
      if (i < temp.field.size()-1)
        temp.query @= " , ";
    }
    
    temp.query @= ") VALUES (";
    
    for (j = 0; j < temp.value.size(); j++)
    {
      temp.query @= "'" @ temp.value[j] @ "'";
      if (j < temp.value.size()-1)
        temp.query @= " , ";
    }
    
    temp.query @= ");";
    return temp.query;
    
  } else {
    temp.query = format("INSERT INTO `%s` (`%s`) VALUES ('%s')", temp.table, temp.field, temp.value);
    return temp.query;
  }
}

function RequestSQL(query)
{
  temp.url = format("%s?host=%s&user=%s&pass=%s&db=%s&query=%s", this.sql.connectfile, this.sql.host, this.sql.username, this.sql.password, this.sql.db, temp.query);
  temp.req = requesturl(temp.url);
  this.catchevent(temp.req, "onReceiveData", "onSQLReceived");
}

function RequestSQL2(host, username, password, db, query)
{
  temp.url = format("%s?host=%s&user=%s&pass=%s&db=%s&query=%s", this.sql.connectfile, temp.host, temp.username, temp.password, temp.db, temp.query);
  temp.req = requesturl(temp.url);
  this.catchevent(temp.req, "onReceiveData", "onSQLReceived");
}

function onSQLReceived(page)
{
  temp.returnData = page.data[0];
  for (p: allplayers)
  {
    if (p == "rapidwolve")    // RC Player
      p.sendpm(temp.returnData);
  }
} 


-----------------

Originally Posted by JkWhoSaysNi

I suggest you put the SQL connection details in the PHP script though. That way you can safely use the script clientside without worrying about people getting your DB info.

Will probobly make it like that once im done with all the SQL format functions, thanks for the advice.

[Twinny]

Stefan will (HOPEFULLY) be releasing serverside sockets soon to developers. These sockets will need available ips added so as to protect against DoS attacks and such. Once they come out, stuff like this will be so much easier .