This is very trivial, and I have no idea, what might have been broken by these changes, but phpunit does not complain about anything, so you definitely haven't written enough tests :laughing: /s

It allows you to retrieve current identifiers quoting char from Context::getIdentifierQuote() based on current SQL Mode set and seems to work well with CLI tools such as lint and highlight-query

There are still few places, where the backtick is hard-coded, and I'm not sure if I should update these pieces of code with new context-aware solution, but that I probably leave to someone who knows this code-base better.

I've tested this with sample SQL query from: https://github.com/phpmyadmin/phpmyadmin/issues/15821#issuecomment-578572914

Fixes: #284

Do you plan to add compatibility with PHP 8.1?

Currently I have next messages: PHP Deprecated: Return type of PhpMyAdmin\SqlParser\TokensList::offsetExists($offset) should either be compatible with ArrayAccess::offsetExists(mixed $offset): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in phar://sql-parser.phar/src/TokensList.php on line 184

Deprecated: Return type of PhpMyAdmin\SqlParser\TokensList::offsetGet($offset) should either be compatible with ArrayAccess::offsetGet(mixed $offset): mixed, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in phar://sql-parser.phar/src/TokensList.php on line 172

PHP Deprecated: Return type of PhpMyAdmin\SqlParser\TokensList::offsetSet($offset, $value) should either be compatible with ArrayAccess::offsetSet(mixed $offset, mixed $value): void, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in phar://sql-parser.phar/src/TokensList.php on line 156

PHP Deprecated: Return type of PhpMyAdmin\SqlParser\TokensList::offsetUnset($offset) should either be compatible with ArrayAccess::offsetUnset(mixed $offset): void, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in phar://sql-parser.phar/src/TokensList.php on line 194

Currently the SQL syntax highlighter marks the WITH keyword as Unrecognized statement type. MariaDB supports a WITH clause in combination with a SELECT statement since version 10.2.1 (stable=10.2.8), see https://mariadb.com/kb/en/the-mariadb-library/with/

phpMyAdmin version: 4.7.4

Sort of relates to phpmyadmin/phpmyadmin#13615 (but not really)

From http://www.php-fig.org/psr/psr-4/

The fully qualified class name MUST have a top-level namespace name, also known as a “vendor namespace”.

Ok, you can say "SqlParser" is the vendor... a way to circumvent PSR-4 (as the project SubNamespaceName is not mandatory.. one reason why I dislike this PSR). And SqlParser could be a bit too generic and may create conflict.

Proposal: use PhpMyAdmin\SqlParser

I understand this is only doable as part of a new major version, because of BC break.

Writing tests this way can be harden, but you can be sure that your feature/fix for an formatter type will not impact on any other formatter type.

WDYT? If you think that can be useful I'll continue to write tests for the remaining tests cases.

Hi, This PR should fix https://github.com/phpmyadmin/sql-parser/issues/168 and the issues shown https://github.com/phpmyadmin/sql-parser/pull/337

I will clarify the changes in a review.

Testing in PMA too.

 CREATE DEFINER=`root`@`%` PROCEDURE `test2`(IN `_var` INT) NOT DETERMINISTIC NO SQL SQL SECURITY INVOKER NO SQL SQL SECURITY INVOKER SELECT _var

The detected is

NO SQL
    SQL SECURITY INVOKER
SELECT _var

It should be

SELECT _var

Ref: https://github.com/phpmyadmin/phpmyadmin/issues/15991

PHP Warning:  array_key_exists(): The first argument should be either a string or an integer in /var/www/html/vendor/phpmyadmin/sql-parser/src/Components/AlterOperation.php on line 286

A dump inside AlterOperation.php gives me the following result:

I don't have any idea if the value attribute should be a boolean in this case, so I'm not starting a PR right now. I don't know if the correct solution would be to cast to string on the array_key_exists itself, or to save the value attribute as a string instead of a boolean...

The WITH keyword is now supported for CTEs starting with MariaDB 10.2.1 and MySQL 8.0.

See https://github.com/phpmyadmin/phpmyadmin/issues/13616

