<?php
/*
* SOAP SQL Library
* Created: Apr 18, 2007
* Modified: Aug 30, 2007
* License: LGPL (open source)
* BungeeLabs, Inc.
*
* This library provides an easy way to present SQL database queries as a web service.
* For information on how to use this library, look at "docs/index.html"
*
* Dependencies:
* NuSOAP at http://dietrich.ganx4.com/nusoap
* PEAR MDB2 at http://pear.php.net/package/MDB2
*
* See INSTALLATION for details on how to setup MDB2 and SOAPSQL.
*/
require_once("nusoap/nusoap.php");
require_once("MDB2.php");
/**
* Global variable set by the constructor for the auto-created callback functions to access the class instance
*/
$ptr;
/**
* This class is designed to be used as a help to export an SQL database as a Web Service.
* The web service is defined by creating WSDL functions that are tied to SQL queries.
* Features include:
* 1) Database type agnostic
* 2) Supports database queries (selects) and modify-style web service functions
* 3) Capability to auto-generate WSDL callback functions, or use custom functions.
* 4) Automatically quotes and escapes SQL input to help protect against injection attacks.
* 5) Works with PHP 4 and 5
*/
class soapsql
{
/**
* Web service namespace
* @access private
*/
var $wsdlns;
/**
* DB connection
* @access private
*/
var $db;
/**
* NuSOAP sreference
* @access private
*/
var $soapserver;
/**
* The security token for this service.
* @access private
*/
var $securityToken = '';
/**
* Web service namespace
* @access private
*/
var $debug = false;
/**
* Web service namespace
* @access private
*/
var $debugStr = '';
/**
* Web service namespace
* @access private
*/
var $errorStr = '';
/**
* Constructor
*/
function soapsql() {
}
/**
* Registers the web service and connects to the specified database.
*
* @param string $wsdlname The name of this web service
* @param string $wsdlns The name space of this web service (e.g 'http://my.site.com/ws')
* @param string $dbtype The type of the database (e.g. 'mysql' or 'pgsql')
* @param string $dbuser The name of the database user
* @param string $dbpass The password of the database user
* @param string $dbhost The hostname of the database
* @param string $dbname The name of the database to access
* @return error string, or true if no error occurred. Use isError() and getError() to deal with the error.
* @access public
*/
function register($wsdlname, $wsdlns, $dbtype, $dbuser, $dbpass, $dbhost, $dbname) {
$this->clearError();
if (!isset($wsdlname) || !isset($wsdlns) || !isset($dbtype) || !isset($dbuser) ||
!isset($dbpass) || !isset($dbhost) || !isset($dbname)) {
return setError('All params to register must be set');
}
$this->wsdlns = $wsdlns;
//Init the DB
$dburl = "$dbtype://$dbuser:$dbpass@$dbhost/$dbname";
$this->debug("Connecting to database at: $dbtype://$dbuser@$dbhost/$dbname");
$this->db = MDB2::connect($dburl);
if (PEAR::isError($this->db)) {
return $this->setError($this->db->getMessage());
}
$this->db->setFetchMode(MDB2_FETCHMODE_ASSOC);
$this->debug("Connected to database successfully");
// Init the soap server
$this->debug("Initializing the soap server");
$this->soapserver = new soap_server();
$this->soapserver->configureWSDL($wsdlname, $wsdlns, false, 'document');
if ($err = $this->soapserver->getError()) {
return $this->setError($err);
}
$this->soapserver->wsdl->schemaTargetNamespace = $wsdlns;
$soapserver->debug_flag = $this->debug;
$this->debug("Soap server initialized successfully");
//Save $this for our automatic callback functions built with eval()
global $ptr;
$ptr = $this;
return true;
}
/**
* Sets a security token for this web service. Each call to this service must have the
* correct token passed as a parameter in the $securityParams structure.
* This should be at least 10 characters in length, but preferably 30+.
* ***NOTE: Setting a security token will automatically prepend a $securityParams parameter on each
* WSDL function created. This API must be called before creating the WSDL functions.
*
* IMPORTANT: this security feature should be coupled with much stronger
* security measures such as SSL and an authentication protocol (i.e. basic auth)
* provided by the web server. It is a good idea to do manual data sanitation (SQL injection scrubbing, etc)
* on incoming parameters using custom WSDL callbacks.
* @param $securityToken string The security token to set.
* @access public
*/
function setSecurityToken($securityToken) {
if (!isset($securityToken) || strlen($securityToken) < 10)
return $this->setError('Security token parameter must be set and length >= 10 ');
$this->securityToken = $securityToken;
$this->soapserver->wsdl->addComplexType(
'SecurityParams',
'complexType',
'struct',
'all',
'',
array('securityToken' => array('name' => 'securityToken', 'type' => 'xsd:string'))
);
$this->debug('Set security token of length '.strlen($securityToken));
}
/**
* Returns the DB connection. Useful when using custom WSDL callbacks.
* @return MDB2::connection The PEAR MDB2 connection.
* @access public
*/
function &getDBConnection() {
return $this->db;
}
/**
* Adds a web service operation (function) that queries the database (such as using a 'select' SQL statement).
* The WSDL callback is automatically generated.
* If a security token is set, the first parameter of the callback function will be a $securityParams structure
* containing a variable $securityToken as a string.
* NOTE: see http://www.w3.org/TR/xmlschema-2/#dt-built-in for xsd datatypes for in/out parameters.
*
* Three WSDL structures will be automatically created, called <functionName>RequestType, <functionName>ResponseType, and <functionName>QueryResultType.
* (e.g. MyQueryRequestType, etc). These structures define how the WSDL expects to be called, and it's return value.
* The <functionName>ResultType is a structure containing the $outParams passed to this call.
*
* @param string $functionName The name of the web service function.
* @param array $inParams An associative array of in parameter names and xsd types (e.g. array('name' => 'xsd:string')).
* These parameters will be available as PHP variables to use in your SQL query (e.g. $name).
* @param array $outParams An associative array of out parameter names and xsd types (e.g. array('name' => 'xsd:string', 'age' => 'xsd:int'))
* The return value of the function will be an array of these types.
* @param string $sqlQuery A string containing the SQL query that this function will perform.
* IMPORTANT: the column names and order of the resulting query must match EXACTLY
* with the $outParams array. Some experimentation may be necessary.
* @param boolean $autoGenCallback Set to true (default) to allow the WSDL callback function to be auto-generated. False, to use a custom function for more flexibility.
* If false, a function with the same name as $functionName and parameters as $inParams must be created manually.
* For example code, see getSQLQueryResult().
* @param boolean $singleRow Optional (default is false). Set to true if the result of the SQL query will ALWAYS return one row only. This will make the return type singular.
* @return boolean true if no error occurred, or a string containing the error. Use isError() to detect if an error occurred and getError() to read the error.
* @access public
*/
function addQueryWSDLOperation($functionName, $inParams, $outParams, $sqlQuery, $autoGenCallback=true, $singleRow=false) {
$this->clearError();
if (!isset($functionName) || !isset($inParams) || !isset($outParams)|| !isset($sqlQuery)) {
return $this->setError('Missing parameters to createQueryWSDLFunction');
}
$p = stripos($sqlQuery, 'insert');
if ($p != false && $p == 0) {
return $this->setError('Insert statements cannot be used with createQueryWSDLFunction');
}
$p = stripos($sqlQuery, 'update');
if ($p != false && $p == 0) {
return $this->setError('Update statements cannot be used with createQueryWSDLFunction');
}
$this->debug("Creating query-type WSDL function: $functionName");
// Construct the callback function
$sqlQuery = str_replace('"', '\\"', $sqlQuery);
$useSecurityToken = false;
if (isset($this->securityToken) && $this->securityToken != '') {
$useSecurityToken = true;
$inParams = array_merge(array('securityParams' => 'tns:SecurityParams'), $inParams);
}
//Build inParamStr and escape all input parameters
$inParamStr = '';
$sqlEscapeParams = '';
foreach ($inParams as $name => $type) {
if ($inParamStr != '') {
$inParamStr .= ', ';
}
$inParamStr .= '$'.$name;
$sqlEscapeParams .= ' $'.$name.' =& $ptr->escapeSQLParam($'.$name.', \''.$type.'\');';
}
if ($autoGenCallback) {
$code = 'function '.$functionName.'('.$inParamStr.') {'.
' global $ptr;' .
($useSecurityToken ?
' if (!$ptr->verifySecurity($securityParams)) {' .
' return $ptr->getError();' .
' }' : '').
$sqlEscapeParams.
' return $ptr->getSQLQueryResult("'.$sqlQuery.'", '.count($outParams).'); '.
'}';
$err = $this->createFunc($code);
if ($err != '')
return $this->setError($err);
}
if (!$singleRow) {
//Create the array result structure
$structName = $this->createArrayResultType($functionName, $outParams);
//Wrap the out parameters in an array
$outParams = array('return' => 'tns:ArrayOf'.$structName);
}
$this->soapserver->register($functionName, $inParams, $outParams, $this->wsdlns, false, 'document');
if ($err = $this->soapserver->getError())
return $this->setError($err);
return true;
}
/**
* Adds a web service operation (function) that modifies the database (such as using an 'insert' or 'update' SQL statement).
* The WSDL callback is automatically generated if [$autoGenCallback] is left as true.
* The resulting out parameters are: array('success'=>'xsd:boolean', 'error'=>'xsd:string', 'affectedRows'=>'xsd:int', 'generatedId'=>'xsd:string');
* Where 'success' is true if successful, 'error' contains an error string if an error occurred, 'affectedRows' is the number of changed rows, and
* 'generatedId' is the id of a new created row if 'insert' was used, otherwise it will be an empty string.
* NOTE: see http://www.w3.org/TR/xmlschema-2/#dt-built-in for all available XSD datatypes for in/out parameters.
*
* Three WSDL structures will be automatically created, called <functionName>RequestType, <functionName>ResponseType, and <functionName>QueryResultType.
* (e.g. MyQueryRequestType, etc). These structures define how the WSDL expects to be called, and it's return value.
*
* @param string $functionName The name of the web service function.
* @param array $inParams An associative array of in parameter names and xsd types (e.g. array('name' => 'xsd:string')).
* These parameters will be available as PHP variables to use in your SQL query (e.g. as $name) and
* automatically quoted and escaped (helping to prevent SQL injection).
* @param string $sqlQuery A string containing the SQL query that will modify the database.
* @param boolean $autoGenCallback Set to true (default) to allow the WSDL callback function to be auto-generated. False, to use a custom function for more flexibility.
* If false, a function with the same name as $functionName and parameters as $inParams must be created manually.
* For example code, see getSQLModifyResult(). Also, you must escape each input with a call like:
* @return boolean true if no error occurred, or a string containing the error. Use isError() to detect if an error occurred and getError() to read the error.
* @public
*/
function addModifyWSDLOperation($functionName, $inParams, $sqlQuery, $autoGenCallback=true) {
$this->clearError();
if (!isset($functionName) || !isset($inParams) || !isset($sqlQuery)) {
return $this->setError('Missing parameters to createModifyWSDLFunction');
}
$p = stripos($sqlQuery, 'select');
if ($p != false && $p == 0) {
return $this->setError('Select statements cannot be used with createModifyWSDLFunction');
}
$this->debug("Creating modify-type WSDL function: $functionName");
// Construct the callback function
$sqlQuery = str_replace('"', '\\"', $sqlQuery);
$useSecurityToken = false;
if (isset($this->securityToken) && $this->securityToken != '') {
$useSecurityToken = true;
$inParams = array_merge(array('securityParams' => 'tns:SecurityParams'), $inParams);
}
//Build inParamStr and escape all input parameters
$inParamStr = '';
$sqlEscapeParams = '';
foreach ($inParams as $name => $type) {
if ($inParamStr != '') {
$inParamStr .= ', ';
}
$inParamStr .= '$'.$name;
$sqlEscapeParams .= ' $'.$name.' =& $ptr->escapeSQLParam($'.$name.', \''.$type.'\');';
}
$outParams = array('success'=>'xsd:boolean', 'error'=>'xsd:string', 'affectedRows'=>'xsd:int', 'generatedId'=>'xsd:string');
if ($autoGenCallback) {
$code = 'function '.$functionName.'('.$inParamStr.') {'.
' global $ptr;' .
($useSecurityToken ?
' if (!$ptr->verifySecurity($securityParams)) {' .
' return $ptr->getError();' .
' }' : '').
$sqlEscapeParams.
' return $ptr->getSQLModifyResult("'.$sqlQuery.'"); '.
'}';
$err = $this->createFunc($code);
if ($err != '')
return $this->setError($err);
}
$this->soapserver->register($functionName, $inParams, $outParams, $this->wsdlns, false, 'document');
if ($err = $this->soapserver->getError())
return $this->setError($err);
return true;
}
/**
* Escapes a variable for use in an SQL statement.
* IMPORTANT: This API should be called for each input to an SQL statement to properly format the statement,
* and to protect against SQL injection attacks.
*
* Currently supported types are: xsd:string, xsd:int/integer, xsd:float/double, xsd:boolean, xsd:time, xsd:dateTime.
* You can also use the simple database types: text, integer, float, decimal, boolean, time, dateTime.
* This function could be improved to convert similar data types between xsd and database (xsd:base64Binary => BLOB, etc).
* See http://www.w3.org/TR/xmlschema-2/#dt-built-in for and exhaustive list of XSD types, and
* see http://cvs.php.net/viewcvs.cgi/pear/MDB2/docs/datatypes.html?view=co for the list of DB types.
*
* @param string $param The parameter from the WSDL callback to be used in a SQL statement. If the param is empty, the
* string NULL is returned.
* @param string $xsdType The xsd type passed in. This will be mapped to the appropriate db variable type.
* @access public
*/
function &escapeSQLParam($param, $xsdType) {
if (!isset($param)) {
$e = $this->setError('Param must be set to escape');
return $e;
}
$type = 'text';
if (!isset($xsdType) || $xsdType == 'xsd:string' || $xsdType == 'string') {
$type = 'text';
} else
if ($xsdType == 'xsd:int' || $xsdType == 'xsd:integer' ||
$xsdType == 'xsd:long' || $xsdType == 'xsd:unsignedLong' ||
$xsdType == 'xsd:short' || $xsdType == 'xsd:byte') {
$type = 'integer';
} else
if ($xsdType == 'xsd:float') {
$type = 'float';
} else
if ($xsdType == 'xsd:double') {
$type = 'decimal';
} else
if ($xsdType == 'xsd:boolean' || $xsdType == 'boolean') {
$param = (isset($param) &&
($param == '1' || stricmp($param, 'true') == 0)) ? '1' : '0';
$type = 'boolean';
} else
if ($xsdType == 'xsd:time') {
$type = 'time';
} else
if ($xsdType == 'xsd:dateTime') {
$type = 'timestamp';
}
if ($xsdType == 'integer' || $xsdType == 'float' ||
$xsdType == 'decimal' || $xsdType == 'time' ||
$xsdType == 'dateTime') {
$type = $xsdType;
}
$this->debug("Param before escape: ".$this->varDump($param));
$param = $this->db->quote($param, $type);
$this->debug("Param after escape : ".$this->varDump($param));
return $param;
}
/**
* Returns an array from an SQL statement that modified the database,
* appropritate for the WSDL callback format.
* @param string $sqlQuery The SQL query to perform.
* @return array Associative array containing the result of the modify statement.
* @access public
*/
function &getSQLModifyResult($sqlQuery) {
$this->clearError();
$success = true;
$error = '';
$generatedId = '';
$affected =& $this->db->exec($sqlQuery);
if (PEAR::isError($affected)) {
$success = false;
$error = $this->setError($affected->getMessage());
$affected = 0;
} else if (stripos($sqlQuery, 'insert') == 0) {
$generatedId = $this->db->lastInsertID();
}
$res = array("success" => $success, "error" => $error,
"affectedRows" => $affected,
"generatedId" => $generatedId ? "$generatedId" : '');
if ($this->debug) {
$this->debug($this->varDump($res));
}
return $res;
}
/**
* Returns an array from an SQL query appropriate for the WSDL callback format.
*
* @param string $sqlQuery The SQL query to perform.
* @param array/boolean Specifies the out parameters for additional error checking.
* @return array Array containing all of the output rows.
* @access public
*/
function &getSQLQueryResult($sqlQuery, $outParamCount=false) {
$this->clearError();
$this->debug('Performing SQL query: '.$sqlQuery);
$result =& $this->db->query($sqlQuery);
if (PEAR::isError($result)) {
$e = $this->setError($result->getMessage());
return $e;
}
if ($outParamCount) {
if ($result->numCols() != $outParamCount) {
return $ptr->setError("Query '$sqlQuery' result has $result->numCols() cols, that ' .
'do not match outParams ".$outParamCount);
}
}
$rows = array();
while ($row =& $result->fetchRow()) {
$rows[] =& $row;
}
$result->free();
$r = array('return' => $rows);
if ($this->debug) {
$this->debug('Returned '.count($rows).' rows:');
$this->debug($this->varDump($r));
}
return $r;
}
/**
* Creates an array structure with the specified out parameters
* @access private
*/
function createArrayResultType($functionName, $outParams) {
$structName = $functionName.'ResultType';
$types = array();
foreach ($outParams as $name => $type) {
$types[$name] = array('name' => $name, 'type' => $type);
}
$this->soapserver->wsdl->addComplexType(
$structName,
'complexType',
'struct',
'all',
'',
$types
);
$this->soapserver->wsdl->addComplexType(
'ArrayOf'.$structName,
'complexType',
'array',
'',
'SOAP-ENC:Array',
array(),
array(array('ref'=>'SOAP-ENC:arrayType',
'wsdl:arrayType'=>'tns:'.$structName.'[]')),
'tns:'.$structName
);
return $structName;
}
/**
* Verifies the security of a WSDL call according to the security token.
* @param $securityParams array Contains the security token
* @return boolean True if security token matches, false otherwise.
* If false, setError() will be called with an deny message, so return the result of getError().
*/
function verifySecurity($securityParams) {
$this->clearError();
if ($this->debug) {
$this->debug('Security Params: '.$this->varDump($securityParams));
}
$tokenGood = false;
if (isset($securityParams)) {
if (is_array($securityParams) &&
(isset($securityParams['securityToken']) && $securityParams['securityToken'] == $this->securityToken) ||
(isset($securityParams[':securityToken']) && $securityParams[':securityToken'] == $this->securityToken)) {
$tokenGood = true;
} else
if (is_string($securityParams) && $securityParams == $this->securityToken) {
$tokenGood = true;
}
}
if ($tokenGood) {
$this->debug('Security token accepted.');
} else {
$this->setError('Access Denied');
}
return $tokenGood;
}
/**
* Services the WSDL. Call this after all *WSDLFunction calls.
* @access public
*/
function serviceWSDL() {
if (!isset($HTTP_RAW_POST_DATA))
$HTTP_RAW_POST_DATA = file_get_contents("php://input");
if ($this->debug) {
ob_start();
}
$this->soapserver->service($HTTP_RAW_POST_DATA);
if ($this->debug) {
$this->debug("SOAP Request:\r\n".$HTTP_RAW_POST_DATA);
$res = ob_get_contents();
ob_end_clean();
$this->debug("\r\nSOAP Response:\r\n".$res);
echo $res;
}
$this->db->disconnect();
}
/**
* Sets the debug flag to true.
* @access public
*/
function setDebug($debug=true) {
$this->debug = $debug;
$soapserver->debug_flag = $debug;
}
/**
* Clears the debug flag.
* @access public
*/
function clearDebug() {
$this->debugStr = '';
}
/**
* Appends a string to the debug log string.
* @access public
*/
function debug($str) {
if ($this->debug) {
$this->debugStr .= $str . "\r\n";
}
}
/**
* Returns true if debug is turned on.
* @return boolean true if debug is turned on.
*/
function isDebug() {
return $this->debug;
}
/**
* Returns a string with the debug log output
* @return string The output of the debug log
* @access public
*/
function &getDebug() {
return $this->debugStr;
}
/**
* Returns a string with the output of the NuSOAP debug log
* @return string The output of the NuSOAP debug log
* @access public
*/
function &getSOAPDebug() {
return $this->soapserver->getDebug();
}
/**
* Returns a string with the output of var_dump
* @param mixed $data The input to var_dump
* @return string The output of var_dump
* @access public
*/
function varDump($data) {
ob_start();
var_dump($data);
$s = ob_get_contents();
ob_end_clean();
return $s;
}
/**
* Returns true if an error occurred in the last function call on this class.
* @return boolean returns true if an error occurred in the function call on this class.
* @access public
*/
function isError() {
return $this->errorStr != '';
}
/**
* Returns the error string that occurred in the last function call on this class.
* @return string the error string that occurred in the function call on this class.
* @access public
*/
function getError() {
return $this->errorStr;
}
/**
* Clears the last error. Use with isError().
* @access public
*/
function clearError() {
$this->errorStr = '';
}
/**
* Sets an error. Use with isError().
* @access public
*/
function setError($error) {
$this->errorStr = $error;
if (isset($this->soapserver)) {
$this->soapserver->fault('Server', $error);
}
$this->debug('Error: '.$error);
return $error;
}
/**
* Helper functions to capture any errors from eval()
* @access private
*/
function createFunc($code) {
$orig_hndl = set_error_handler('_error_hndl');
eval($code);
restore_error_handler();
global $funcErrors;
global $ptr;
$err = '';
if (count($funcErrors) > 0)
$err = $ptr->varDump($funcErrors);
if ($ptr->isDebug()) {
$ptr->debug("Creating function:\n$code\n$err");
}
return $err;
}
}
/**
* Error handler for soapsql.
*/
$funcErrors = array();
function _error_hndl($errno, $errstr) {
$funcErrors[] = array("errno"=>$errno, "errstr"=>$errstr);
}
?>