parse() public method

It parses the given SQL statement and generates a detailled output array for every part of the statement. The method can also generate [position] fields within the output, which hold the character position for every statement part. The calculation of the positions needs some time, if you don't need positions in your application, set the parameter to false.
public parse ( String $sql, boolean $calcPositions = false ) : array
$sql String The SQL statement.
$calcPositions boolean True, if the output should contain [position], false otherwise.
return array An associative array with all meta information about the SQL statement.
コード例 #1
0
 /**
  * @throws \Exception
  *
  * @return string
  */
 public function convert()
 {
     $parsed = $this->sqlParser->parse($this->sql);
     if (false === $parsed) {
         throw new \Exception('SQL query is not valid');
     }
     $results = [];
     foreach ($parsed as $section => $data) {
         if ($this->converterFactory->canCreate($section)) {
             $converter = $this->converterFactory->create($section);
             $results = array_merge($results, $converter->convert($data));
         }
     }
     $table = current(array_filter($results, function ($item) {
         return 'table' === $item['name'];
     }));
     unset($results[array_search($table, $results)]);
     array_unshift($results, $table);
     foreach ($results as $function) {
         $args = isset($function['args']) ? $function['args'] : [];
         $this->generator->addFunction($function['name'], $args);
     }
     $this->generator->addFunction('get');
     return $this->generator->generate();
 }
コード例 #2
0
 public function testIssue56()
 {
     // optimizer/index hints
     // TODO: not solved
     $parser = new PHPSQLParser();
     $sql = "insert /* +APPEND */ into TableName (Col1,col2) values(1,'pol')";
     $parser->parse($sql, true);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue56a.serialized');
     $this->assertEquals($expected, $p, 'optimizer hint within INSERT');
     // optimizer/index hints
     // TODO: not solved
     $parser = new PHPSQLParser();
     $sql = "insert /* a comment -- haha */ into TableName (Col1,col2) values(1,'pol')";
     $parser->parse($sql, true);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue56a.serialized');
     $this->assertEquals($expected, $p, 'multiline comment with inline comment inside');
     // inline comment
     // TODO: not solved
     $sql = "SELECT acol -- an inline comment\n        FROM --another comment\n        table\n        WHERE x = 1";
     $parser->parse($sql, true);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue56b.serialized');
     $this->assertEquals($expected, $p, 'inline comment should not fail, issue 56');
     // inline comment
     // TODO: not solved
     $sql = "SELECT acol -- an /*inline comment\n        FROM --another */comment\n        table\n        WHERE x = 1";
     $parser->parse($sql, true);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue56b.serialized');
     $this->assertEquals($expected, $p, 'inline comment with multiline comment inside');
 }
コード例 #3
0
ファイル: selectTest.php プロジェクト: simha95/php-sql-parser
 public function testSelect()
 {
     $parser = new PHPSQLParser();
     $sql = 'SELECT
     1';
     $p = $parser->parse($sql);
     $this->assertEquals(1, count($p));
     $this->assertEquals(1, count($p['SELECT']));
     $this->assertEquals('const', $p['SELECT'][0]['expr_type']);
     $this->assertEquals('1', $p['SELECT'][0]['base_expr']);
     $this->assertEquals('', $p['SELECT'][0]['sub_tree']);
     $sql = 'SELECT 1+2 c1, 1+2 as c2, 1+2,  sum(a) sum_a_alias,a,a an_alias, a as another_alias,terminate
               from some_table an_alias
     	where d > 5;';
     $parser->parse($sql);
     $p = $parser->parsed;
     $this->assertEquals(3, count($p));
     $this->assertEquals(8, count($p['SELECT']));
     $this->assertEquals('terminate', $p['SELECT'][count($p['SELECT']) - 1]['base_expr']);
     $this->assertEquals(3, count($p));
     $this->assertEquals(1, count($p['FROM']));
     $this->assertEquals(3, count($p['WHERE']));
     $parser->parse('SELECT NOW( ),now(),sysdate( ),sysdate () as now');
     $this->assertEquals('sysdate', $parser->parsed['SELECT'][3]['base_expr']);
     $sql = " SELECT a.*, surveyls_title, surveyls_description, surveyls_welcometext, surveyls_url  FROM SURVEYS AS a INNER JOIN SURVEYS_LANGUAGESETTINGS on (surveyls_survey_id=a.sid and surveyls_language=a.language)  order by active DESC, surveyls_title";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'select1.serialized');
     $this->assertEquals($expected, $p, 'a test for ref_clauses');
     $sql = "SELECT pl_namespace,pl_title FROM `pagelinks` WHERE pl_from = '1' FOR UPDATE";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'select2.serialized');
     $this->assertEquals($expected, $p, 'select for update');
 }
