Location: PHPKode > projects > SOAP SQL > soapsql/soapsql.php
<?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);
}

?>
Return current item: SOAP SQL