<?php
/**
* (c) 2000, 2001, 2002, 2003 Remistech Sdn Bhd. All Rights Reserved.
*  Developed by John Lim
*  Licensed to Natsoft (M) Sdn Bhd to distribute and support. Refer to LICENSE document.
*/

include_once("phplens.inc.php");
//$TEST = 'a';

if (isset($TEST))
	print PHPLens_Remote_Query('form','obj',"1\tmysqlnwind\tselect * from products where categoryid={id}\tIG\tID::2::I");
else {
/* this is the PHP interface to the server library we are using */
	include_once("phplens-jsrsServer.inc.php");
	jsrsDispatch( "PHPLens_Remote_Query" );
}

/*
	To display long strings in alert(), we need to wordwrap, except for IE
	that does not accept wordwrapped strings properly.
*/
function lens_debug_wordwrap($str, $len, $sep, $cut)
{
GLOBAL $HTTP_SERVER_VARS;

	$u = strtoupper($HTTP_SERVER_VARS['HTTP_USER_AGENT']);
	
	 if (strpos($u,'MSIE') !== false) return $str;
	 else return wordwrap($str,$len,$sep,$cut);
}

/*
 Execute an SQL query from a remote browser. Return the results as a string.
 Assumes that the SQL query is for a HTML popup, so the SELECT statement
 can only retrieve 1 or 2 columns max.
 
 For security we checksum the SQL and database connection details with a salt. 
 The salt should be defined as a constant in phplens.config.inc.php (PHPLENS_MD5_SALT).
 
 Parameters:
 $form is the name of the DOM form 
 $name is the name of the field in the form that is awaiting the reply
 $str is the tab-delimited record - see below 
 
 Returns:
 A tab-delimited string with the following fields:
 errormessage	- this can be a blank string
 formname       - see $form above
 name           - see $name above
 [fieldval::fieldtext]* - multiple popup list values delimited by tab
*/
function PHPLens_Remote_Query($form,$name,$str)
{
global $PHPLENS_DATABASES;

	$sepRS = '-v@z-';
	
	/* (1) UNCOMMENT THE FOLLOWING LINE TO DEBUG */
	//return str_replace($sepRS," ",urldecode($str));
	
	/* (2) ALTERNATIVE DEBUG STATEMENT */
	//global $HTTP_SERVER_VARS; return 'len='.strlen($HTTP_SERVER_VARS['QUERY_STRING'])."\nSERVER=".wordwrap(str_replace($sepRS," ",$HTTP_SERVER_VARS['QUERY_STRING']),40,"\n",1);

	$msg = ''; // set this to display a javascript alert
	
//----------------------------------------------------------------------------------------
// FORMAT OF $str
// $str is a tab-delimited record with the following fields:
// [0] version - currently "1"
// [1] phplens database name, can be blank, but the $DB_* variables need to be manually set
// [2] sql string
// [3] md5 checksum, which is an md5 checksum of [1] and [2] + salt
//
// The remaining fields depend on how many {variables} there are.
// In the example below, we have two: [4] and [5]
//
// [4] fld :: val :: type   [note: type is optional and can be blank - we use ADODB types]
// [5] fld :: val :: type
//
	$arr = explode($sepRS,$str);
	if (sizeof($arr) == 1) {
		$sepRS = "\t";
		$arr = explode($sepRS,$str);
		if (sizeof($arr) < 3) {
			print "Error in \$QUERY_STRING format";
		}
	}
	$dbname = $arr[1];
	$sql = rawurldecode($arr[2]);
	$chksum = $arr[3];
	$mustfill = @$arr[4];
	
	//return " $mustfill";
	
	$chksum2 = md5(PHPLENS_MD5_SALT.'#'.$sql.$dbname);
	# return PHPLENS_MD5_SALT."\n#sql=$sql db=$dbname\ninhtml=$chksum remote_q=$chksum2 ";
	
	if ($chksum != $chksum2) return "Bad fingerprint - are you trying to modify SQL?";
	
	if ($dbname) {
		$db = $PHPLENS_DATABASES[$arr[1]];
		if (empty($db)) return "\$lens->phplensDatabase not configured correctly";
		$DB_DRIVER = $db[0];
		$DB_SERVER = $db[1];
		$DB_USER = $db[2];
		$DB_PASSWORD = $db[3];
		$DB_DATABASE = $db[4];
	} else {
		$DB_DRIVER = 'mysql';
		$DB_SERVER = 'localhost';
		$DB_USER = 'root';
		$DB_PASSWORD = '';
		$DB_DATABASE = 'northwind';
	}
	
	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
	
	$conn = ADONewConnection($DB_DRIVER);
	// change to Connect <-> PConnect for persistent connections
	if (!$conn->Connect($DB_SERVER,$DB_USER,$DB_PASSWORD,$DB_DATABASE)) 
		return "Cannot connect to database: ".$conn->ErrorMsg();
	
	for ($i=4, $max=sizeof($arr); $i < $max; $i++) {
		$varr = split('::',$arr[$i]);
		$match[] = '/\{'.$varr[0].'\}/i';
		$val = rawurldecode($varr[1]);
		
		// guess the type if empty
		if (empty($varr[2]))
			if (is_numeric($val)) $varr[2] = 'N';
			else $varr[2] = 'C';
			
		switch($varr[2]) {
		case 'C':
		case 'X':
			$avar = $conn->qstr($val);
			if ($varr[0] == '#') $avar = substr($avar,1,strlen($avar)-2);
			// juris patch
			if ($DB_DRIVER == 'oci8' && $avar == "''" && file_exists('/htdocs/juris')) $avar = "' '";
			$flds[] = $avar;
			break;
		/*	
		## T and D not supported currently
		case 'T':
			$flds[] = rawurlencode($conn->DBTimeStamp($v));
			break;
		case 'D':
			$flds[] = $conn->DBDate($v);
			break;*/
			
		default: // assume integer or number
			if (is_numeric($val)) $flds[] = $val;
			else $flds[] = (float) $val;
		}
	}
	if (sizeof($match) && sizeof($flds))
		$sql = preg_replace($match,$flds,trim($sql));

	$s = '';
	
	// final security check
	switch(strtoupper(trim(substr($sql,0,5)))) {
	case 'SELEC':
		break;
	default:
	case 'DROP':
	case 'DESC': // describe
	case 'TRUNC': // truncate
	case 'DELET': // delete
	case 'DESCR': // describe
	case 'UPDAT': // update
	case 'INSER': // insert
	case 'REPLA': // replace
		return "Illegal to manipulate data in hot update: $sql";
	
	case 'METAT':
		$a = explode(' ',$sql);
		if (sizeof($a)>1) {
			$a[1] = trim($a[1],"'");
			include_once(APPLETS_DIR.'/init/db.inc.php');
			if ($a[1] && $a[1] != ' ') {
				$conn =& LensDBConnectionPersist($a[1]);
				if (!is_object($conn)) return "$sql - undefined database connection";
			}
		}
		$arr = $conn->MetaTables();
		if ($arr) {
			foreach($arr as $v) {
				$s .= $sepRS.rawurlencode($v);
			}
			asort($arr);
		}
		return "$msg$sepRS$form$sepRS$name$sepRS$s";
		
	case 'METAF': // MetaForeignKeys {srctable} {desttable}
		$a = explode(' ',$sql);
		if (sizeof($a)>3) {
			$a[3] = trim($a[3],"'");
			include_once(APPLETS_DIR.'/init/db.inc.php');
			if ($a[3] && $a[3] != ' ') {
				$conn =& LensDBConnectionPersist($a[3]);
				if (!is_object($conn)) return "$sql - undefined database connection";
			}
		}
		$arr = $conn->MetaForeignKeys(trim($a[1],"'"),false,true);
		$a2 = strtoupper(trim($a[2],"'"));
		if (isset($arr[$a2])) {
			foreach($arr[$a2] as $v) {
				$s .= $sepRS.rawurlencode($v);
			}
		}
		return "$msg$sepRS$form$sepRS$name$sepRS$s";
		
	case 'METAC': // MetaColumns {table}
		$a = explode(' ',$sql);
		
		if (sizeof($a) < 2) return "MetaC - must define table name";
		
		if (sizeof($a)>2) {
			$a[2] = trim($a[2],"'");
		
			if ($a[2] && $a[2] != ' ') {
				include_once(APPLETS_DIR.'/init/db.inc.php');
				$conn =& LensDBConnectionPersist($a[2]);
				if (!is_object($conn)) return "$sql - undefined database connection";
			}
		}
		
		$arr = $conn->MetaColumns(trim($a[1],"'"));
		foreach($arr as $o) {
			$az[]  = rawurlencode($o->name);
		}
		asort($az);
		$s = $sepRS.implode($sepRS,$az);
		
		return "$msg$sepRS$form$sepRS$name$sepRS$s";
	
	case 'METAP':
		$a = explode(' ',$sql);
		if (sizeof($a)<3) return "$sql - missing parameter";
		
		$code = $a[2];
		if (sizeof($a)>3) {
			for ($i=3, $max = sizeof($a); $i < $max; $i++) {
				$code .= ' '.$a[$i];
			}
		} 
		$file = str_replace('\\','/',$a[1]);
		if (strpos($file,'..') !== false) return "$file - invalid";
		if (strpos($file,'/') !== false) return "$file - invalid";
		$arr = '';
		if (include_once(APPLETS_DIR.'/remoteq/'.$file)) {
			global $DB;
			
			$DB = $conn;
			$ok = eval('$arr = '.$code.';');
		} else {
			return "$file - missing file";
		}
		
		if ($arr) {
			$s = '';
			$cnt = 0;
			foreach($arr as $k => $v) {
				if (!is_string($k) && $cnt == $k) $s .= $sepRS.rawurlencode($v);
				else $s .= $sepRS.rawurlencode($k).'::'.($v);
				
				$cnt += 1;
			}
		} else
			return $a[2]." - eval failed";
		return "$msg$sepRS$form$sepRS$name$sepRS$s";
		
	case 'METAK': // MetaPrimaryKeys {table}
		$a = explode(' ',$sql);
		
		if (sizeof($a)>2) {
			$a[2] = trim($a[2],"'");
		
			if ($a[2] && $a[2] != ' ') {
				include_once(APPLETS_DIR.'/init/db.inc.php');
				$conn =& LensDBConnectionPersist($a[2]);
				if (!is_object($conn)) return "$sql - undefined database connection";
			}
		}
		
		$arr =& $conn->MetaPrimaryKeys(trim($a[1],"'"));
		
		foreach($arr as $v) {
			$s .= $sepRS.$v;
		}
		//return $a[1].$s;
		return "$msg$sepRS$form$sepRS$name$sepRS$s";
	}
	$conn->SetFetchMode(ADODB_FETCH_NUM);
	$sql = preg_replace('/\{[a-z_0-9]+\}/i','null',$sql);
	$rs = $conn->Execute($sql);	
	
	$row = 0;
	if ($rs) {
		$nflds = $rs->FieldCount();
		while (!$rs->EOF) {
			$rows += 1;
			$s .= $sepRS;
			if ($nflds>1) {
				$cca = array();
				for ($cc=0;$cc<$nflds;$cc++) {
					$cca[] = rawurlencode($rs->fields[$cc]);
				}
				
				$s .= implode('::',$cca);
			} else $s .= rawurlencode($rs->fields[0]).'::'.rawurlencode($rs->fields[0]);
			
			$rs->MoveNext();
		}
	} else
		return "SQL failed: [$sql] ".$conn->ErrorMsg();
	
	// return optionalmsg formname objname  fieldval::fieldtext 
	if ($mustfill && $rows == 1) return "$msg$sepRS$form$sepRS$name$s";
	return "$msg$sepRS$form$sepRS$name$sepRS$s";
}
?>