コード例 #4
0
 public function testSubselect()
 {
     $parser = new PHPSQLParser();
     $sql = 'SELECT (select colA FRom TableA) as b From test t';
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'subselect1.serialized');
     $this->assertEquals($expected, $p, 'sub-select with alias');
     $sql = 'SELECT a.uid, a.users_name FROM USERS AS a LEFT JOIN (SELECT uid AS id FROM USER_IN_GROUPS WHERE ugid = 1) AS b ON a.uid = b.id WHERE id IS NULL ORDER BY a.users_name';
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'subselect2.serialized');
     $this->assertEquals($expected, $p, 'sub-select as table replacement with alias');
 }
コード例 #5
0
 /**
  * @return string
  */
 public function convert()
 {
     $parsed = $this->sqlParser->parse($this->sql);
     $builderParts = [];
     foreach ($parsed as $section => $data) {
         if ($this->converterFactory->canCreate($section)) {
             $converter = $this->converterFactory->create($section);
             $builderParts[$section] = $converter->convert($data);
         }
     }
     return $this->buildFromParts($builderParts);
 }
コード例 #6
0
 public function testIssue21()
 {
     $parser = new PHPSQLParser();
     $sql = 'SELECT  SUM( 10 ) as test FROM account';
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'issue21.serialized');
     $this->assertEquals($expected, $p, 'only space characters within SQL statement');
     $sql = "SELECT\tSUM( 10 ) \tas test FROM account";
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'issue21.serialized');
     // should be the same as above
     $this->assertEquals($expected, $p, 'tab character within SQL statement');
 }
コード例 #7
0
 public function testIssue40()
 {
     $parser = new PHPSQLParser();
     $sql = "select a from t where x = \"a'b\\cd\" and y = 'ef\"gh'";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue40a.serialized');
     $this->assertEquals($expected, $p, 'escaped characters 1');
     $sql = "select a from t where x = \"abcd\" and y = 'efgh'";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue40b.serialized');
     $this->assertEquals($expected, $p, 'escaped characters 2');
 }
コード例 #8
0
ファイル: updateTest.php プロジェクト: simha95/php-sql-parser
 public function testUpdate()
 {
     $parser = new PHPSQLParser();
     $sql = "UPDATE table1 SET field1='foo' WHERE field2='bar' AND id=(SELECT id FROM test1 t where t.field1=(SELECT id from test2 t2 where t2.field = 'foo'))";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'update1.serialized');
     $this->assertEquals($expected, $p, 'update with a sub-select');
     $sql = "update SETTINGS_GLOBAL set stg_value='' where stg_name='force_ssl'";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'update2.serialized');
     $this->assertEquals($expected, $p, 'simple update with strings');
 }
コード例 #9
0
 public function testIssue55()
 {
     $parser = new PHPSQLParser();
     $sql = "GROUP BY a, b, table.c";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue55a.serialized');
     $this->assertEquals($expected, $p, 'partial SQL statement - group by clause');
     $sql = "ORDER BY a ASC, b DESC, table.c ASC";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue55b.serialized');
     $this->assertEquals($expected, $p, 'partial SQL statement - order by clause');
 }
