test/libraries/common/PMA_formatSql_test.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Test for PMA_formatSql from common.lib.php
   5  *
   6  * @package phpMyAdmin-test
   7  * @version $Id: PMA_formatSql_test.php
   8  * @group common.lib-tests
   9  */
  10
  11 /*
  12  * Include to test.
  13  */
  14 require_once 'libraries/common.lib.php';
  15 require_once 'libraries/sqlparser.lib.php';
  16
  17 class PMA_formatSql_test extends PHPUnit_Framework_TestCase
  18 {
  19
  20     /**
  21      * temporary variable for globals array
  22      */
  23     protected $tmpCfg;
  24
  25     /**
  26      * temporary variable for session array
  27      */
  28     protected $tmpSession;
  29
  30     /**
  31      * storing globals and session
  32      */
  33     public function setUp() {
  34         global $cfg;
  35         $this->tmpCfg = $cfg;
  36     }
  37
  38     /**
  39      * recovering globals and session
  40      */
  41     public function tearDown() {
  42         global $cfg;
  43         $cfg = $this->tmpCfg;
  44     }
  45
  46     function testFormatSQLNotArray(){
  47         global $cfg;
  48         $cfg['SQP']['fmtType'] = 'html';
  49         $sql = "SELECT * FROM tTable;";
  50         $this->assertEquals("<pre>\n$sql\n</pre>",PMA_formatSql($sql));
  51     }
  52
  53     function testFormatSQLfmTypeHtml_1(){
  54         global $cfg;
  55         $cfg['SQP']['fmtType'] = 'html';
  56         $cfg['MySQLManualType'] = 'viewable';
  57
  58         $sql = array (
  59           'raw' => 'SELECT 1;',
  60           0 =>
  61           array (
  62             'type' => 'alpha_reservedWord',
  63             'data' => 'SELECT',
  64             'pos' => 6,
  65             'forbidden' => true,
  66           ),
  67           1 =>
  68           array (
  69             'type' => 'digit_integer',
  70             'data' => '1',
  71             'pos' => 8,
  72           ),
  73           2 =>
  74           array (
  75             'type' => 'punct_queryend',
  76             'data' => ';',
  77             'pos' => 0,
  78           ),
  79           'len' => 3,
  80         );
  81         $unparsed = "SELECT 1;";
  82         $expected = '<span class="syntax"><span class="inner_sql"><span class="syntax_alpha syntax_alpha_reservedWord">SELECT</span></a> <span class="syntax_digit syntax_digit_integer">1</span> <span class="syntax_punct syntax_punct_queryend">;</span><br /><br /></span></span>';
  83
  84         $this->assertEquals($expected,PMA_formatSql($sql, $unparsed));
  85     }
  86
  87     function testFormatSQLfmTypeHtml_2(){
  88         global $cfg;
  89         $cfg['SQP']['fmtType'] = 'html';
  90         $cfg['MySQLManualType'] = 'viewable';
  91
  92         $unparsed = "SELECT * from `tTable`;";
  93         $sql = array (
  94           'raw' => $unparsed,
  95           0 =>
  96           array (
  97             'type' => 'alpha_reservedWord',
  98             'data' => 'SELECT',
  99             'pos' => 6,
 100             'forbidden' => true,
 101           ),
 102           1 =>
 103           array (
 104             'type' => 'punct',
 105             'data' => '*',
 106             'pos' => 0,
 107           ),
 108           2 =>
 109           array (
 110             'type' => 'alpha_reservedWord',
 111             'data' => 'from',
 112             'pos' => 13,
 113             'forbidden' => true,
 114           ),
 115           3 =>
 116           array (
 117             'type' => 'quote_backtick',
 118             'data' => '`tTable`',
 119             'pos' => 0,
 120           ),
 121           4 =>
 122           array (
 123             'type' => 'punct_queryend',
 124             'data' => ';',
 125             'pos' => 0,
 126           ),
 127           'len' => 5,
 128         );
 129         $expected = '<span class="syntax"><span class="inner_sql"><span class="syntax_alpha syntax_alpha_reservedWord">SELECT</span></a>  <span class="syntax_punct">*</span> <br /><span class="syntax_alpha syntax_alpha_reservedWord">FROM</span>  <span class="syntax_quote syntax_quote_backtick">`tTable`</span> <span class="syntax_punct syntax_punct_queryend">;</span><br /><br /></span></span>';
 130
 131         $this->assertEquals($expected,PMA_formatSql($sql, $unparsed));
 132     }
 133
 134     function testFormatSQLfmTypeHtml_3(){
 135         global $cfg;
 136         $cfg['SQP']['fmtType'] = 'html';
 137         $cfg['MySQLManualType'] = 'viewable';
 138
 139         $unparsed = 'SELECT * FROM `tTable_A` A INNER JOIN `tTable_B` B ON B.ID = A.ID;';
 140         $sql = array (
 141           'raw' => $unparsed,
 142           0 =>
 143           array (
 144             'type' => 'alpha_reservedWord',
 145             'data' => 'SELECT',
 146             'pos' => 6,
 147             'forbidden' => true,
 148           ),
 149           1 =>
 150           array (
 151             'type' => 'punct',
 152             'data' => '*',
 153             'pos' => 0,
 154           ),
 155           2 =>
 156           array (
 157             'type' => 'alpha_reservedWord',
 158             'data' => 'FROM',
 159             'pos' => 13,
 160             'forbidden' => true,
 161           ),
 162           3 =>
 163           array (
 164             'type' => 'quote_backtick',
 165             'data' => '`tTable_A`',
 166             'pos' => 0,
 167           ),
 168           4 =>
 169           array (
 170             'type' => 'alpha_identifier',
 171             'data' => 'A',
 172             'pos' => 26,
 173             'forbidden' => false,
 174           ),
 175           5 =>
 176           array (
 177             'type' => 'alpha_reservedWord',
 178             'data' => 'INNER',
 179             'pos' => 32,
 180             'forbidden' => true,
 181           ),
 182           6 =>
 183           array (
 184             'type' => 'alpha_reservedWord',
 185             'data' => 'JOIN',
 186             'pos' => 37,
 187             'forbidden' => true,
 188           ),
 189           7 =>
 190           array (
 191             'type' => 'quote_backtick',
 192             'data' => '`tTable_B`',
 193             'pos' => 0,
 194           ),
 195           8 =>
 196           array (
 197             'type' => 'alpha_identifier',
 198             'data' => 'B',
 199             'pos' => 50,
 200             'forbidden' => false,
 201           ),
 202           9 =>
 203           array (
 204             'type' => 'alpha_reservedWord',
 205             'data' => 'ON',
 206             'pos' => 53,
 207             'forbidden' => true,
 208           ),
 209           10 =>
 210           array (
 211             'type' => 'alpha_identifier',
 212             'data' => 'B',
 213             'pos' => 55,
 214             'forbidden' => false,
 215           ),
 216           11 =>
 217           array (
 218             'type' => 'punct_qualifier',
 219             'data' => '.',
 220             'pos' => 0,
 221           ),
 222           12 =>
 223           array (
 224             'type' => 'alpha_identifier',
 225             'data' => 'ID',
 226             'pos' => 58,
 227             'forbidden' => false,
 228           ),
 229           13 =>
 230           array (
 231             'type' => 'punct',
 232             'data' => '=',
 233             'pos' => 0,
 234           ),
 235           14 =>
 236           array (
 237             'type' => 'alpha_identifier',
 238             'data' => 'A',
 239             'pos' => 62,
 240             'forbidden' => false,
 241           ),
 242           15 =>
 243           array (
 244             'type' => 'punct_qualifier',
 245             'data' => '.',
 246             'pos' => 0,
 247           ),
 248           16 =>
 249           array (
 250             'type' => 'alpha_identifier',
 251             'data' => 'ID',
 252             'pos' => 65,
 253             'forbidden' => false,
 254           ),
 255           17 =>
 256           array (
 257             'type' => 'punct_queryend',
 258             'data' => ';',
 259             'pos' => 0,
 260           ),
 261           'len' => 18,
 262         );
 263
 264         $expected = '<span class="syntax"><span class="inner_sql"><span class="syntax_alpha syntax_alpha_reservedWord">SELECT</span></a>  <span class="syntax_punct">*</span> <br /><span class="syntax_alpha syntax_alpha_reservedWord">FROM</span>  <span class="syntax_quote syntax_quote_backtick">`tTable_A`</span> <span class="syntax_alpha syntax_alpha_identifier">A</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">INNER</span>  <span class="syntax_alpha syntax_alpha_reservedWord">JOIN</span>  <span class="syntax_quote syntax_quote_backtick">`tTable_B`</span> <span class="syntax_alpha syntax_alpha_identifier">B</span> <span class="syntax_alpha syntax_alpha_reservedWord">ON</span> <span class="syntax_alpha syntax_alpha_identifier">B</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">ID</span> <span class="syntax_punct">=</span></a> <span class="syntax_alpha syntax_alpha_identifier">A</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">ID</span><span class="syntax_punct syntax_punct_queryend">;</span><br /><br /></span></span>';
 265
 266         $this->assertEquals($expected,PMA_formatSql($sql, $unparsed));
 267     }
 268
 269     function testFormatSQLfmTypeText_1(){
 270         global $cfg;
 271         $cfg['SQP']['fmtType'] = 'text';
 272         $cfg['MySQLManualType'] = 'viewable';
 273
 274         $sql = array (
 275           'raw' => 'SELECT 1;',
 276           0 =>
 277           array (
 278             'type' => 'alpha_reservedWord',
 279             'data' => 'SELECT',
 280             'pos' => 6,
 281             'forbidden' => true,
 282           ),
 283           1 =>
 284           array (
 285             'type' => 'digit_integer',
 286             'data' => '1',
 287             'pos' => 8,
 288           ),
 289           2 =>
 290           array (
 291             'type' => 'punct_queryend',
 292             'data' => ';',
 293             'pos' => 0,
 294           ),
 295           'len' => 3,
 296         );
 297         $unparsed = "SELECT 1;";
 298         $expected = '<span class="inner_sql">SELECT</a> 1 ;<br /><br /></span>';
 299
 300         $this->assertEquals($expected,PMA_formatSql($sql, $unparsed));
 301     }
 302
 303     function testFormatSQLfmTypeText_2(){
 304         global $cfg;
 305         $cfg['SQP']['fmtType'] = 'text';
 306         $cfg['MySQLManualType'] = 'viewable';
 307
 308         $unparsed = "SELECT * from `tTable`;";
 309         $sql = array (
 310           'raw' => $unparsed,
 311           0 =>
 312           array (
 313             'type' => 'alpha_reservedWord',
 314             'data' => 'SELECT',
 315             'pos' => 6,
 316             'forbidden' => true,
 317           ),
 318           1 =>
 319           array (
 320             'type' => 'punct',
 321             'data' => '*',
 322             'pos' => 0,
 323           ),
 324           2 =>
 325           array (
 326             'type' => 'alpha_reservedWord',
 327             'data' => 'from',
 328             'pos' => 13,
 329             'forbidden' => true,
 330           ),
 331           3 =>
 332           array (
 333             'type' => 'quote_backtick',
 334             'data' => '`tTable`',
 335             'pos' => 0,
 336           ),
 337           4 =>
 338           array (
 339             'type' => 'punct_queryend',
 340             'data' => ';',
 341             'pos' => 0,
 342           ),
 343           'len' => 5,
 344         );
 345         $expected = '<span class="inner_sql">SELECT</a>  * <br />FROM  `tTable` ;<br /><br /></span>';
 346
 347         $this->assertEquals($expected,PMA_formatSql($sql, $unparsed));
 348     }
 349
 350     function testFormatSQLfmTypeText_3(){
 351         global $cfg;
 352         $cfg['SQP']['fmtType'] = 'text';
 353         $cfg['MySQLManualType'] = 'viewable';
 354
 355         $unparsed = 'SELECT * FROM `tTable_A` A INNER JOIN `tTable_B` B ON B.ID = A.ID;';
 356         $sql = array (
 357           'raw' => $unparsed,
 358           0 =>
 359           array (
 360             'type' => 'alpha_reservedWord',
 361             'data' => 'SELECT',
 362             'pos' => 6,
 363             'forbidden' => true,
 364           ),
 365           1 =>
 366           array (
 367             'type' => 'punct',
 368             'data' => '*',
 369             'pos' => 0,
 370           ),
 371           2 =>
 372           array (
 373             'type' => 'alpha_reservedWord',
 374             'data' => 'FROM',
 375             'pos' => 13,
 376             'forbidden' => true,
 377           ),
 378           3 =>
 379           array (
 380             'type' => 'quote_backtick',
 381             'data' => '`tTable_A`',
 382             'pos' => 0,
 383           ),
 384           4 =>
 385           array (
 386             'type' => 'alpha_identifier',
 387             'data' => 'A',
 388             'pos' => 26,
 389             'forbidden' => false,
 390           ),
 391           5 =>
 392           array (
 393             'type' => 'alpha_reservedWord',
 394             'data' => 'INNER',
 395             'pos' => 32,
 396             'forbidden' => true,
 397           ),
 398           6 =>
 399           array (
 400             'type' => 'alpha_reservedWord',
 401             'data' => 'JOIN',
 402             'pos' => 37,
 403             'forbidden' => true,
 404           ),
 405           7 =>
 406           array (
 407             'type' => 'quote_backtick',
 408             'data' => '`tTable_B`',
 409             'pos' => 0,
 410           ),
 411           8 =>
 412           array (
 413             'type' => 'alpha_identifier',
 414             'data' => 'B',
 415             'pos' => 50,
 416             'forbidden' => false,
 417           ),
 418           9 =>
 419           array (
 420             'type' => 'alpha_reservedWord',
 421             'data' => 'ON',
 422             'pos' => 53,
 423             'forbidden' => true,
 424           ),
 425           10 =>
 426           array (
 427             'type' => 'alpha_identifier',
 428             'data' => 'B',
 429             'pos' => 55,
 430             'forbidden' => false,
 431           ),
 432           11 =>
 433           array (
 434             'type' => 'punct_qualifier',
 435             'data' => '.',
 436             'pos' => 0,
 437           ),
 438           12 =>
 439           array (
 440             'type' => 'alpha_identifier',
 441             'data' => 'ID',
 442             'pos' => 58,
 443             'forbidden' => false,
 444           ),
 445           13 =>
 446           array (
 447             'type' => 'punct',
 448             'data' => '=',
 449             'pos' => 0,
 450           ),
 451           14 =>
 452           array (
 453             'type' => 'alpha_identifier',
 454             'data' => 'A',
 455             'pos' => 62,
 456             'forbidden' => false,
 457           ),
 458           15 =>
 459           array (
 460             'type' => 'punct_qualifier',
 461             'data' => '.',
 462             'pos' => 0,
 463           ),
 464           16 =>
 465           array (
 466             'type' => 'alpha_identifier',
 467             'data' => 'ID',
 468             'pos' => 65,
 469             'forbidden' => false,
 470           ),
 471           17 =>
 472           array (
 473             'type' => 'punct_queryend',
 474             'data' => ';',
 475             'pos' => 0,
 476           ),
 477           'len' => 18,
 478         );
 479         $expected = '<span class="inner_sql">SELECT</a>  * <br />FROM  `tTable_A` A<br />INNER  JOIN  `tTable_B` B ON B.ID =</a> A.ID;<br /><br /></span>';
 480
 481         $this->assertEquals($expected,PMA_formatSql($sql, $unparsed));
 482     }
 483
 484     function testFormatSQLfmTypeNone_1(){
 485         global $cfg;
 486         $cfg['SQP']['fmtType'] = 'none';
 487         $cfg['MySQLManualType'] = 'viewable';
 488
 489         $sql = array (
 490           'raw' => 'SELECT 1;',
 491           0 =>
 492           array (
 493             'type' => 'alpha_reservedWord',
 494             'data' => 'SELECT',
 495             'pos' => 6,
 496             'forbidden' => true,
 497           ),
 498           1 =>
 499           array (
 500             'type' => 'digit_integer',
 501             'data' => '1',
 502             'pos' => 8,
 503           ),
 504           2 =>
 505           array (
 506             'type' => 'punct_queryend',
 507             'data' => ';',
 508             'pos' => 0,
 509           ),
 510           'len' => 3,
 511         );
 512         $unparsed = "SELECT 1;";
 513
 514         $expected = "<span class=\"inner_sql\"><pre>\nSELECT 1;\n</pre></span>";
 515         $this->assertEquals($expected,PMA_formatSql($sql, $unparsed));
 516
 517         $expected = "SELECT 1;";
 518         $this->assertEquals($expected,PMA_formatSql($sql));
 519     }
 520
 521     function testFormatSQLfmTypeNone_2(){
 522         global $cfg;
 523         $cfg['SQP']['fmtType'] = 'none';
 524         $cfg['MySQLManualType'] = 'viewable';
 525
 526         $unparsed = "SELECT * from `tTable`;";
 527         $sql = array (
 528           'raw' => $unparsed,
 529           0 =>
 530           array (
 531             'type' => 'alpha_reservedWord',
 532             'data' => 'SELECT',
 533             'pos' => 6,
 534             'forbidden' => true,
 535           ),
 536           1 =>
 537           array (
 538             'type' => 'punct',
 539             'data' => '*',
 540             'pos' => 0,
 541           ),
 542           2 =>
 543           array (
 544             'type' => 'alpha_reservedWord',
 545             'data' => 'from',
 546             'pos' => 13,
 547             'forbidden' => true,
 548           ),
 549           3 =>
 550           array (
 551             'type' => 'quote_backtick',
 552             'data' => '`tTable`',
 553             'pos' => 0,
 554           ),
 555           4 =>
 556           array (
 557             'type' => 'punct_queryend',
 558             'data' => ';',
 559             'pos' => 0,
 560           ),
 561           'len' => 5,
 562         );
 563
 564         $expected = "<span class=\"inner_sql\"><pre>\nSELECT * from `tTable`;\n</pre></span>";
 565         $this->assertEquals($expected,PMA_formatSql($sql, $unparsed));
 566
 567         $expected = "SELECT * from `tTable`;";
 568         $this->assertEquals($expected,PMA_formatSql($sql));
 569     }
 570
 571     function testFormatSQLfmTypeNone_3(){
 572         global $cfg;
 573         $cfg['SQP']['fmtType'] = 'none';
 574         $cfg['MySQLManualType'] = 'viewable';
 575
 576         $unparsed = 'SELECT * FROM `tTable_A` A INNER JOIN `tTable_B` B ON B.ID = A.ID;';
 577         $sql = array (
 578           'raw' => $unparsed,
 579           0 =>
 580           array (
 581             'type' => 'alpha_reservedWord',
 582             'data' => 'SELECT',
 583             'pos' => 6,
 584             'forbidden' => true,
 585           ),
 586           1 =>
 587           array (
 588             'type' => 'punct',
 589             'data' => '*',
 590             'pos' => 0,
 591           ),
 592           2 =>
 593           array (
 594             'type' => 'alpha_reservedWord',
 595             'data' => 'FROM',
 596             'pos' => 13,
 597             'forbidden' => true,
 598           ),
 599           3 =>
 600           array (
 601             'type' => 'quote_backtick',
 602             'data' => '`tTable_A`',
 603             'pos' => 0,
 604           ),
 605           4 =>
 606           array (
 607             'type' => 'alpha_identifier',
 608             'data' => 'A',
 609             'pos' => 26,
 610             'forbidden' => false,
 611           ),
 612           5 =>
 613           array (
 614             'type' => 'alpha_reservedWord',
 615             'data' => 'INNER',
 616             'pos' => 32,
 617             'forbidden' => true,
 618           ),
 619           6 =>
 620           array (
 621             'type' => 'alpha_reservedWord',
 622             'data' => 'JOIN',
 623             'pos' => 37,
 624             'forbidden' => true,
 625           ),
 626           7 =>
 627           array (
 628             'type' => 'quote_backtick',
 629             'data' => '`tTable_B`',
 630             'pos' => 0,
 631           ),
 632           8 =>
 633           array (
 634             'type' => 'alpha_identifier',
 635             'data' => 'B',
 636             'pos' => 50,
 637             'forbidden' => false,
 638           ),
 639           9 =>
 640           array (
 641             'type' => 'alpha_reservedWord',
 642             'data' => 'ON',
 643             'pos' => 53,
 644             'forbidden' => true,
 645           ),
 646           10 =>
 647           array (
 648             'type' => 'alpha_identifier',
 649             'data' => 'B',
 650             'pos' => 55,
 651             'forbidden' => false,
 652           ),
 653           11 =>
 654           array (
 655             'type' => 'punct_qualifier',
 656             'data' => '.',
 657             'pos' => 0,
 658           ),
 659           12 =>
 660           array (
 661             'type' => 'alpha_identifier',
 662             'data' => 'ID',
 663             'pos' => 58,
 664             'forbidden' => false,
 665           ),
 666           13 =>
 667           array (
 668             'type' => 'punct',
 669             'data' => '=',
 670             'pos' => 0,
 671           ),
 672           14 =>
 673           array (
 674             'type' => 'alpha_identifier',
 675             'data' => 'A',
 676             'pos' => 62,
 677             'forbidden' => false,
 678           ),
 679           15 =>
 680           array (
 681             'type' => 'punct_qualifier',
 682             'data' => '.',
 683             'pos' => 0,
 684           ),
 685           16 =>
 686           array (
 687             'type' => 'alpha_identifier',
 688             'data' => 'ID',
 689             'pos' => 65,
 690             'forbidden' => false,
 691           ),
 692           17 =>
 693           array (
 694             'type' => 'punct_queryend',
 695             'data' => ';',
 696             'pos' => 0,
 697           ),
 698           'len' => 18,
 699         );
 700
 701         $expected = "<span class=\"inner_sql\"><pre>\nSELECT * FROM `tTable_A` A INNER JOIN `tTable_B` B ON B.ID = A.ID;\n</pre></span>";
 702         $this->assertEquals($expected,PMA_formatSql($sql, $unparsed));
 703
 704         $expected = 'SELECT * FROM `tTable_A` A INNER JOIN `tTable_B` B ON B.ID = A.ID;';
 705         $this->assertEquals($expected,PMA_formatSql($sql));
 706     }
 707
 708     function testFormatSQLWithoutType(){
 709         global $cfg;
 710         $cfg['SQP']['fmtType'] = '';
 711         $cfg['MySQLManualType'] = 'viewable';
 712         $sql = array (
 713           'raw' => 'SELECT 1;',
 714           0 =>
 715           array (
 716             'type' => 'alpha_reservedWord',
 717             'data' => 'SELECT',
 718             'pos' => 6,
 719             'forbidden' => true,
 720           ),
 721           1 =>
 722           array (
 723             'type' => 'digit_integer',
 724             'data' => '1',
 725             'pos' => 8,
 726           ),
 727           2 =>
 728           array (
 729             'type' => 'punct_queryend',
 730             'data' => ';',
 731             'pos' => 0,
 732           ),
 733           'len' => 3,
 734         );
 735         $this->assertEmpty(PMA_formatSql($sql));
 736     }
 737
 738     function testFormatSQLError(){
 739         global $SQP_errorString;
 740         $SQP_errorString = true;
 741         $sql = array("raw" => "& \" < >");
 742         $this->assertEquals("&amp; &quot; &lt; &gt;",PMA_formatSql($sql));
 743         $SQP_errorString = false;
 744     }
 745 }