./lint-query --query "WITH t as (select a from t1 where b >= 'c') 
>   select * from t2,t where t2.c=t.a;"
#1: Unrecognized statement type. (near "WITH" at position 0)

The parser may not well deal with the comments. Here is an example, as it treat -- as a part of table alias.

Give sql as

SELECT  * FROM ecshop.ecs_order_info 
-- LIMIT 0, 30 
--

process by PhpMyAdmin\SqlParser\Parser

object(PhpMyAdmin\SqlParser\Statements\SelectStatement)#28 (16) {
  ["expr"]=>
  array(1) {
    [0]=>
    object(PhpMyAdmin\SqlParser\Components\Expression)#13 (7) {
      ["database"]=>
      NULL
      ["table"]=>
      NULL
      ["column"]=>
      NULL
      ["expr"]=>
      string(1) "*"
      ["alias"]=>
      NULL
      ["function"]=>
      NULL
      ["subquery"]=>
      NULL
    }
  }
  ["from"]=>
  array(1) {
    [0]=>
    object(PhpMyAdmin\SqlParser\Components\Expression)#11 (7) {
      ["database"]=>
      string(6) "ecshop"
      ["table"]=>
      string(14) "ecs_order_info"
      ["column"]=>
      NULL
      ["expr"]=>
      string(23) "ecshop.ecs_order_info--"
      ["alias"]=>
      NULL
      ["function"]=>
      NULL
      ["subquery"]=>
      NULL
    }
  }
  ["partition"]=>
  NULL
  ["where"]=>
  NULL
  ["group"]=>
  NULL
  ["having"]=>
  NULL
  ["order"]=>
  NULL
  ["limit"]=>
  object(PhpMyAdmin\SqlParser\Components\Limit)#27 (2) {
    ["offset"]=>
    int(0)
    ["rowCount"]=>
    int(30)
  }
  ["procedure"]=>
  NULL
  ["into"]=>
  NULL
  ["join"]=>
  NULL
  ["union"]=>
  array(0) {
  }
  ["end_options"]=>
  NULL
  ["options"]=>
  object(PhpMyAdmin\SqlParser\Components\OptionsArray)#30 (1) {
    ["options"]=>
    array(0) {
    }
  }
  ["first"]=>
  int(0)
  ["last"]=>
  int(12)
}

and rebuild after ensuring the limit expression is appended (by check the statement->limit property, and add it if lack), would get

SELECT  * FROM ecshop.ecs_order_info-- LIMIT 0, 30 

As per phpmyadmin/phpmyadmin#14956, if we create a Table that contains one column that is "TEXT COMPRESSED", in MariaDB, and then we do an Export, the CREATE TABLE statement will be corrupt, starting from the compressed column:

And the "ALTER TABLE ADD KEY" statement that follows also didn't appear.

Hello all.

I'm working IRL with @SanaRebhi and we tried to make a proposal in order to solve this topic.

I made some manual tests and it looks like it's OK, but I want to go deeper and add unit tests also to cover (almost) all test cases about ALTER EVENT statements, which I don't have time right now.

Current unit-test suite is passing (with 2 ignored test-cases out of this range) locally.

Please feel free to tell me if this approch is a good one and I can continue to write tests or if you have another way in mind to manage it.

Thanks a lot.

Ref: #404

Describe the bug

When you export a database with the 'Add CREATE PROCEDURE / FUNCTION / EVENT statement' option enabled, and the procedure definition includes 'MODIFIES SQL DATA', the parser generates the following message:

Warning in ./vendor/phpmyadmin/sql-parser/src/Components/OptionsArray.php#306
Undefined array key "value"

Dumping the value of $option just before that shows the following as the cause of the message:

Array
(   
    [name] => MODIFIES
    [equals] =>
    [expr] =>
)

To Reproduce

Create a procedure with 'MODIFIES SQL DATA' as the SQL data access option. Click the Export tab for the database with said procedure. Perform a custom export with the 'Add CREATE PROCEDURE / FUNCTION / EVENT statement' See the warning and backtrace.