コード例 #10
0
 public function testIssue34()
 {
     $parser = new PHPSQLParser();
     $sql = "SELECT * FROM cache as t";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue34a.serialized');
     $this->assertEquals($expected, $p, 'SELECT statement with keyword CACHE as tablename');
     $sql = "INSERT INTO CACHE VALUES (1);";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue34b.serialized');
     $this->assertEquals($expected, $p, 'INSERT statement with keyword CACHE as tablename');
 }
コード例 #11
0
 /**
  * @return QueryInterface
  */
 protected function getDecoratedQuery()
 {
     if ($this->query === null) {
         $this->query = new Query($this->sql, $this->parser->parse($this->sql));
     }
     return $this->query;
 }
コード例 #12
0
 /**
  * Will inspect the given $sql and write problems to the storage
  *
  * @param $sql
  */
 public function inspect($sql)
 {
     $result = $this->sqlParser->parse($sql);
     $normalizedSql = $this->getNormalizedSql($result);
     $hash = $this->getQueryHash($normalizedSql);
     if (in_array($hash, $this->queryWhitelist)) {
         return;
     }
     $problems = $this->recursivelyGetProblems($result);
     if (empty($problems)) {
         return;
     }
     list($code, $trace) = $this->traceProvider->getTraceData();
     $document = $this->mightBeFalsePositive($problems) ? 'issues' : 'problem';
     $this->storage->addDocument($document, $hash, ['route' => $this->routeProvider->getRoute(), 'problems' => $problems, 'code' => $code, 'sql' => $sql, 'trace' => $trace, 'normalized' => $normalizedSql]);
 }
コード例 #13
0
 public function testMixedQuotes()
 {
     $parser = new PHPSQLParser();
     $sql = 'SELECT ISNULL(\'"\') AS foo FROM bar;';
     $parser->parse($sql);
     $this->assertEquals('\'"\'', $parser->parsed['SELECT'][0]['sub_tree'][0]['base_expr'], "Mixed quotes test failed");
 }
コード例 #14
0
 public function testBacktick()
 {
     $parser = new PHPSQLParser();
     $sql = 'SELECT c1.`some_column` or `c1`.`another_column` or c1.`some column` as `an alias`
               from some_table an_alias group by `an alias`, `alias2`;';
     $parser->parse($sql);
     $p = $parser->parsed;
     $this->assertEquals('`an alias`', $parser->parsed['SELECT'][0]['alias']['name']);
     $this->assertEquals('c1.`some column`', $parser->parsed['SELECT'][0]['sub_tree'][4]['base_expr']);
     $this->assertEquals('alias', $parser->parsed['GROUP'][0]['expr_type']);
     $sql = "INSERT INTO test (`name`) VALUES ('ben\\'s test containing an escaped quote')";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'backtick1.serialized');
     $this->assertEquals($expected, $p, "issue 35: ben's test");
 }
