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.
/**
* @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();
}
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');
}
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');
}
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');
}
/**
* @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);
}
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');
}
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');
}
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');
}
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');
}
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');
}
/**
* @return QueryInterface
*/
protected function getDecoratedQuery()
{
if ($this->query === null) {
$this->query = new Query($this->sql, $this->parser->parse($this->sql));
}
return $this->query;
}
/**
* 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]);
}
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");
}
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");
}
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');
}
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');
}
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');
}
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');
}
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');
}
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');
}
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');
}
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');
}
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()');
}
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');
}
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');
}
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');
}
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');
}
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');
}
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');
}
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');
}