= before >)
$g_sqlComparisonOperators=array("<>","!=",">=","<=","=","<",">"," LIKE " );
$g_sqlQuerySpecialStrings = array_merge($g_sqlComparisonOperators, array("(",")",";",",","."));
$g_sqlQuerySpecialStringsMaxLen =6;
// Functions for SQL Queries
// SingleRec functions need no grouped ResultSet
$g_sqlSingleRecFuncs=array("UNIX_TIMESTAMP","MD5","NOW","ABS","LCASE","UCASE","LOWER","UPPER");
$g_sqlGroupingFuncs=array("MAX","MIN","COUNT","SUM", "AVG");
/**********************************************************************
SqlParser
***********************************************************************/
// Used to parse an SQL-Query (as String) into an SqlObject
class SqlParser extends StringParser {
/***********************************
Constructor
************************************/
function SqlParser($sql_query_str="") {
debug_print("New SqlParser instance: $sql_query_str
");
global $g_sqlQuerySpecialStrings;
$this->quoteChars=array("'","\"");
$this->escapeChar="\\";
$this->whitespaceChars=array(" ","\n","\r","\t");
$this->specialElements=$g_sqlQuerySpecialStrings;
$this->removeQuotes=false;
$this->removeEscapeChars=true;
}
/***********************************
Parse Dispatcher
************************************/
// Returns a SqlQuery Object or null if an error accoured
function parseSqlQuery() {
$type="";
if(!$type=$this->parseNextElement())
return null;
$type=strtoupper($type);
switch($type) {
case "SELECT":
return $this->parseSelectQuery();
case "INSERT":
return $this->parseInsertQuery();
case "DELETE":
return $this->parseDeleteQuery();
case "UPDATE":
return $this->parseUpdateQuery();
case "CREATE":
if(strtoupper($this->peekNextElement())=="TABLE") {
$this->skipNextElement();
return $this->parseCreateTableQuery();
}
}
print_error_msg("SQL Type $type not supported");
return null;
}
/***********************************
Select Query Parse Function
************************************/
// SELECT must be removed here (do not call this Function directly !!!)
// returns a SqlQuery Object
function parseSelectQuery() {
$colNames="";
$table="";
$orderColumn="";
$orderType="";
$where_expr="";
$distinct=0;
// parse Distinct
if(strtoupper($this->peekNextElement())=="DISTINCT") {
$distinct=1;
$this->skipNextElement();
}
// parse Columns
$arrElements=array();
$colIndex=-1;
while($this->parseNextElements(",",array("FROM"),$arrElements)) {
// *empty array* | col
// column name ?
if(count($arrElements)>=1) {
if($colNames!='')$colNames=$colNames.",".$arrElements[0];
else $colNames=$arrElements[0];
array_splice($arrElements,0,1);
}
if(count($arrElements)>0) {
$errStr="Unexpected Element(s): ";
for($i=0;$iskipNextElement();
// parse Tables
$arrElements=array();
while($this->parseNextElements(",",array("WHERE","ORDER",";"),$arrElements)) {
$table=$arrElements[0];
}
// parse Where statement (Raw, because the escape-chars are needend in the ExpressionParser)
if(strtoupper($this->peekNextElement()) == "WHERE") {
$this->skipNextElement();
while(!is_empty_str($elem=$this->peekNextElementRaw())) {
if(strtoupper($elem)=="ORDER" || $elem==";" )break;
$this->skipNextElement();
// no " " on points
if($elem==".") {
remove_last_char($where_expr);
$where_expr .= $elem;
} else {
$where_expr .= $elem . " ";
}
}
}
debug_print( "WHERE EXPR: $where_expr
");
// parse ORDER BY
if(strtoupper($this->peekNextElement()) == "ORDER") {
$this->skipNextElement();
if(strtoupper($this->parseNextElement())!="BY") {
print_error_msg("BY expected");
return null;
}
while(!is_empty_str($elem=$this->peekNextElement())) {
if($elem==";" ) break;
$this->skipNextElement();
if(strtoupper($elem)=="ASC")
$orderType="+";
else if(strtoupper($elem)=="DESC")
$orderType="-";
else {
$orderColumn=$elem;
}
}
}
$sqlObj = new stdClass();
$sqlObj->colNames=$colNames;
$sqlObj->table=$table;
$sqlObj->where_expr=$where_expr;
$sqlObj->distinct=$distinct;
$sqlObj->orderColumn=$orderColumn;
$sqlObj->orderType=$orderType;
return $sqlObj;
}
/***********************************
Insert Query Parse Function
************************************/
// INSERT must be removed here (do not call this Function directly !!!)
// returns a SqlQuery Object
function parseInsertQuery() {
$colNames=array();
$fieldValues=array();
$table="";
$insertType="";
// remove INTO
if(strtoupper($this->peekNextElement())=="INTO")
$this->skipNextElement();
// Read Table
$table=$this->parseNextElement();
// Read Column Names between ()'s
$colIndex=0;
if($this->peekNextElement()=="(") {
$this->skipNextElement();
while(($elem=$this->parseNextElement())!=")") {
if($elem==",")
$colIndex++;
else
$colNames[$colIndex]=$elem;
}
}
// read Insert Type
$insertType=$this->parseNextElement();
switch(strtoupper($insertType)) {
case "SET":
// Read Columns and Values
$colIndex=0;
$writeToValue=false;
while( !is_empty_str(($elem=$this->parseNextElement())) && ($elem != ";")) {
if($elem==",") {
$colIndex++;
$writeToValue=false;
} else if($elem=="=") {
$writeToValue=true;
} else {
if($writeToValue) {
if(!isset($fieldValues[$colIndex])) {
$fieldValues[$colIndex]="";
}
$fieldValues[$colIndex].=$elem;
} else {
if(!isset($fieldValues[$colIndex])) {
$colNames[$colIndex]="";
}
$colNames[$colIndex].=$elem;
}
}
}
break;
case "VALUES":
if($this->parseNextElement()!="(") {
print_error_msg("VALUES in the INSERT Statement must be in Braces \"(,)\"");
return null;
}
$openBraces=1;
$fieldValuesIndex=0;
while( !is_empty_str(($elem=$this->parseNextElement())) && ($elem != ";" )) {
if($elem=="(") {
$openBraces++;
$fieldValues[$fieldValuesIndex].=$elem;
} else if($elem==")") {
$openBraces--;
if($openBraces<1) {
break;
}
$fieldValues[$fieldValuesIndex].=$elem;
} else if($elem==",") {
$fieldValuesIndex++;
} else {
if(!isset($fieldValues[$fieldValuesIndex])) {
$fieldValues[$fieldValuesIndex]="";
}
$fieldValues[$fieldValuesIndex].=$elem;
}
}
break;
default:
print_error_msg("Insert Type " . $insertType . " not supported");
return null;
}
$sqlObj = new stdClass();
$sqlObj->type = "INSERT";
$sqlObj->colNames=$colNames;
$sqlObj->fieldValues=$fieldValues;
$sqlObj->table=$table;
return $sqlObj;
}
/***********************************
Delete Query Parse Function
************************************/
// DELETE must be removed here (do not call this Function directly !!!)
// returns a SqlQuery Object
function parseDeleteQuery() {
$tables=array();
$where_expr="";
if(strtoupper($this->parseNextElement())!="FROM") {
print_error_msg("FROM expected");
return null;
}
$tables[0]=$this->parseNextElement();
// Because the Where Statement is not parsed with
// the parseXX Functions, this equals a Raw-Parse,
// as needed for the ExpressionParser
if(strtoupper($this->parseNextElement())=="WHERE") {
$where_expr=rtrim($this->workingStr);
debug_print("where_expr: $where_expr
");
if(last_char($where_expr)==";")
remove_last_char($where_expr);
} else if ($elem=$this->parseNextElement()) {
print_error_msg("Nothing more expected: $elem");
return null;
}
$sqlObj = new SqlQuery();
$sqlObj->type = "DELETE";
$sqlObj->tables=$tables;
$sqlObj->where_expr=$where_expr;
return $sqlObj;
}
/***********************************
Update Query Parse Function
************************************/
// UPDATE must be removed here (do not call this Function directly !!!)
// returns a SqlQuery Object
function parseUpdateQuery() {
$colNames=array();
$fieldValues=array();
$tables=array();
$where_expr="";
// Read Table
$table=$this->parseNextElement();
// Remove SET
if(strtoupper($this->parseNextElement())!="SET") {
print_error_msg("SET expected");
return null;
}
// Read Columns and Values
$colIndex=0;
$writeToValue=false;
while( !is_empty_str(($elem=$this->parseNextElement())) && ($elem != ";") && strtoupper($elem)!="WHERE") {
if($elem==",") {
$colIndex++;
$writeToValue=false;
} else if($elem=="=") {
$writeToValue=true;
} else {
if($writeToValue) {
if(!isset($fieldValues[$colIndex])) {
$fieldValues[$colIndex]="";
}
$fieldValues[$colIndex].=$elem;
} else {
if(!isset($fieldValues[$colIndex])) {
$colNames[$colIndex]="";
}
$colNames[$colIndex].=$elem;
}
}
}
// Raw-Parse Where Statement
if(strtoupper($elem)=="WHERE") {
$where_expr=rtrim($this->workingStr);
debug_print("where_expr: $where_expr
");
if(last_char($where_expr)==";")
remove_last_char($where_expr);
}
$sqlObj = new stdClass();
$sqlObj->type = "UPDATE";
$sqlObj->colNames=$colNames;
$sqlObj->fieldValues=$fieldValues;
$sqlObj->table=$table;
$sqlObj->where_expr=$where_expr;
return $sqlObj;
}
/***********************************
Create Table Query Parse Function
************************************/
// CREATE TABLE must be removed here (do not call this Function directly !!!)
// returns a SqlQuery Object
function parseCreateTableQuery() {
$colNames=array();
$colTypes=array();
$colDefaultValues=array();
$table="";
$table=$this->parseNextElement();
if($this->parseNextElement()!="(") {
print_error_msg("( expected");
return null;
}
$index=0;
$arrElements=array();
while($this->parseNextElements(",",array(";","PRIMARY"),$arrElements)) {
$colNames[$index]=$arrElements[0];
$coltype=$arrElements[1];
if(count($arrElements)<=2){
$colTypes[$index]=$coltype;
$index++;
continue;
}
if($arrElements[2]=="(")
{
$coltype.=$arrElements[2].$arrElements[3].$arrElements[4];
$arrElements=array_slice($arrElements,5);
}else $arrElements=array_slice($arrElements,2);
$colTypes[$index]=$coltype;
if(!count($arrElements)){
$index++;
continue;
}
if($arrElements[0]=="NOT" && $arrElements[1]=="NULL")
{
$arrElements=array_slice($arrElements,2);
}
if( $arrElements[0]=="NULL")
{
$index++;
continue;
}
if(!count($arrElements)){$index++;continue;}
if(strtoupper($arrElements[0])=="DEFAULT")
{
if( has_quotes($arrElements[1]) ){
remove_quotes($arrElements[1]);
$colDefaultValues[$index]=$arrElements[1];
} else {
$colDefaultValues[$index]=$arrElements[1];
echo remove_quotes($arrElements[1]);
}
}
$index++;
}
$sqlObj = new stdClass();
$sqlObj->colNames=$colNames;
$sqlObj->colTypes=$colTypes;
$sqlObj->fieldValues=$colDefaultValues;
$sqlObj->table=$table;
return $sqlObj;
}
/***********************************
Parse Helper Functions
************************************/
// does not remove Escape Chars
function parseNextElementRaw() {
$this->removeEscapeChars=false;
$ret= $this->parseNextElement();
$this->removeEscapeChars=true;
return $ret;
}
// does not remove Escape Chars
function peekNextElementRaw() {
$this->removeEscapeChars=false;
$ret= $this->peekNextElement();
$this->removeEscapeChars=true;
return $ret;
}
}
?>