コード例 #15
0
ファイル: insertTest.php プロジェクト: simha95/php-sql-parser
 public function testInsert()
 {
     $parser = new PHPSQLParser();
     $sql = "insert into SETTINGS_GLOBAL (stg_value,stg_name) values('','force_ssl')";
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'insert1.serialized');
     $this->assertEquals($expected, $p, 'insert some data into table');
     $sql = " INSERT INTO settings_global VALUES ('DBVersion', '146')";
     $p = $parser->parse($sql, true);
     $expected = getExpectedValue(dirname(__FILE__), 'insert2.serialized');
     $this->assertEquals($expected, $p, 'insert some data into table with positions');
     $sql = "INSERT INTO surveys ( SID, OWNER_ID, ADMIN, ACTIVE, EXPIRES, STARTDATE, ADMINEMAIL, ANONYMIZED, FAXTO, FORMAT, SAVETIMINGS, TEMPLATE, LANGUAGE, DATESTAMP, USECOOKIE, ALLOWREGISTER, ALLOWSAVE, AUTOREDIRECT, ALLOWPREV, PRINTANSWERS, IPADDR, REFURL, DATECREATED, PUBLICSTATISTICS, PUBLICGRAPHS, LISTPUBLIC, HTMLEMAIL, TOKENANSWERSPERSISTENCE, ASSESSMENTS, USECAPTCHA, BOUNCE_EMAIL, EMAILRESPONSETO, EMAILNOTIFICATIONTO, TOKENLENGTH, SHOWXQUESTIONS, SHOWGROUPINFO, SHOWNOANSWER, SHOWQNUMCODE, SHOWWELCOME, SHOWPROGRESS, ALLOWJUMPS, NAVIGATIONDELAY, NOKEYBOARD, ALLOWEDITAFTERCOMPLETION ) \n        VALUES ( 32225, 1, 'André', 'N', null, null, '*****@*****.**', 'N', '', 'G', 'N', 'default', 'de-informal', 'N', 'N', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', a_function('2012-02-16','YYYY-MM-DD'), 'N', 'N', 'Y', 'Y', 'N', 'N', 'D', '*****@*****.**', '', '', 15, 'Y', 'B', 'Y', 'X', 'Y', 'Y', 'N', 0, 'N', 'N' )";
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'insert3.serialized');
     $this->assertEquals($expected, $p, 'insert with user-function');
 }
コード例 #16
0
 public function testIssue90()
 {
     $sql = 'INSERT DELAYED IGNORE INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;';
     $parser = new PHPSQLParser();
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'issue90.serialized');
     $this->assertEquals($expected, $p, 'on duplicate key problem');
 }
コード例 #17
0
 public function testIssue12()
 {
     $parser = new PHPSQLParser();
     $sql = "SELECT SQL_CALC_FOUND_ROWS SmTable.*, MATCH (SmTable.fulltextsearch_keyword) AGAINST ('google googles' WITH QUERY EXPANSION) AS keyword_score FROM SmTable WHERE SmTable.status = 'A' AND (SmTable.country_id = 1 AND SmTable.state_id = 10) AND MATCH (SmTable.fulltextsearch_keyword) AGAINST ('google googles') ORDER BY SmTable.level DESC, keyword_score DESC LIMIT 0,10";
     $p = $parser->parse($sql, true);
     $expected = getExpectedValue(dirname(__FILE__), 'issue12.serialized');
     $this->assertEquals($expected, $p, 'issue 12');
 }
コード例 #18
0
 public function testIssueGit11()
 {
     $query = "select column from table as ";
     $parser = new PHPSQLParser();
     $p = $parser->parse($query);
     $expected = getExpectedValue(dirname(__FILE__), 'issue_git11.serialized');
     $this->assertEquals($expected, $p, 'infinite loop with empty alias');
 }
コード例 #19
0
ファイル: dropTest.php プロジェクト: simha95/php-sql-parser
 public function testDrop()
 {
     $parser = new PHPSQLParser();
     $sql = "drop table if exists xyz cascade";
     $p = $parser->parse($sql, true);
     $expected = getExpectedValue(dirname(__FILE__), 'drop.serialized');
     $this->assertEquals($expected, $p, 'drop table statement');
 }
コード例 #20
0
ファイル: inlistTest.php プロジェクト: simha95/php-sql-parser
 public function testInlist()
 {
     $parser = new PHPSQLParser();
     $sql = "SELECT q.qid, question, gid FROM questions as q WHERE (select count(*) from answers as a where a.qid=q.qid and scale_id=0)=0 and sid=11929 AND type IN ('F', 'H', 'W', 'Z', '1') and q.parent_qid=0";
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'inlist1.serialized');
     $this->assertEquals($expected, $p, 'in list within WHERE clause');
 }