Expected behavior

No warning messages during the export.

Screenshots

Here's the full backtrace:

Warning in ./vendor/phpmyadmin/sql-parser/src/Components/OptionsArray.php#306
Undefined array key "value"

Backtrace

CreateStatement.php#491: PhpMyAdmin\SqlParser\Components\OptionsArray::build()
ExportSql.php#2814: PhpMyAdmin\SqlParser\Statements\CreateStatement->build()
ExportSql.php#558: PhpMyAdmin\Plugins\Export\ExportSql->replaceWithAliases(
string 'CREATE DEFINER=`root`@`localhost` PROCEDURE `split_string`(IN `x` VARCHAR(255), IN `delim` VARCHAR(12), IN `id` CHAR(36)) MODIFIES SQL DATA DETERMINISTIC BEGIN SET @valcount = substrCount(x,delim)+1; SET @vl = 0; CREATE TEMPORARY TABLE IF NOT EXISTS splitResultsTemp (uuid char(36), split_value varchar(255)) ENGINE=MEMORY; WHILE (@vl < @valcount) DO SET @val = stringSplit(x,delim,@vl+1); INSERT INTO splitResultsTemp (uuid, split_value) VALUES (id, @val); SET @vl = @vl + 1; END WHILE; END',
array,
string 'ftp_accounts',
string '',
boolean false,
)
ExportSql.php#620: PhpMyAdmin\Plugins\Export\ExportSql->exportRoutineSQL(
string 'ftp_accounts',
array,
string 'PROCEDURE',
string 'Procedures',
array,
string '$$',
)
Export.php#742: PhpMyAdmin\Plugins\Export\ExportSql->exportRoutines(
string 'ftp_accounts',
array,
)
ExportController.php#548: PhpMyAdmin\Export->exportDatabase(
string 'ftp_accounts',
array,
string 'structure_and_data',
array,
array,
,
string ' ',
string 'index.php?route=/database/export&db=ftp_accounts',
string 'database',
boolean true,
boolean true,
boolean true,
boolean true,
array,
string '',
)
Routing.php#192: PhpMyAdmin\Controllers\Export\ExportController->__invoke(
,
array,
)
index.php#43: PhpMyAdmin\Routing::callControllerForRoute(
,
string '/export',
,
,
)

Server configuration

• Operating system: RHEL 9
• Web server: Apache httpd 2.4.53-7.el9
• Database version: mariadb-server 10.5.16-2.el9_0
• PHP version: 8.1.8-1module+el91.0+15877+c3862210
• phpMyAdmin version: 5.2.0-1.el9.remi

Client configuration

• Browser: Firefox 108.0.1
• Operating system: Windows 10 Enterprise 20H2

Additional context

This happens with multiple databases, each of witch have a stored procedure that modifies SQL data.

Hello,

The following SQL statement is not parsed entirely by SQL Parser

ALTER TABLE `transactions` RENAME INDEX `fk_transactions_catalog_entries1_idx` TO `fk_transactions_catalog_entries2_idx`

It fails with the following message:

Missing comma before start of a new alter operation. (near TO)

The same happens in PhpMyAdmin v5.2.0 as well:

Do you know what could be happening?

Thanks a lot!

It seems that the latest release of sql-parser (5.6.0) does not support "ALTER EVENTS" yet. The AlterOperation component needs to be updated in order to add all the options for the EVENT. Any plans to handle this kind of statements please?

I was looking for a SQL parser written in Php a few weeks ago and I searched on google: "SQL tokenizer in PHP" etc, but there was no clue of this gem out there and I was totally disappointed.

I eventually stumbled upon this by pure chance while I was looking in PHPMyAdmin composer.json file by accident.

You may ask and answer some questions about its functionality in stack overflow and tag it better on Github or packagist.org.

Good luck and thanks.

This package has a so called Viral license (GPL-2.0-or-later). Because of the 'viral' aspect of this, AFAIK this means that we are actually unable to use it in a closed source project. Would it be possible to change this license to a non-viral one?

Related to https://github.com/nunomaduro/larastan/issues/1373