00001 <?php
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00106 class t3lib_sqlparser {
00107
00108
00109 var $parse_error = '';
00110 var $lastStopKeyWord = '';
00111
00112
00113
00114
00115
00116
00117
00118
00119
00120
00128 function parseSQL($parseString) {
00129
00130
00131 $parseString = $this->trimSQL($parseString);
00132 $this->parse_error = '';
00133 $result = array();
00134
00135
00136 $_parseString = $parseString;
00137 $keyword = $this->nextPart($_parseString, '^(SELECT|UPDATE|INSERT[[:space:]]+INTO|DELETE[[:space:]]+FROM|EXPLAIN|DROP[[:space:]]+TABLE|CREATE[[:space:]]+TABLE|ALTER[[:space:]]+TABLE)[[:space:]]+');
00138 $keyword = strtoupper(ereg_replace('[[:space:]]*','',$keyword));
00139
00140 switch($keyword) {
00141 case 'SELECT':
00142
00143 $result = $this->parseSELECT($parseString);
00144 break;
00145 case 'UPDATE':
00146
00147 $result = $this->parseUPDATE($parseString);
00148 break;
00149 case 'INSERTINTO':
00150
00151 $result = $this->parseINSERT($parseString);
00152 break;
00153 case 'DELETEFROM':
00154
00155 $result = $this->parseDELETE($parseString);
00156 break;
00157 case 'EXPLAIN':
00158
00159 $result = $this->parseEXPLAIN($parseString);
00160 break;
00161 case 'DROPTABLE':
00162
00163 $result = $this->parseDROPTABLE($parseString);
00164 break;
00165 case 'ALTERTABLE':
00166
00167 $result = $this->parseALTERTABLE($parseString);
00168 break;
00169 case 'CREATETABLE':
00170
00171 $result = $this->parseCREATETABLE($parseString);
00172 break;
00173 default:
00174 return $this->parseError('"'.$keyword.'" is not a keyword',$parseString);
00175 break;
00176 }
00177
00178 return $result;
00179 }
00180
00188 function parseSELECT($parseString) {
00189
00190 // Removing SELECT:
00191 $parseString = $this->trimSQL($parseString);
00192 $parseString = eregi_replace('^SELECT[[:space:]]+','',$parseString);
00193
00194 // Init output variable:
00195 $result = array();
00196 $result['type'] = 'SELECT';
00197
00198 // Looking for STRAIGHT_JOIN keyword:
00199 $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
00200
00201 // Select fields:
00202 $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
00203 if ($this->parse_error) { return $this->parse_error; }
00204
00205 // Continue if string is not ended:
00206 if ($parseString) {
00207
00208 // Get table list:
00209 $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
00210 if ($this->parse_error) { return $this->parse_error; }
00211
00212 // If there are more than just the tables (a WHERE clause that would be...)
00213 if ($parseString) {
00214
00215 // Get WHERE clause:
00216 $result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
00217 if ($this->parse_error) { return $this->parse_error; }
00218
00219 // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
00220 if ($this->lastStopKeyWord) {
00221
00222 // GROUP BY parsing:
00223 if ($this->lastStopKeyWord == 'GROUPBY') {
00224 $result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
00225 if ($this->parse_error) { return $this->parse_error; }
00226 }
00227
00228 // ORDER BY parsing:
00229 if ($this->lastStopKeyWord == 'ORDERBY') {
00230 $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
00231 if ($this->parse_error) { return $this->parse_error; }
00232 }
00233
00234 // LIMIT parsing:
00235 if ($this->lastStopKeyWord == 'LIMIT') {
00236 if (ereg('^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$',trim($parseString))) {
00237 $result['LIMIT'] = $parseString;
00238 } else {
00239 return $this->parseError('No value for limit!',$parseString);
00240 }
00241 }
00242 }
00243 }
00244 } else return $this->parseError('No table to select from!',$parseString);
00245
00246 // Return result:
00247 return $result;
00248 }
00249
00257 function parseUPDATE($parseString) {
00258
00259 // Removing UPDATE
00260 $parseString = $this->trimSQL($parseString);
00261 $parseString = eregi_replace('^UPDATE[[:space:]]+','',$parseString);
00262
00263 // Init output variable:
00264 $result = array();
00265 $result['type'] = 'UPDATE';
00266
00267 // Get table:
00268 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
00269
00270 // Continue if string is not ended:
00271 if ($result['TABLE']) {
00272 if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+')) {
00273
00274 $comma = TRUE;
00275
00276 // Get field/value pairs:
00277 while($comma) {
00278 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*=')) {
00279 $this->nextPart($parseString,'^(=)'); // Strip of "=" sign.
00280 $value = $this->getValue($parseString);
00281 $result['FIELDS'][$fieldName] = $value;
00282 } else return $this->parseError('No fieldname found',$parseString);
00283
00284 $comma = $this->nextPart($parseString,'^(,)');
00285 }
00286
00287 // WHERE
00288 if ($this->nextPart($parseString,'^(WHERE)')) {
00289 $result['WHERE'] = $this->parseWhereClause($parseString);
00290 if ($this->parse_error) { return $this->parse_error; }
00291 }
00292 } else return $this->parseError('Query missing SET...',$parseString);
00293 } else return $this->parseError('No table found!',$parseString);
00294
00295 // Should be no more content now:
00296 if ($parseString) {
00297 return $this->parseError('Still content in clause after parsing!',$parseString);
00298 }
00299
00300 // Return result:
00301 return $result;
00302 }
00303
00311 function parseINSERT($parseString) {
00312
00313 // Removing INSERT
00314 $parseString = $this->trimSQL($parseString);
00315 $parseString = eregi_replace('^INSERT[[:space:]]+INTO[[:space:]]+','',$parseString);
00316
00317 // Init output variable:
00318 $result = array();
00319 $result['type'] = 'INSERT';
00320
00321 // Get table:
00322 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()');
00323
00324 if ($result['TABLE']) {
00325
00326 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+')) { // In this case there are no field names mentioned in the SQL!
00327 // Get values/fieldnames (depending...)
00328 $result['VALUES_ONLY'] = $this->getValue($parseString,'IN');
00329 if ($this->parse_error) { return $this->parse_error; }
00330 } else { // There are apparently fieldnames listed:
00331 $fieldNames = $this->getValue($parseString,'_LIST');
00332 if ($this->parse_error) { return $this->parse_error; }
00333
00334 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+')) { // "VALUES" keyword binds the fieldnames to values:
00335
00336 $values = $this->getValue($parseString,'IN'); // Using the "getValue" function to get the field list...
00337 if ($this->parse_error) { return $this->parse_error; }
00338
00339 foreach($fieldNames as $k => $fN) {
00340 if (ereg('^[[:alnum:]_]+$',$fN)) {
00341 if (isset($values[$k])) {
00342 if (!isset($result['FIELDS'][$fN])) {
00343 $result['FIELDS'][$fN] = $values[$k];
00344 } else return $this->parseError('Fieldname ("'.$fN.'") already found in list!',$parseString);
00345 } else return $this->parseError('No value set!',$parseString);
00346 } else return $this->parseError('Invalid fieldname ("'.$fN.'")',$parseString);
00347 }
00348 if (isset($values[$k+1])) {
00349 return $this->parseError('Too many values in list!',$parseString);
00350 }
00351 } else return $this->parseError('VALUES keyword expected',$parseString);
00352 }
00353 } else return $this->parseError('No table found!',$parseString);
00354
00355 // Should be no more content now:
00356 if ($parseString) {
00357 return $this->parseError('Still content after parsing!',$parseString);
00358 }
00359
00360 // Return result
00361 return $result;
00362 }
00363
00371 function parseDELETE($parseString) {
00372
00373 // Removing DELETE
00374 $parseString = $this->trimSQL($parseString);
00375 $parseString = eregi_replace('^DELETE[[:space:]]+FROM[[:space:]]+','',$parseString);
00376
00377 // Init output variable:
00378 $result = array();
00379 $result['type'] = 'DELETE';
00380
00381 // Get table:
00382 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
00383
00384 if ($result['TABLE']) {
00385
00386 // WHERE
00387 if ($this->nextPart($parseString,'^(WHERE)')) {
00388 $result['WHERE'] = $this->parseWhereClause($parseString);
00389 if ($this->parse_error) { return $this->parse_error; }
00390 }
00391 } else return $this->parseError('No table found!',$parseString);
00392
00393 // Should be no more content now:
00394 if ($parseString) {
00395 return $this->parseError('Still content in clause after parsing!',$parseString);
00396 }
00397
00398 // Return result:
00399 return $result;
00400 }
00401
00409 function parseEXPLAIN($parseString) {
00410
00411 // Removing EXPLAIN
00412 $parseString = $this->trimSQL($parseString);
00413 $parseString = eregi_replace('^EXPLAIN[[:space:]]+','',$parseString);
00414
00415 // Init output variable:
00416 $result = $this->parseSELECT($parseString);
00417 if (is_array($result)) {
00418 $result['type'] = 'EXPLAIN';
00419 }
00420
00421 return $result;
00422 }
00423
00431 function parseCREATETABLE($parseString) {
00432
00433 // Removing CREATE TABLE
00434 $parseString = $this->trimSQL($parseString);
00435 $parseString = eregi_replace('^CREATE[[:space:]]+TABLE[[:space:]]+','',$parseString);
00436
00437 // Init output variable:
00438 $result = array();
00439 $result['type'] = 'CREATETABLE';
00440
00441 // Get table:
00442 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\(',TRUE);
00443
00444 if ($result['TABLE']) {
00445
00446 // While the parseString is not yet empty:
00447 while(strlen($parseString)>0) {
00448 if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY)([[:space:]]+|\()')) { // Getting key
00449 $key = strtoupper(ereg_replace('[[:space:]]','',$key));
00450
00451 switch($key) {
00452 case 'PRIMARYKEY':
00453 $result['KEYS'][$key] = $this->getValue($parseString,'_LIST');
00454 if ($this->parse_error) { return $this->parse_error; }
00455 break;
00456 case 'KEY':
00457 if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()')) {
00458 $result['KEYS'][$keyName] = $this->getValue($parseString,'_LIST');
00459 if ($this->parse_error) { return $this->parse_error; }
00460 } else return $this->parseError('No keyname found',$parseString);
00461 break;
00462 }
00463 } elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) { // Getting field:
00464 $result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString);
00465 if ($this->parse_error) { return $this->parse_error; }
00466 }
00467
00468 // Finding delimiter:
00469 $delim = $this->nextPart($parseString, '^(,|\))');
00470 if (!$delim) {
00471 return $this->parseError('No delimiter found',$parseString);
00472 } elseif ($delim==')') {
00473 break;
00474 }
00475 }
00476
00477 // Finding what is after the table definition - table type in MySQL
00478 if ($delim==')') {
00479 if ($this->nextPart($parseString, '^(TYPE[[:space:]]*=)')) {
00480 $result['tableType'] = $parseString;
00481 $parseString = '';
00482 }
00483 } else return $this->parseError('No fieldname found!',$parseString);
00484
00485 // Getting table type
00486 } else return $this->parseError('No table found!',$parseString);
00487
00488 // Should be no more content now:
00489 if ($parseString) {
00490 return $this->parseError('Still content in clause after parsing!',$parseString);
00491 }
00492
00493 return $result;
00494 }
00495
00503 function parseALTERTABLE($parseString) {
00504
00505 // Removing ALTER TABLE
00506 $parseString = $this->trimSQL($parseString);
00507 $parseString = eregi_replace('^ALTER[[:space:]]+TABLE[[:space:]]+','',$parseString);
00508
00509 // Init output variable:
00510 $result = array();
00511 $result['type'] = 'ALTERTABLE';
00512
00513 // Get table:
00514 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
00515
00516 if ($result['TABLE']) {
00517 if ($result['action'] = $this->nextPart($parseString, '^(CHANGE|DROP[[:space:]]+KEY|DROP[[:space:]]+PRIMARY[[:space:]]+KEY|ADD[[:space:]]+KEY|ADD[[:space:]]+PRIMARY[[:space:]]+KEY|DROP|ADD|RENAME)([[:space:]]+|\()')) {
00518 $actionKey = strtoupper(ereg_replace('[[:space:]]','',$result['action']));
00519
00520 // Getting field:
00521 if (t3lib_div::inList('ADDPRIMARYKEY,DROPPRIMARYKEY',$actionKey) || $fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
00522
00523 switch($actionKey) {
00524 case 'ADD':
00525 $result['FIELD'] = $fieldKey;
00526 $result['definition'] = $this->parseFieldDef($parseString);
00527 if ($this->parse_error) { return $this->parse_error; }
00528 break;
00529 case 'DROP':
00530 case 'RENAME':
00531 $result['FIELD'] = $fieldKey;
00532 break;
00533 case 'CHANGE':
00534 $result['FIELD'] = $fieldKey;
00535 if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
00536 $result['definition'] = $this->parseFieldDef($parseString);
00537 if ($this->parse_error) { return $this->parse_error; }
00538 } else return $this->parseError('No NEW field name found',$parseString);
00539 break;
00540
00541 case 'ADDKEY':
00542 case 'ADDPRIMARYKEY':
00543 $result['KEY'] = $fieldKey;
00544 $result['fields'] = $this->getValue($parseString,'_LIST');
00545 if ($this->parse_error) { return $this->parse_error; }
00546 break;
00547 case 'DROPKEY':
00548 $result['KEY'] = $fieldKey;
00549 break;
00550 case 'DROPPRIMARYKEY':
00551 // ??? todo!
00552 break;
00553 }
00554 } else return $this->parseError('No field name found',$parseString);
00555 } else return $this->parseError('No action CHANGE, DROP or ADD found!',$parseString);
00556 } else return $this->parseError('No table found!',$parseString);
00557
00558 // Should be no more content now:
00559 if ($parseString) {
00560 return $this->parseError('Still content in clause after parsing!',$parseString);
00561 }
00562
00563 return $result;
00564 }
00565
00572 function parseDROPTABLE($parseString) {
00573
00574 // Removing DROP TABLE
00575 $parseString = $this->trimSQL($parseString);
00576 $parseString = eregi_replace('^DROP[[:space:]]+TABLE[[:space:]]+','',$parseString);
00577
00578 // Init output variable:
00579 $result = array();
00580 $result['type'] = 'DROPTABLE';
00581
00582 // IF EXISTS
00583 $result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
00584
00585 // Get table:
00586 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
00587
00588 if ($result['TABLE']) {
00589
00590 // Should be no more content now:
00591 if ($parseString) {
00592 return $this->parseError('Still content in clause after parsing!',$parseString);
00593 }
00594
00595 return $result;
00596 } else return $this->parseError('No table found!',$parseString);
00597 }
00598
00599
00600
00601
00602
00603
00604
00605
00606
00607
00608
00609
00610
00611
00612
00613
00614
00615 /**************************************
00616 *
00617 * SQL Parsing, helper functions for parts of queries
00618 *
00619 **************************************/
00620
00631 function parseFieldList(&$parseString, $stopRegex='') {
00632
00633 // Prepare variables:
00634 $parseString = $this->trimSQL($parseString);
00635 $this->lastStopKeyWord = '';
00636 $this->parse_error = '';
00637
00638
00639 $stack = array(); // Contains the parsed content
00640 $pnt = 0; // Pointer to positions in $stack
00641 $level = 0; // Indicates the parenthesis level we are at.
00642 $loopExit = 0; // Recursivity brake.
00643
00644 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
00645 while (strlen($parseString)) {
00646
00647 // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
00648 if ($level>0) { // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
00649
00650 // Accumulate function content until next () parenthesis:
00651 $funcContent = $this->nextPart($parseString,'^([^()]*.)');
00652 $stack[$pnt]['func_content.'][] = array(
00653 'level' => $level,
00654 'func_content' => substr($funcContent,0,-1)
00655 );
00656 $stack[$pnt]['func_content'].= $funcContent;
00657
00658 // Detecting ( or )
00659 switch(substr($stack[$pnt]['func_content'],-1)) {
00660 case '(':
00661 $level++;
00662 break;
00663 case ')':
00664 $level--;
00665 if (!$level) { // If this was the last parenthesis:
00666 $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'],0,-1);
00667 $parseString = ltrim($parseString); // Remove any whitespace after the parenthesis.
00668 }
00669 break;
00670 }
00671 } else { // Outside parenthesis, looking for next field:
00672
00673 // Looking for a known function (only known functions supported)
00674 $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
00675 if ($func) {
00676 $parseString = trim(substr($parseString,1)); // Strip of "("
00677 $stack[$pnt]['type'] = 'function';
00678 $stack[$pnt]['function'] = $func;
00679 $level++; // increse parenthesis level counter.
00680 } else {
00681 // Otherwise, look for regular fieldname:
00682 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]\*._]+)(,|[[:space:]]+)')) {
00683 $stack[$pnt]['type'] = 'field';
00684
00685 // Explode fieldname into field and table:
00686 $tableField = explode('.',$fieldName,2);
00687 if (count($tableField)==2) {
00688 $stack[$pnt]['table'] = $tableField[0];
00689 $stack[$pnt]['field'] = $tableField[1];
00690 } else {
00691 $stack[$pnt]['table'] = '';
00692 $stack[$pnt]['field'] = $tableField[0];
00693 }
00694 } else {
00695 return $this->parseError('No field name found as expected',$parseString);
00696 }
00697 }
00698 }
00699
00700 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
00701 if (!$level) {
00702
00703 // Looking for "AS" alias:
00704 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+')) {
00705 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
00706 $stack[$pnt]['as_keyword'] = $as;
00707 }
00708
00709 // Looking for "ASC" or "DESC" keywords (for ORDER BY)
00710 if ($sDir = $this->nextPart($parseString,'^(ASC|DESC)([[:space:]]+|,)')) {
00711 $stack[$pnt]['sortDir'] = $sDir;
00712 }
00713
00714 // Looking for stop-keywords:
00715 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
00716 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
00717 return $stack;
00718 }
00719
00720 // Looking for comma (since the stop-keyword did not trigger a return...)
00721 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
00722 return $this->parseError('No comma found as expected',$parseString);
00723 }
00724
00725 // Increasing pointer:
00726 $pnt++;
00727 }
00728
00729 // Check recursivity brake:
00730 $loopExit++;
00731 if ($loopExit>500) {
00732 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
00733 }
00734 }
00735
00736 // Return result array:
00737 return $stack;
00738 }
00739
00749 function parseFromTables(&$parseString, $stopRegex='') {
00750
00751 // Prepare variables:
00752 $parseString = $this->trimSQL($parseString);
00753 $this->lastStopKeyWord = '';
00754 $this->parse_error = '';
00755
00756 $stack = array(); // Contains the parsed content
00757 $pnt = 0; // Pointer to positions in $stack
00758 $loopExit = 0; // Recursivity brake.
00759
00760 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
00761 while (strlen($parseString)) {
00762
00763 // Looking for the table:
00764 if ($stack[$pnt]['table'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)')) {
00765 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+')) {
00766 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
00767 $stack[$pnt]['as_keyword'] = $as;
00768 }
00769 } else return $this->parseError('No table name found as expected!',$parseString);
00770
00771 // Looking for JOIN
00772 if ($join = $this->nextPart($parseString,'^(JOIN|LEFT[[:space:]]+JOIN)[[:space:]]+')) {
00773 $stack[$pnt]['JOIN']['type'] = $join;
00774 if ($stack[$pnt]['JOIN']['withTable'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]+ON[[:space:]]+',1)) {
00775 $field1 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]*=[[:space:]]*',1);
00776 $field2 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]+');
00777 if ($field1 && $field2) {
00778 $stack[$pnt]['JOIN']['ON'] = array($field1,$field2);
00779 } else return $this->parseError('No join fields found!',$parseString);
00780 } else return $this->parseError('No join table found!',$parseString);
00781 }
00782
00783 // Looking for stop-keywords:
00784 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
00785 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
00786 return $stack;
00787 }
00788
00789 // Looking for comma:
00790 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
00791 return $this->parseError('No comma found as expected',$parseString);
00792 }
00793
00794 // Increasing pointer:
00795 $pnt++;
00796
00797 // Check recursivity brake:
00798 $loopExit++;
00799 if ($loopExit>500) {
00800 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
00801 }
00802 }
00803
00804 // Return result array:
00805 return $stack;
00806 }
00807
00816 function parseWhereClause(&$parseString, $stopRegex='') {
00817
00818 // Prepare variables:
00819 $parseString = $this->trimSQL($parseString);
00820 $this->lastStopKeyWord = '';
00821 $this->parse_error = '';
00822
00823 $stack = array(0 => array()); // Contains the parsed content
00824 $pnt = array(0 => 0); // Pointer to positions in $stack
00825 $level = 0; // Determines parenthesis level
00826 $loopExit = 0; // Recursivity brake.
00827
00828 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
00829 while (strlen($parseString)) {
00830
00831 // Look for next parenthesis level:
00832 $newLevel = $this->nextPart($parseString,'^([(])');
00833 if ($newLevel=='(') { // If new level is started, manage stack/pointers:
00834 $level++; // Increase level
00835 $pnt[$level] = 0; // Reset pointer for this level
00836 $stack[$level] = array(); // Reset stack for this level
00837 } else { // If no new level is started, just parse the current level:
00838
00839 // Find "modifyer", eg. "NOT or !"
00840 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString,'^(!|NOT[[:space:]]+)'));
00841
00842 // Fieldname:
00843 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]._]+)([[:space:]]+|&|<=|>=|<|>|=|!=|IS)')) {
00844
00845 // Parse field name into field and table:
00846 $tableField = explode('.',$fieldName,2);
00847 if (count($tableField)==2) {
00848 $stack[$level][$pnt[$level]]['table'] = $tableField[0];
00849 $stack[$level][$pnt[$level]]['field'] = $tableField[1];
00850 } else {
00851 $stack[$level][$pnt[$level]]['table'] = '';
00852 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
00853 }
00854 } else {
00855 return $this->parseError('No field name found as expected',$parseString);
00856 }
00857
00858 // See if the value is calculated. Support only for "&" (boolean AND) at the moment:
00859 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString,'^(&)');
00860 if (strlen($stack[$level][$pnt[$level]]['calc'])) {
00861 // Finding value for calculation:
00862 $stack[$level][$pnt[$level]]['calc_value'] = $this->getValue($parseString);
00863 }
00864
00865 // Find "comparator":
00866 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString,'^(<=|>=|<|>|=|!=|NOT[[:space:]]+IN|IN|NOT[[:space:]]+LIKE|LIKE|IS)');
00867 if (strlen($stack[$level][$pnt[$level]]['comparator'])) {
00868 // Finding value for comparator:
00869 $stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString,$stack[$level][$pnt[$level]]['comparator']);
00870 if ($this->parse_error) { return $this->parse_error; }
00871 }
00872
00873 // Finished, increase pointer:
00874 $pnt[$level]++;
00875
00876 // Checking if the current level is ended, in that case do stack management:
00877 while ($this->nextPart($parseString,'^([)])')) {
00878 $level--; // Decrease level:
00879 $stack[$level][$pnt[$level]]['sub'] = $stack[$level+1]; // Copy stack
00880 $pnt[$level]++; // Increase pointer of the new level
00881
00882 // Make recursivity check:
00883 $loopExit++;
00884 if ($loopExit>500) {
00885 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely...',$parseString);
00886 }
00887 }
00888
00889 // Detecting the operator for the next level; support for AND, OR and &&):
00890 $op = $this->nextPart($parseString,'^(AND|OR|AND[[:space:]]+NOT)(\(|[[:space:]]+)');
00891 if ($op) {
00892 $stack[$level][$pnt[$level]]['operator'] = $op;
00893 } elseif (strlen($parseString)) {
00894
00895 // Looking for stop-keywords:
00896 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
00897 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
00898 return $stack[0];
00899 } else {
00900 return $this->parseError('No operator, but parsing not finished.',$parseString);
00901 }
00902 }
00903 }
00904
00905 // Make recursivity check:
00906 $loopExit++;
00907 if ($loopExit>500) {
00908 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
00909 }
00910 }
00911
00912 // Return the stacks lowest level:
00913 return $stack[0];
00914 }
00915
00924 function parseFieldDef(&$parseString, $stopRegex='') {
00925 // Prepare variables:
00926 $parseString = $this->trimSQL($parseString);
00927 $this->lastStopKeyWord = '';
00928 $this->parse_error = '';
00929
00930 $result = array();
00931
00932 // Field type:
00933 if ($result['fieldType'] = $this->nextPart($parseString,'^(int|smallint|tinyint|mediumint|double|varchar|char|text|tinytext|mediumtext|blob|tinyblob|mediumblob|longblob)([[:space:]]+|\()')) {
00934
00935 // Looking for value:
00936 if (substr($parseString,0,1)=='(') {
00937 $parseString = substr($parseString,1);
00938 if ($result['value'] = $this->nextPart($parseString,'^([^)]*)')) {
00939 $parseString = ltrim(substr($parseString,1));
00940 } else return $this->parseError('No end-parenthesis for value found!',$parseString);
00941 }
00942
00943 // Looking for keywords
00944 while($keyword = $this->nextPart($parseString,'^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\))')) {
00945 $keywordCmp = strtoupper(ereg_replace('[[:space:]]*','',$keyword));
00946
00947 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
00948
00949 switch($keywordCmp) {
00950 case 'DEFAULT':
00951 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
00952 break;
00953 }
00954 }
00955 } else return $this->parseError('Field type unknown!',$parseString);
00956
00957 return $result;
00958 }
00959
00960
00961
00962
00963
00964
00965
00966
00967
00968
00969
00970 /************************************
00971 *
00972 * Parsing: Helper functions
00973 *
00974 ************************************/
00975
00985 function nextPart(&$parseString,$regex,$trimAll=FALSE) {
00986 if (eregi($regex,$parseString.' ', $reg)) { // Adding space char because [[:space:]]+ is often a requirement in regex's
00987 $parseString = ltrim(substr($parseString,strlen($reg[$trimAll?0:1])));
00988 return $reg[1];
00989 }
00990 }
00991
00999 function getValue(&$parseString,$comparator='') {
01000 if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(ereg_replace('[[:space:]]','',$comparator)))) { // List of values:
01001 if ($this->nextPart($parseString,'^([(])')) {
01002 $listValues = array();
01003 $comma=',';
01004
01005 while($comma==',') {
01006 $listValues[] = $this->getValue($parseString);
01007 $comma = $this->nextPart($parseString,'^([,])');
01008 }
01009
01010 $out = $this->nextPart($parseString,'^([)])');
01011 if ($out) {
01012 if ($comparator=='_LIST') {
01013 $kVals = array();
01014 foreach ($listValues as $vArr) {
01015 $kVals[] = $vArr[0];
01016 }
01017 return $kVals;
01018 } else {
01019 return $listValues;
01020 }
01021 } else return array($this->parseError('No ) parenthesis in list',$parseString));
01022 } else return array($this->parseError('No ( parenthesis starting the list',$parseString));
01023
01024 } else { // Just plain string value, in quotes or not:
01025
01026 // Quote?
01027 $firstChar = substr($parseString,0,1);
01028
01029 switch($firstChar) {
01030 case '"':
01031 return array($this->getValueInQuotes($parseString,'"'),'"');
01032 break;
01033 case "'":
01034 return array($this->getValueInQuotes($parseString,"'"),"'");
01035 break;
01036 default:
01037 if (eregi('^([[:alnum:]._-]+)',$parseString, $reg)) {
01038 $parseString = ltrim(substr($parseString,strlen($reg[0])));
01039 return array($reg[1]);
01040 }
01041 break;
01042 }
01043 }
01044 }
01045
01054 function getValueInQuotes(&$parseString,$quote) {
01055
01056 $parts = explode($quote,substr($parseString,1));
01057 $buffer = '';
01058 foreach($parts as $k => $v) {
01059 $buffer.=$v;
01060
01061 unset($reg);
01062 ereg('[\]*$',$v,$reg);
01063 if (strlen($reg[0])%2) {
01064 $buffer.=$quote;
01065 } else {
01066 $parseString = ltrim(substr($parseString,strlen($buffer)+2));
01067 return $this->parseStripslashes($buffer);
01068 }
01069 }
01070 }
01071
01079 function parseStripslashes($str) {
01080 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
01081 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
01082
01083 return str_replace($search, $replace, $str);
01084 }
01085
01093 function compileAddslashes($str) {
01094 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
01095 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
01096
01097 return str_replace($search, $replace, $str);
01098 }
01099
01107 function parseError($msg,$restQuery) {
01108 $this->parse_error = 'SQL engine parse ERROR: '.$msg.': near "'.substr($restQuery,0,50).'"';
01109 return $this->parse_error;
01110 }
01111
01121 function trimSQL($str) {
01122 return trim(ereg_replace('[[:space:];]*$','',$str)).' ';
01123 }
01124
01125
01126
01127
01128
01129
01130
01131
01132
01133
01134
01135
01136
01137
01138
01139
01140
01141
01149 function compileSQL($components) {
01150 switch($components['type']) {
01151 case 'SELECT':
01152 $query = $this->compileSELECT($components);
01153 break;
01154 case 'UPDATE':
01155 $query = $this->compileUPDATE($components);
01156 break;
01157 case 'INSERT':
01158 $query = $this->compileINSERT($components);
01159 break;
01160 case 'DELETE':
01161 $query = $this->compileDELETE($components);
01162 break;
01163 case 'EXPLAIN':
01164 $query = 'EXPLAIN '.$this->compileSELECT($components);
01165 break;
01166 case 'DROPTABLE':
01167 $query = 'DROP TABLE'.($components['ifExists']?' IF EXISTS':'').' '.$components['TABLE'];
01168 break;
01169 case 'CREATETABLE':
01170 $query = $this->compileCREATETABLE($components);
01171 break;
01172 case 'ALTERTABLE':
01173 $query = $this->compileALTERTABLE($components);
01174 break;
01175 }
01176
01177 return $query;
01178 }
01179
01187 function compileSELECT($components) {
01188
01189
01190 $where = $this->compileWhereClause($components['WHERE']);
01191 $groupBy = $this->compileFieldList($components['GROUPBY']);
01192 $orderBy = $this->compileFieldList($components['ORDERBY']);
01193 $limit = $components['LIMIT'];
01194
01195
01196 $query = 'SELECT '.($components['STRAIGHT_JOIN'] ? $components['STRAIGHT_JOIN'].'' : '').'
01197 '.$this->compileFieldList($components['SELECT']).'
01198 FROM '.$this->compileFromTables($components['FROM']).
01199 (strlen($where)?'
01200 WHERE '.$where : '').
01201 (strlen($groupBy)?'
01202 GROUP BY '.$groupBy : '').
01203 (strlen($orderBy)?'
01204 ORDER BY '.$orderBy : '').
01205 (strlen($limit)?'
01206 LIMIT '.$limit : '');
01207
01208 return $query;
01209 }
01210
01218 function compileUPDATE($components) {
01219
01220
01221 $where = $this->compileWhereClause($components['WHERE']);
01222
01223
01224 $fields = array();
01225 foreach($components['FIELDS'] as $fN => $fV) {
01226 $fields[]=$fN.'='.$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
01227 }
01228
01229
01230 $query = 'UPDATE '.$components['TABLE'].' SET
01231 '.implode(',
01232 ',$fields).'
01233 '.(strlen($where)?'
01234 WHERE '.$where : '');
01235
01236 return $query;
01237 }
01238
01246 function compileINSERT($components) {
01247
01248 if ($components['VALUES_ONLY']) {
01249
01250 $fields = array();
01251 foreach($components['VALUES_ONLY'] as $fV) {
01252 $fields[]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
01253 }
01254
01255
01256 $query = 'INSERT INTO '.$components['TABLE'].'
01257 VALUES
01258 ('.implode(',
01259 ',$fields).')';
01260 } else {
01261
01262 $fields = array();
01263 foreach($components['FIELDS'] as $fN => $fV) {
01264 $fields[$fN]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
01265 }
01266
01267
01268 $query = 'INSERT INTO '.$components['TABLE'].'
01269 ('.implode(',
01270 ',array_keys($fields)).')
01271 VALUES
01272 ('.implode(',
01273 ',$fields).')';
01274 }
01275
01276 return $query;
01277 }
01278
01286 function compileDELETE($components) {
01287
01288
01289 $where = $this->compileWhereClause($components['WHERE']);
01290
01291
01292 $query = 'DELETE FROM '.$components['TABLE'].
01293 (strlen($where)?'
01294 WHERE '.$where : '');
01295
01296 return $query;
01297 }
01298
01306 function compileCREATETABLE($components) {
01307
01308
01309 $fieldsKeys = array();
01310 foreach($components['FIELDS'] as $fN => $fCfg) {
01311 $fieldsKeys[]=$fN.' '.$this->compileFieldCfg($fCfg['definition']);
01312 }
01313 foreach($components['KEYS'] as $kN => $kCfg) {
01314 if ($kN == 'PRIMARYKEY') {
01315 $fieldsKeys[]='PRIMARY KEY ('.implode(',', $kCfg).')';
01316 } else {
01317 $fieldsKeys[]='KEY '.$kN.' ('.implode(',', $kCfg).')';
01318 }
01319 }
01320
01321
01322 $query = 'CREATE TABLE '.$components['TABLE'].' (
01323 '.implode(',
01324 ', $fieldsKeys).'
01325 )'.($components['tableType'] ? ' TYPE='.$components['tableType'] : '');
01326
01327 return $query;
01328 }
01329
01337 function compileALTERTABLE($components) {
01338
01339
01340 $query = 'ALTER TABLE '.$components['TABLE'].' '.$components['action'].' '.($components['FIELD']?$components['FIELD']:$components['KEY']);
01341
01342
01343 switch(strtoupper(ereg_replace('[[:space:]]','',$components['action']))) {
01344 case 'ADD':
01345 $query.=' '.$this->compileFieldCfg($components['definition']);
01346 break;
01347 case 'CHANGE':
01348 $query.=' '.$components['newField'].' '.$this->compileFieldCfg($components['definition']);
01349 break;
01350 case 'DROP':
01351 case 'DROPKEY':
01352 break;
01353 case 'ADDKEY':
01354 case 'ADDPRIMARYKEY':
01355 $query.=' ('.implode(',',$components['fields']).')';
01356 break;
01357 }
01358
01359
01360 return $query;
01361 }
01362
01363
01364
01365
01366
01367
01368
01369
01370
01371
01372
01373
01374
01375
01376
01377
01378
01379
01380
01381
01390 function compileFieldList($selectFields) {
01391
01392
01393 $outputParts = array();
01394
01395
01396 if (is_array($selectFields)) {
01397 foreach($selectFields as $k => $v) {
01398
01399
01400 switch($v['type']) {
01401 case 'function':
01402 $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
01403 break;
01404 case 'field':
01405 $outputParts[$k] = ($v['table']?$v['table'].'.':'').$v['field'];
01406 break;
01407 }
01408
01409
01410 if ($v['as']) {
01411 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
01412 }
01413
01414
01415 if ($v['sortDir']) {
01416 $outputParts[$k].= ' '.$v['sortDir'];
01417 }
01418 }
01419 }
01420
01421
01422 return implode(', ',$outputParts);
01423 }
01424
01432 function compileFromTables($tablesArray) {
01433
01434
01435 $outputParts = array();
01436
01437
01438 if (is_array($tablesArray)) {
01439 foreach($tablesArray as $k => $v) {
01440
01441
01442 $outputParts[$k] = $v['table'];
01443
01444
01445 if ($v['as']) {
01446 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
01447 }
01448
01449 if (is_array($v['JOIN'])) {
01450 $outputParts[$k].= ' '.$v['JOIN']['type'].' '.$v['JOIN']['withTable'].' ON '.implode('=',$v['JOIN']['ON']);
01451 }
01452
01453 }
01454 }
01455
01456
01457 return implode(', ',$outputParts);
01458 }
01459
01468 function compileWhereClause($clauseArray) {
01469
01470
01471 $output='';
01472
01473
01474 if (is_array($clauseArray)) {
01475 foreach($clauseArray as $k => $v) {
01476
01477
01478 $output.=$v['operator'] ? ' '.$v['operator'] : '';
01479
01480
01481 if (is_array($v['sub'])) {
01482 $output.=' ('.trim($this->compileWhereClause($v['sub'])).')';
01483 } else {
01484
01485
01486 $output.=' '.trim($v['modifier'].' '.($v['table']?$v['table'].'.':'').$v['field']);
01487
01488
01489 if ($v['calc']) {
01490 $output.=$v['calc'].$v['calc_value'][1].$this->compileAddslashes($v['calc_value'][0]).$v['calc_value'][1];
01491 }
01492
01493
01494 if ($v['comparator']) {
01495 $output.=' '.$v['comparator'];
01496
01497
01498 if (t3lib_div::inList('NOTIN,IN',strtoupper(ereg_replace('[[:space:]]','',$v['comparator'])))) {
01499 $valueBuffer = array();
01500 foreach($v['value'] as $realValue) {
01501 $valueBuffer[]=$realValue[1].$this->compileAddslashes($realValue[0]).$realValue[1];
01502 }
01503 $output.=' ('.trim(implode(',',$valueBuffer)).')';
01504 } else {
01505 $output.=' '.$v['value'][1].$this->compileAddslashes($v['value'][0]).$v['value'][1];
01506 }
01507 }
01508 }
01509 }
01510 }
01511
01512
01513 return $output;
01514 }
01515
01522 function compileFieldCfg($fieldCfg) {
01523
01524
01525 $cfg = $fieldCfg['fieldType'];
01526
01527
01528 if (strlen($fieldCfg['value'])) {
01529 $cfg.='('.$fieldCfg['value'].')';
01530 }
01531
01532
01533 if (is_array($fieldCfg['featureIndex'])) {
01534 foreach($fieldCfg['featureIndex'] as $featureDef) {
01535 $cfg.=' '.$featureDef['keyword'];
01536
01537
01538 if (is_array($featureDef['value'])) {
01539 $cfg.=' '.$featureDef['value'][1].$this->compileAddslashes($featureDef['value'][0]).$featureDef['value'][1];
01540 }
01541 }
01542 }
01543
01544
01545 return $cfg;
01546 }
01547
01548
01549
01550
01551
01552
01553
01554
01555
01556
01557
01558
01559
01560
01561
01562
01563
01571 function debug_parseSQLpart($part,$str) {
01572 switch($part) {
01573 case 'SELECT':
01574 return $this->debug_parseSQLpartCompare($str,$this->compileFieldList($this->parseFieldList($str)));
01575 break;
01576 case 'FROM':
01577 return $this->debug_parseSQLpartCompare($str,$this->compileFromTables($this->parseFromTables($str)));
01578 break;
01579 case 'WHERE':
01580 return $this->debug_parseSQLpartCompare($str,$this->compileWhereClause($this->parseWhereClause($str)));
01581 break;
01582 }
01583 }
01584
01593 function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE) {
01594 if ($caseInsensitive) {
01595 $str1 = strtoupper($str);
01596 $str2 = strtoupper($newStr);
01597 } else {
01598 $str1 = $str;
01599 $str2 = $newStr;
01600 }
01601
01602
01603 $search = array('\0', '\n', '\r', '\Z');
01604 $replace = array("\x00", "\x0a", "\x0d", "\x1a");
01605 $str1 = str_replace($search, $replace, $str1);
01606 $str2 = str_replace($search, $replace, $str2);
01607
01608 # Normally, commented out since they are needed only in tricky cases...
01609 # $str1 = stripslashes($str1);
01610 # $str2 = stripslashes($str2);
01611
01612 if (strcmp(ereg_replace('[[:space:]]','',$this->trimSQL($str1)),ereg_replace('[[:space:]]','',$this->trimSQL($str2)))) {
01613 return array(
01614 ereg_replace('[[:space:]]+',' ',$str),
01615 ereg_replace('[[:space:]]+',' ',$newStr),
01616 );
01617 }
01618 }
01619
01626 function debug_testSQL($SQLquery) {
01627 # return $SQLquery;
01628 #debug(array($SQLquery));
01629
01630
01631 $parseResult = $this->parseSQL($SQLquery);
01632
01633
01634 if (is_array($parseResult)) {
01635
01636
01637 $newQuery = $this->compileSQL($parseResult);
01638
01639
01640 $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
01641
01642
01643 if (!is_array($testResult)) {
01644 return $newQuery;
01645 } else {
01646 debug(array('ERROR MESSAGE'=>'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult),'SQL parsing failed:');
01647 exit;
01648 }
01649 } else {
01650 debug(array('query' => $SQLquery, 'ERROR MESSAGE'=>$parseResult),'SQL parsing failed:');
01651 exit;
01652 }
01653 }
01654 }
01655
01656
01657 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']) {
01658 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']);
01659 }
01660 ?>
1.4.6