コード例 #21
0
 public function testIssue15()
 {
     $parser = new PHPSQLParser();
     $sql = "select usr_id, usr_login, case id_tipousuario when 1 then 'Usuario CVE' when 2 then concat('Usuario Vendedor -', codigovendedor, '-') when 3 then concat('Usuario Vendedor Meson (', codigovendedor, ')') end tipousuario, CONCAT( usr_nombres, ' ', usr_apellidos ) as nom_com, cod_local from usuarios where usr_estado <> 2 order by 3, 1, 4";
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'issue15.serialized');
     $this->assertEquals($expected, $p, 'parenthesis problem on issue 15');
 }
コード例 #22
0
 public function testUnion3()
 {
     $sql = "SELECT x FROM ((SELECT y FROM  z  WHERE (y > 2) ) UNION ALL (SELECT a FROM z WHERE (y < 2))) as f ";
     $parser = new PHPSQLParser();
     $p = $parser->parse($sql, true);
     $expected = getExpectedValue(dirname(__FILE__), 'union3.serialized');
     $this->assertEquals($expected, $p, 'complicated mysql union');
 }
コード例 #23
0
 public function testIssue94()
 {
     $sql = 'SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH) AS next_month';
     $parser = new PHPSQLParser();
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'issue94.serialized');
     $this->assertEquals($expected, $p, 'date_add()');
 }
コード例 #24
0
 public function testIssue91()
 {
     $sql = 'SELECT DISTINCT colA * colB From test t';
     $parser = new PHPSQLParser();
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'issue91.serialized');
     $this->assertEquals($expected, $p, 'distinct select');
 }
コード例 #25
0
 public function testIssue25()
 {
     $parser = new PHPSQLParser();
     $sql = "SELECT * FROM contacts WHERE contacts.id IN (SELECT email_addr_bean_rel.bean_id FROM email_addr_bean_rel, email_addresses WHERE email_addresses.id = email_addr_bean_rel.email_address_id AND email_addr_bean_rel.deleted = 0 AND email_addr_bean_rel.bean_module = 'Contacts' AND email_addresses.email_address IN (\"test@example.com\"))";
     $p = $parser->parse($sql);
     $expected = getExpectedValue(dirname(__FILE__), 'issue25.serialized');
     $this->assertEquals($expected, $p, 'parenthesis problem on issue 25');
 }
コード例 #26
0
 public function testIssue43()
 {
     $parser = new PHPSQLParser();
     $sql = "SELECT title, introtext\n        FROM kj9un_content\n        WHERE `id`='159'";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue43.serialized');
     $this->assertEquals($expected, $p, 'problem with linefeed after tablename');
 }
コード例 #27
0
 public function testIssue11()
 {
     $test = str_repeat('0', 18000);
     $query = "UPDATE club SET logo='{$test}' WHERE id=1";
     $parser = new PHPSQLParser();
     $p = $parser->parse($query);
     $expected = getExpectedValue(dirname(__FILE__), 'issue11.serialized');
     $this->assertEquals($expected, $p, 'very long statement');
 }
コード例 #28
0
 public function testIssue51()
 {
     $parser = new PHPSQLParser();
     $sql = "SELECT CAST( 12 AS decimal( 9, 3 ) )";
     $parser->parse($sql, true);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue51.serialized');
     $this->assertEquals($expected, $p, 'should not die if query contains cast expression');
 }
コード例 #29
0
ファイル: nestedTest.php プロジェクト: simha95/php-sql-parser
 public function testNested2()
 {
     $parser = new PHPSQLParser();
     $sql = "SELECT * FROM t1 LEFT JOIN (t2, t3, t4)\n                         ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)";
     $parser->parse($sql);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'nested2.serialized');
     $this->assertEquals($expected, $p, 'left joins with multiple tables');
 }
コード例 #30
0
 public function testIssue41()
 {
     $parser = new PHPSQLParser();
     $sql = "SELECT * FROM v\$mytable";
     $parser->parse($sql, true);
     $p = $parser->parsed;
     $expected = getExpectedValue(dirname(__FILE__), 'issue41.serialized');
     $this->assertEquals($expected, $p, 'escaped $ in tablename');
 }