Allow more than one index on the same column or column list. Since more indexes direct for less efficiency, it will be better to avoid creating more than one indexes on same column list.

I do not get an error when syntax checking a query with switched LIMIT and WHERE clauses:

SELECT pid, name2 FROM tablename LIMIT 10 WHERE pid = 20

The maximum length of a delimiter is 16 (15 characters + \0) for MySQL.

Data Definition Statements

• ALTER
• CREATE
  • DATABASE
  • EVENT
  • FUNCTION
  • INDEX
  • PROCEDURE
  • SERVER
  • TABLE
• DROP
• RENAME
• TRUNCATE
• LOCK (see #180)

Data Manipulation Statements

• CALL
• DELETE
• DO
• HANDLER
• INSERT
• LOAD (see #131)
• REPLACE
• SELECT
• UPDATE

Prepared Statements

• PREPARE
• EXECUTE

var_dump of parser for following query DROP VIEW abc

Fix is required for phpmyadmin/phpmyadmin#11966

test sql:

START TRANSACTION

ALTER TABLE `tbl` CHANGE `uid` `uid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT

ALTER TABLE `tbl` CHANGE `field_1` `field_2` INT( 10 ) UNSIGNED NOT NULL

COMMIT

After each line we missed semicolons, but sql-parser don't see any errors.
Test code same as in #9

While ON DUPLICATE KEY UPDATE works for regular INSERT statements, it does not for INSERT ... SELECT as the SELECT parser seems to consume the ON DUPLICATE part:

./bin/lint-query --query 'INSERT  INTO tbl SELECT  * FROM bar ON DUPLICATE KEY UPDATE baz = 1'
#1: Unrecognized keyword. (near "KEY" at position 49)
#2: A new statement was found, but no delimiter between it and the previous one. (near "UPDATE" at position 53)
#3: Unexpected token. (near "UPDATE" at position 53)
#4: Unexpected token. (near "=" at position 64)
#5: Unexpected token. (near "1" at position 66)

Good for your job,but , in some condition it may not work well as expected

for example, it cannot explain such SQL statement:

$query= 'select DISTINCT 1+2   c1, 1+ 2 as
`c2`, sum(c2),sum(c3) as sum_c3,"Status" = CASE
        WHEN quantity > 0 THEN \'in stock\'
        ELSE \'out of stock\'
        END case_statement
, t4.c1, (select c1+c2 from t1 inner_t1 limit 1) as subquery into @a1, @a2, @a3 from t1 the_t1 left outer join t2 using(c1,c2) join t3 as tX ON tX.c1 = the_t1.c1 join t4 t4_x using(x) where c1 = 1 and c2 in (1,2,3, "apple") and exists ( select 1 from some_other_table another_table where x > 1) and ("zebra" = "orange" or 1 = 1) group by 1, 2 having sum(c2) > 1 ORDER BY 2, c1 DESC LIMIT 0, 10 into outfile "/xyz" FOR UPDATE LOCK IN SHARE MODE';

Using this SQL from the MySQL manual:

CREATE TABLE opening_lines ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, opening_line TEXT(500), author VARCHAR(200), title VARCHAR(200), FULLTEXT idx (opening_line) ) ENGINE=InnoDB;

I get a linter warning of "Unrecognized data type. (near idx)"

Problem definition

When creating a routine using SQL commands in console, if we use label for loop
(eg: student_loop : LOOP), it identifies colon (:) as an "unexpected token" and shows error symbol at that line.

But when we execute (i.e: when pressing "Go" button), it works fine without pushing any errors.

Then when try to export the routine, again it shows as there is an error at the line which defines the label, but which is actually not an error.

Expected behaviour

When creating a label for a loop, colon following the label should not be detected as an error.

Actual behaviour

An error symbol occurs at the line which defines label for a loop; but which is actually not an error.
Also when executing the query, it works fine.

Server configuration

Web server: xampp server

Database: MySQL

phpMyAdmin version: 4.6.2

Client configuration

Browser: Google chrome

Operating system: Windows 8.1

Current version did not support "START TRANSACTION;" and "COMMIT;" commands.
Return error: "Unrecognized statement type."

$parser = new SqlParser\Parser('INSERT INTO `table_name` (`order`) VALUES (1);');
$parser->parse();

echo $parser->statements[0]->build();
// INSERT  INTO `table_name`(order) VALUES (1)

The original query becomes invalid and can not be executed in MySQL (missed ` around field name `order`).

Hi.

Have you considered adding documentation and use cases for this library? This looks like the most actively developed php sql parser.

Does it support reconstituting an sql statement from parse results, e.g. for anonymization before logging?

UPDATE `tableName` SET `colName` = NULL;

Gives me the following error:

Line 1, col 35 at "NULL": Unrecognized keyword.

This seems to be because of the following code in Components/SetOperation.php, which prevents NULL from being considered a valid assignment value:

// No keyword is expected.
if (($token->type === Token::TYPE_KEYWORD) && ($token->flags & Token::FLAG_KEYWORD_RESERVED)) {
    break;
}

The parser as of now is not able to process something like the following SQL statement
select * FROM (select Pop FROM MyTable) p join tadaa t where p.x=t.y order by p.pop

When using the SQL query:

CREATE TABLE MyGuests(
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
);

I get an warning "A closing bracket was expected. (Near ))"

Also, the characters ( and ) are technically parenthesis rather than brackets (which are [ and ]; braces are { and }), I wonder if we should improve the text of the notification.

There are just the ones that I noticed.

Unrecognized keywords for REGEXP, RLIKE
Unrecognized statement type for DISTINCT on UNION DISTINCT

SELECT 1
FROM information_schema.TABLES
WHERE 'a' REGEXP '^[a-d]'
UNION DISTINCT
SELECT 1
FROM information_schema.TABLES
WHERE 'a' RLIKE '^[a-d]';

SqlParser doesn't support INSERT ... SELECT Syntax.

$parser = new SqlParser\Parser('INSERT INTO `a` (`value`) SELECT `b`.`value` FROM `b`;');
$parser->parse();

echo $parser->statements[0]->build();
// Notice: Trying to get property of non-object in .../Components/ArrayObj.php on line 191
// Warning: implode(): Invalid arguments passed in .../Components/ArrayObj.php on line 191
// INSERT  INTO `a`(value) VALUES ()

Did not support creating copy of table by:
CREATE TABLE 'table_copy' LIKE 'table';

Output:
An opening bracket was expected. (near: LIKE) (after LIKE)
At least one column definition was expected. (near: ) (before LIKE)

The SET statement is currently parsed in a same way as SET clause in UPDATE/INSERT what is really wrong:

$ ./bin/lint-query --query "SET CHARACTER SET 'utf8';"
#1: Unrecognized keyword. (near "CHARACTER SET" at position 4)
#2: Unexpected token. (near "'utf8'" at position 18)

The highlighter doesn't properly escape content:

$ ./bin/highlight-query  --query "select '<s>xss' from \`<s>xss\` , <s>nxss /*s<s>xss*/" --format html
<span class="sql-reserved">SELECT</span><br/>  <span class="sql-string">'<s>xss'</span><br/><span class="sql-reserved">FROM</span><br/>  <span class="sql-variable">`<s>xss`</span>,<br/>  < s > nxss <span class="sql-comment">/*s<s>xss*/</span>

only < s > will not work (not treated as TAG) but conduct invalid XHTML.
the other <s>xss are XSS injections.

When syntax-checking a query with an opening, but no closing ", I do not get an error:

SELECT foo FROM tablename WHERE foo = "

Try parsing SELECT '\''. The extracted value is \, while it should be '. The bug is in Token::extract for Token::TYPE_STRING, it does just blindly replace '' with single one, what is not right solution in this case.

• var_dump of parser for the following query ALTER VIEW viewname AS SELECT * FROM tablename

Here AlterStatement is NULL

• var_dump of ALTER TABLE abc ADD COLUMN a INT

Here it won't detect the table name properly

Fix is required for the Tracker phpmyadmin/phpmyadmin#11966

Did not support set 'action' when creating a table prior to create a trigger:
"action varchar(50) default null"

Output:
A symbol name was expected! (near action)

• Define more strict rules.
• Use the error reporting system instead of @assert tags in Lexer.

Currently several SQL queries are not properly flagged as DROP DATABASE:

• DROP SCHEMA IF EXISTS DBNAME
• DROP /*! DATABASE */ DBNAME
• DROP DATABASEDBNAME``

From http://stackoverflow.com/questions/35568587/phpmyadmin-4-5-4-unexpected-token-error-in-replace-statement

This query doesn't work with phpMyAdmin 4.5.5.1:

SELECT  *
FROM mytable
WHERE REPLACE (Col, 'a', '')  = 'bbb'

Similar to #83, but for CLI output.

$ q=`echo -e "select 'text\e[33mcolor-inj' from tbl"`
$ ./bin/highlight-query --query "$q"
SELECT
  'textcolor-inj'
FROM
  tbl

Steps to reproduce

INSERT INTO `champs` (`id`,`key`) VALUES (412,'Thresh')
ON DUPLICATE KEY UPDATE id=412,key='Thresh'

Actual behaviour

#1: Unrecognized keyword. (near "ON" at position xx)
#2: Unrecognized keyword. (near "DUPLICATE" at position xx)
#3: Unrecognized keyword. (near "KEY" at position xx)

Server configuration

Web server:
Apache/2.4.17 (Win32) OpenSSL/1.0.2d PHP/5.6.21

Database:
Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $

phpMyAdmin version:
4.6.1

Same Issue?

Unrecognized keyword FULL and OUTER #12205

I am not sure if the SQL Command are 100% right, but i hope so.
Would be nice if someone could confirm this bug.

Hello,

I am trying to lint some SQL files using cweiske/php-sqllint which uses sql-parser under the hood.

I bumped into an issue with a SELECT inside a REPLACE INTO (third form). I tested it on the latest version of sql-parser
Here is an example (shamelessly taken from RandomStackOverflowQuestionExample)

./bin/lint-query --query "REPLACE INTO sales( 'item_id', 'date', 'qty', 'price' ) SELECT item_id, date, qty, price FROM sales_to_accept"

fails with #1: A new statement was found, but no delimiter between it and the previous one. (near "SELECT" at position 56)

Hope that helps!

e.g. SELECT @@hostname;

I think it could be a good idea to tag 1b2988f as 1.0.0 as this is the code bundled in phpMyAdmin 4.5.0, freshly released.

SqlParser doesn't support SET NAMES as SET CHARACTER SET Syntax.

$parser = new SqlParser\Parser('SET NAMES utf8;');
$parser->parse();

var_dump($parser->statements[0]->build());
// returns empty string

Did not support set comment to table:
ALTER TABLE my_table COMMENT='Comment of table';

Output:
Unrecognized alter operation. (near: ;)

Formatting fails for the following query:

SELECT
  coditm AS Item,
  descripcion AS Descripcion,
  contenedores AS Contenedores,
  IF(suspendido = 1, 'Si', 'No') AS Suspendido
FROM
  `DW_articulos`
WHERE
  superado = 0

I know about not supported ALTER, but current status return errors pointer on correct previous queries.

Sample:

SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";
SELECT * FROM `foo` WHERE `fld` = "фошафщцукщжшрцфгшкрцуг";


CREATE TABLE `tb_` (`rel_uid` BIGINT UNSIGNED NOT NULL ,
`custom_field_uid` INT UNSIGNED NOT NULL ,
`related_object_uid` BIGINT UNSIGNED NOT NULL ,
`value` VARCHAR( 255 ) NOT NULL ,
INDEX ( `related_object_uid` )
) ENGINE = InnoDB;

ALTER TABLE `tb_` ADD PRIMARY KEY ( `rel_uid` , `custom_field_uid` ) ;

ALTER TABLE `tb_` DROP INDEX `w_rel` ,
ADD INDEX `w_rel` ( `w_type_uid` , `related_object_uid` , `w_uid` );

EXP: error position = start of unrecognized operation (start of ALTER): line 24 col 1 and line 26 col 1.

ACT: Checking this code return errors about ALTER ("unrecognized alter operation"), but line and column position in SELECT and CREATE queries: line 14 col 80 and line 18 col 37.

Looks like pointer coordinates can be shifted by the code without any restrictions.

Until 3.3.1, tag were documented (using the github feature).

No information about more recent versions.

See: http://keepachangelog.com/

It would be helpful to add a "ext-mbstring": "*" to the require field in the composer file since the mb_strlen function is used

test sql:

SELECT * FROM foo WHERE
SELECT * FROM foo WHERE

START TRANSACTION;

CREATE TABLE `tb` (`uid` INT UNSIGNED NOT NULL 
`position` INT NOT NULL,
PRIMARY KEY ( `uid` ) ,
INDEX ( `position` ) 
) ENGINE = InnoDB;

COMMIT;

my code:

    $lexer = new SqlParser\Lexer($query);
    $parser = new SqlParser\Parser($lexer->list);
    $errors = SqlParser\Utils\Error::get(array($lexer, $parser));

Problems:

1. Warning: Creating default object from empty value in Parser.php on line 439
2. there is skipped comma before position, but we don't have any errors about it.
3. skipped semicolon before "START TRANSACTION". Is it error case?

Did not support changing the delimiter:
"delimiter $$"

Output:
Unexpected character. (near $)

The parser doesn't parse properly the following query:

 CREATE TABLE `jos_core_acl_aro` (
  `id` int(11) NOT NULL,
  `section_value` varchar(240) NOT NULL DEFAULT '0',
  `value` varchar(240) NOT NULL DEFAULT '',
  `order_value` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `hidden` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `jos_section_value_value_aro` (`section_value`(100),`value`(15)) USING BTREE,
  KEY `jos_gacl_hidden_aro` (`hidden`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Please revert a023393 and use

    "phpunit/phpunit": "~4.8 || ~5.1" 

With PHP 5.6

$ phpunit --verbose
PHPUnit 5.1.3 by Sebastian Bergmann and contributors.

Runtime:       PHP 5.6.16
Configuration: /dev/shm/extras/BUILD/sql-parser-47d03c5dc614939b2df057b984544635ae6217db/phpunit.xml

...............................................................  63 / 288 ( 21%)
............................................................... 126 / 288 ( 43%)
............................................................... 189 / 288 ( 65%)
............................................................... 252 / 288 ( 87%)
....................................                            288 / 288 (100%)

Time: 1.67 seconds, Memory: 23.50Mb

OK (288 tests, 640 assertions)

And with PHP 7.0

$ phpunit --verbose
PHPUnit 5.1.3 by Sebastian Bergmann and contributors.

Runtime:       PHP 7.0.2RC1
Configuration: /dev/shm/extras/BUILD/sql-parser-47d03c5dc614939b2df057b984544635ae6217db/phpunit.xml

...............................................................  63 / 288 ( 21%)
............................................................... 126 / 288 ( 43%)
............................................................... 189 / 288 ( 65%)
............................................................... 252 / 288 ( 87%)
....................................                            288 / 288 (100%)

Time: 374 ms, Memory: 6.00Mb

OK (288 tests, 640 assertions)

This also imply to drop composer.lock, but this file have no sense for a library.

Data Definition Statements

• ALTER
• CREATE
• DROP
• RENAME
• TRUNCATE

Data Manipulation Statements

• CALL
• DELETE
• DO
• HANDLER
• INSERT
• LOAD
• REPLACE
• SELECT
• UPDATE

Prepared Statements

• PREPARE
• EXECUTE

• Lexer
• Parser
  • ALTER Statement
  • CREATE Statement
  • DROP Statement
  • RENAME Statement
  • TRUNCATE Statement
  • CALL Statement
  • DELETE Statement
  • DO Statement
  • HANDLER Statement
  • INSERT Statement
  • LOAD Statement
  • REPLACE Statement
  • SELECT Statement
  • UPDATE Statement
  • PREPARE Statement
  • EXECUTE Statement

Because the DELIMITER keyword affects the way statements are delimited, I believe that it should be evaluated in the lexical analyser.

At this moment, the delimiter does not influence the lexer or the parser in any way.

• [ ] Drizzle 7.1
• [ ] Drizzle 7.2
• MariaDB 10.0
• MariaDB 10.1
• MariaDB 10.2
• MySQL 5.0
• MySQL 5.1
• MySQL 5.5
• MySQL 5.6
• MySQL 5.7
• MySQL 8.0

Following SQL is not correctly parsed:

CREATE TABLE `customers` (
  `login_cnt` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Počet přihlášení',
  `activated` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Účet je aktivovaný (0 pouze u fiktivní registrace na základě prodejek)',
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

It seems that combiding DEFAULT with COMMENT is what causes the problem here.

See phpmyadmin/phpmyadmin#11400.

At least the POINT type is not recognized:

$ ./bin/lint-query --query 'CREATE TABLE `xss`.`gis` ( `x` POINT NOT NULL ) ENGINE = InnoDB;'
#1: Unrecognized data type. (near "POINT" at position 31)