Bad procedure body

[williamdes]

 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: phpmyadmin/phpmyadmin#15991

[williamdes]

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
https://mariadb.com/kb/en/create-function/
https://mariadb.com/kb/en/create-procedure/

This seems like non sense:

sql-parser/src/Statements/CreateStatement.php

Line 50 in 6186c3f

'SQL SECURITY' => [

and real full definitions:

sql-parser/src/Statements/CreateStatement.php

Line 232 in 6186c3f

'SQL SECURITY DEFINER' => [

If you can @iifawzi , could you also confirm this looks not right to have it in $OPTIONS because it will not be found in the same place as DEFINER

[williamdes]

Never mind, this is for CREATE VIEW

[iifawzi]

If you can @iifawzi , could you also confirm this looks not right to have it in $OPTIONS because it will not be found in the same place as DEFINER

I think this's incorrect yes, because the DEFINER keyword, will never be in the same token.

For a query like:

CREATE FUNCTION `func\\`(`pa``ram` VARCHAR(45) CHARSET latin1) RETURNS DECIMAL(5,5) UNSIGNED ZEROFILL COMMENT DETERMINISTIC SQL SECURITY DEFINER SELECT;

The tokens are:

[
.
.
.
.
.
.
{
  "token": "SQL SECURITY",
  "value": "SQL SECURITY",
  "keyword": "SQL SECURITY",
  "type": 1,
  "flags": 7,
  "position": 116
}, {
  "token": " ",
  "value": " ",
  "keyword": null,
  "type": 3,
  "flags": 0,
  "position": 128
}, {
  "token": "DEFINER",
  "value": "DEFINER",
  "keyword": "DEFINER",
  "type": 1,
  "flags": 1,
  "position": 129
},
.
.
.
.
.]

[williamdes]

🤔 I am not sure to understand why FUNC_OPTIONS exists and seems to be not working at all

What makes SQL SECURITY glued and some other not glued together ? (maybe context files ?)

token list from tests

RPhpMyAdmin\SqlParser\TokensList Object
(
    [tokens] => Array
        (
            [0] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => CREATE
                    [value] => CREATE
                    [keyword] => CREATE
                    [type] => 1
                    [flags] => 3
                    [position] => 0
                )

            [1] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 6
                )

            [2] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => DEFINER
                    [value] => DEFINER
                    [keyword] => DEFINER
                    [type] => 1
                    [flags] => 1
                    [position] => 7
                )

            [3] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => =
                    [value] => =
                    [keyword] => 
                    [type] => 2
                    [flags] => 2
                    [position] => 14
                )

            [4] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `root`@`%`
                    [value] => root@%
                    [keyword] => 
                    [type] => 8
                    [flags] => 4
                    [position] => 15
                )

            [5] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 25
                )

            [6] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => PROCEDURE
                    [value] => PROCEDURE
                    [keyword] => PROCEDURE
                    [type] => 1
                    [flags] => 3
                    [position] => 26
                )

            [7] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 35
                )

            [8] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `test2`
                    [value] => test2
                    [keyword] => 
                    [type] => 8
                    [flags] => 2
                    [position] => 36
                )

            [9] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => (
                    [value] => (
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 43
                )

            [10] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => IN
                    [value] => IN
                    [keyword] => IN
                    [type] => 1
                    [flags] => 35
                    [position] => 44
                )

            [11] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 46
                )

            [12] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `_var`
                    [value] => _var
                    [keyword] => 
                    [type] => 8
                    [flags] => 2
                    [position] => 47
                )

            [13] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 53
                )

            [14] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => INT
                    [value] => INT
                    [keyword] => INT
                    [type] => 1
                    [flags] => 11
                    [position] => 54
                )

            [15] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => )
                    [value] => )
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 57
                )

            [16] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 58
                )

            [17] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => NOT
                    [value] => NOT
                    [keyword] => NOT
                    [type] => 1
                    [flags] => 3
                    [position] => 59
                )

            [18] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 62
                )

            [19] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => DETERMINISTIC
                    [value] => DETERMINISTIC
                    [keyword] => DETERMINISTIC
                    [type] => 1
                    [flags] => 3
                    [position] => 63
                )

            [20] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 76
                )

            [21] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => NO
                    [value] => NO
                    [keyword] => NO
                    [type] => 1
                    [flags] => 1
                    [position] => 77
                )

            [22] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 79
                )

            [23] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL
                    [value] => SQL
                    [keyword] => SQL
                    [type] => 1
                    [flags] => 3
                    [position] => 80
                )

            [24] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 83
                )

            [25] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL SECURITY
                    [value] => SQL SECURITY
                    [keyword] => SQL SECURITY
                    [type] => 1
                    [flags] => 7
                    [position] => 84
                )

            [26] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 96
                )

            [27] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => INVOKER
                    [value] => INVOKER
                    [keyword] => INVOKER
                    [type] => 1
                    [flags] => 1
                    [position] => 97
                )

            [28] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 104
                )

            [29] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => NO
                    [value] => NO
                    [keyword] => NO
                    [type] => 1
                    [flags] => 1
                    [position] => 105
                )

            [30] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 107
                )

            [31] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL
                    [value] => SQL
                    [keyword] => SQL
                    [type] => 1
                    [flags] => 3
                    [position] => 108
                )

            [32] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 111
                )

            [33] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL SECURITY
                    [value] => SQL SECURITY
                    [keyword] => SQL SECURITY
                    [type] => 1
                    [flags] => 7
                    [position] => 112
                )

            [34] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 124
                )

            [35] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => INVOKER
                    [value] => INVOKER
                    [keyword] => INVOKER
                    [type] => 1
                    [flags] => 1
                    [position] => 125
                )

            [36] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 132
                )

            [37] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SELECT
                    [value] => SELECT
                    [keyword] => SELECT
                    [type] => 1
                    [flags] => 3
                    [position] => 133
                )

            [38] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 139
                )

            [39] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => _var
                    [value] => _var
                    [keyword] => 
                    [type] => 0
                    [flags] => 0
                    [position] => 140
                )

            [40] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 
                    [value] => 
                    [keyword] => 
                    [type] => 9
                    [flags] => 0
                    [position] => 
                )

        )

    [count] => 41
    [idx] => 0
)
R                                                                  2 / 2 (100%)PhpMyAdmin\SqlParser\TokensList Object
(
    [tokens] => Array
        (
            [0] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => CREATE
                    [value] => CREATE
                    [keyword] => CREATE
                    [type] => 1
                    [flags] => 3
                    [position] => 0
                )

            [1] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 6
                )

            [2] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => DEFINER
                    [value] => DEFINER
                    [keyword] => DEFINER
                    [type] => 1
                    [flags] => 1
                    [position] => 7
                )

            [3] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => =
                    [value] => =
                    [keyword] => 
                    [type] => 2
                    [flags] => 2
                    [position] => 14
                )

            [4] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `root`@`localhost`
                    [value] => root@localhost
                    [keyword] => 
                    [type] => 8
                    [flags] => 4
                    [position] => 15
                )

            [5] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 33
                )

            [6] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => FUNCTION
                    [value] => FUNCTION
                    [keyword] => FUNCTION
                    [type] => 1
                    [flags] => 1
                    [position] => 34
                )

            [7] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 42
                )

            [8] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `inventory_in_stock`
                    [value] => inventory_in_stock
                    [keyword] => 
                    [type] => 8
                    [flags] => 2
                    [position] => 43
                )

            [9] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => (
                    [value] => (
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 63
                )

            [10] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `p_inventory_id`
                    [value] => p_inventory_id
                    [keyword] => 
                    [type] => 8
                    [flags] => 2
                    [position] => 64
                )

            [11] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 80
                )

            [12] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => INT
                    [value] => INT
                    [keyword] => INT
                    [type] => 1
                    [flags] => 11
                    [position] => 81
                )

            [13] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => )
                    [value] => )
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 84
                )

            [14] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 85
                )

            [15] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => RETURNS
                    [value] => RETURNS
                    [keyword] => RETURNS
                    [type] => 1
                    [flags] => 1
                    [position] => 86
                )

            [16] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 93
                )

            [17] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => tinyint
                    [value] => TINYINT
                    [keyword] => TINYINT
                    [type] => 1
                    [flags] => 11
                    [position] => 94
                )

            [18] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => (
                    [value] => (
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 101
                )

            [19] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 1
                    [value] => 1
                    [keyword] => 
                    [type] => 6
                    [flags] => 0
                    [position] => 102
                )

            [20] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => )
                    [value] => )
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 103
                )

            [21] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 104
                )

            [22] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => READS
                    [value] => READS
                    [keyword] => READS
                    [type] => 1
                    [flags] => 3
                    [position] => 105
                )

            [23] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 110
                )

            [24] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL
                    [value] => SQL
                    [keyword] => SQL
                    [type] => 1
                    [flags] => 3
                    [position] => 111
                )

            [25] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 114
                )

            [26] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => DATA
                    [value] => DATA
                    [keyword] => DATA
                    [type] => 1
                    [flags] => 1
                    [position] => 115
                )

            [27] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 
 
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 119
                )

            [28] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => COMMENT
                    [value] => COMMENT
                    [keyword] => COMMENT
                    [type] => 1
                    [flags] => 1
                    [position] => 121
                )

            [29] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 128
                )

            [30] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 'My best function written by a friend''s friend
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    
    

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END
                    [value] => My best function written by a friend's friend
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    
    

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
EN
                    [keyword] => 
                    [type] => 7
                    [flags] => 1
                    [position] => 129
                )

            [31] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 
                    [value] => 
                    [keyword] => 
                    [type] => 9
                    [flags] => 0
                    [position] => 
                )

        )

    [count] => 32
    [idx] => 0
)


Time: 88 ms, Memory: 10.00 MB

There were 2 risky tests:

1) PhpMyAdmin\SqlParser\Tests\Builder\CreateStatementTest::testBuilderCreateProcedure
This test printed output: PhpMyAdmin\SqlParser\TokensList Object
(
    [tokens] => Array
        (
            [0] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => CREATE
                    [value] => CREATE
                    [keyword] => CREATE
                    [type] => 1
                    [flags] => 3
                    [position] => 0
                )

            [1] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 6
                )

            [2] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => DEFINER
                    [value] => DEFINER
                    [keyword] => DEFINER
                    [type] => 1
                    [flags] => 1
                    [position] => 7
                )

            [3] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => =
                    [value] => =
                    [keyword] => 
                    [type] => 2
                    [flags] => 2
                    [position] => 14
                )

            [4] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `root`@`%`
                    [value] => root@%
                    [keyword] => 
                    [type] => 8
                    [flags] => 4
                    [position] => 15
                )

            [5] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 25
                )

            [6] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => PROCEDURE
                    [value] => PROCEDURE
                    [keyword] => PROCEDURE
                    [type] => 1
                    [flags] => 3
                    [position] => 26
                )

            [7] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 35
                )

            [8] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `test2`
                    [value] => test2
                    [keyword] => 
                    [type] => 8
                    [flags] => 2
                    [position] => 36
                )

            [9] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => (
                    [value] => (
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 43
                )

            [10] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => IN
                    [value] => IN
                    [keyword] => IN
                    [type] => 1
                    [flags] => 35
                    [position] => 44
                )

            [11] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 46
                )

            [12] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `_var`
                    [value] => _var
                    [keyword] => 
                    [type] => 8
                    [flags] => 2
                    [position] => 47
                )

            [13] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 53
                )

            [14] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => INT
                    [value] => INT
                    [keyword] => INT
                    [type] => 1
                    [flags] => 11
                    [position] => 54
                )

            [15] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => )
                    [value] => )
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 57
                )

            [16] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 58
                )

            [17] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => NOT
                    [value] => NOT
                    [keyword] => NOT
                    [type] => 1
                    [flags] => 3
                    [position] => 59
                )

            [18] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 62
                )

            [19] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => DETERMINISTIC
                    [value] => DETERMINISTIC
                    [keyword] => DETERMINISTIC
                    [type] => 1
                    [flags] => 3
                    [position] => 63
                )

            [20] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 76
                )

            [21] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => NO
                    [value] => NO
                    [keyword] => NO
                    [type] => 1
                    [flags] => 1
                    [position] => 77
                )

            [22] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 79
                )

            [23] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL
                    [value] => SQL
                    [keyword] => SQL
                    [type] => 1
                    [flags] => 3
                    [position] => 80
                )

            [24] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 83
                )

            [25] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL SECURITY
                    [value] => SQL SECURITY
                    [keyword] => SQL SECURITY
                    [type] => 1
                    [flags] => 7
                    [position] => 84
                )

            [26] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 96
                )

            [27] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => INVOKER
                    [value] => INVOKER
                    [keyword] => INVOKER
                    [type] => 1
                    [flags] => 1
                    [position] => 97
                )

            [28] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 104
                )

            [29] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => NO
                    [value] => NO
                    [keyword] => NO
                    [type] => 1
                    [flags] => 1
                    [position] => 105
                )

            [30] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 107
                )

            [31] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL
                    [value] => SQL
                    [keyword] => SQL
                    [type] => 1
                    [flags] => 3
                    [position] => 108
                )

            [32] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 111
                )

            [33] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL SECURITY
                    [value] => SQL SECURITY
                    [keyword] => SQL SECURITY
                    [type] => 1
                    [flags] => 7
                    [position] => 112
                )

            [34] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 124
                )

            [35] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => INVOKER
                    [value] => INVOKER
                    [keyword] => INVOKER
                    [type] => 1
                    [flags] => 1
                    [position] => 125
                )

            [36] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 132
                )

            [37] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SELECT
                    [value] => SELECT
                    [keyword] => SELECT
                    [type] => 1
                    [flags] => 3
                    [position] => 133
                )

            [38] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 139
                )

            [39] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => _var
                    [value] => _var
                    [keyword] => 
                    [type] => 0
                    [flags] => 0
                    [position] => 140
                )

            [40] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 
                    [value] => 
                    [keyword] => 
                    [type] => 9
                    [flags] => 0
                    [position] => 
                )

        )

    [count] => 41
    [idx] => 0
)

2) PhpMyAdmin\SqlParser\Tests\Builder\CreateStatementTest::testBuilderCreateFunction
This test printed output: PhpMyAdmin\SqlParser\TokensList Object
(
    [tokens] => Array
        (
            [0] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => CREATE
                    [value] => CREATE
                    [keyword] => CREATE
                    [type] => 1
                    [flags] => 3
                    [position] => 0
                )

            [1] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 6
                )

            [2] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => DEFINER
                    [value] => DEFINER
                    [keyword] => DEFINER
                    [type] => 1
                    [flags] => 1
                    [position] => 7
                )

            [3] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => =
                    [value] => =
                    [keyword] => 
                    [type] => 2
                    [flags] => 2
                    [position] => 14
                )

            [4] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `root`@`localhost`
                    [value] => root@localhost
                    [keyword] => 
                    [type] => 8
                    [flags] => 4
                    [position] => 15
                )

            [5] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 33
                )

            [6] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => FUNCTION
                    [value] => FUNCTION
                    [keyword] => FUNCTION
                    [type] => 1
                    [flags] => 1
                    [position] => 34
                )

            [7] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 42
                )

            [8] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `inventory_in_stock`
                    [value] => inventory_in_stock
                    [keyword] => 
                    [type] => 8
                    [flags] => 2
                    [position] => 43
                )

            [9] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => (
                    [value] => (
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 63
                )

            [10] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => `p_inventory_id`
                    [value] => p_inventory_id
                    [keyword] => 
                    [type] => 8
                    [flags] => 2
                    [position] => 64
                )

            [11] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 80
                )

            [12] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => INT
                    [value] => INT
                    [keyword] => INT
                    [type] => 1
                    [flags] => 11
                    [position] => 81
                )

            [13] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => )
                    [value] => )
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 84
                )

            [14] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 85
                )

            [15] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => RETURNS
                    [value] => RETURNS
                    [keyword] => RETURNS
                    [type] => 1
                    [flags] => 1
                    [position] => 86
                )

            [16] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 93
                )

            [17] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => tinyint
                    [value] => TINYINT
                    [keyword] => TINYINT
                    [type] => 1
                    [flags] => 11
                    [position] => 94
                )

            [18] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => (
                    [value] => (
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 101
                )

            [19] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 1
                    [value] => 1
                    [keyword] => 
                    [type] => 6
                    [flags] => 0
                    [position] => 102
                )

            [20] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => )
                    [value] => )
                    [keyword] => 
                    [type] => 2
                    [flags] => 16
                    [position] => 103
                )

            [21] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 104
                )

            [22] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => READS
                    [value] => READS
                    [keyword] => READS
                    [type] => 1
                    [flags] => 3
                    [position] => 105
                )

            [23] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 110
                )

            [24] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => SQL
                    [value] => SQL
                    [keyword] => SQL
                    [type] => 1
                    [flags] => 3
                    [position] => 111
                )

            [25] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 114
                )

            [26] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => DATA
                    [value] => DATA
                    [keyword] => DATA
                    [type] => 1
                    [flags] => 1
                    [position] => 115
                )

            [27] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 
 
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 119
                )

            [28] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => COMMENT
                    [value] => COMMENT
                    [keyword] => COMMENT
                    [type] => 1
                    [flags] => 1
                    [position] => 121
                )

            [29] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] =>  
                    [value] =>  
                    [keyword] => 
                    [type] => 3
                    [flags] => 0
                    [position] => 128
                )

            [30] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 'My best function written by a friend''s friend
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    
    

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END
                    [value] => My best function written by a friend's friend
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    
    

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
EN
                    [keyword] => 
                    [type] => 7
                    [flags] => 1
                    [position] => 129
                )

            [31] => PhpMyAdmin\SqlParser\Token Object
                (
                    [token] => 
                    [value] => 
                    [keyword] => 
                    [type] => 9
                    [flags] => 0
                    [position] => 
                )

        )

    [count] => 32
    [idx] => 0
)

[iifawzi]

What makes SQL SECURITY glued and some other not glued together ? (maybe context files ?)

Looks like yes.. and I think the numbers in the context files is the numbers that we have to follow when using them in the options, it will make sure that for any tokens that can be in the same query, they will have different number

If this conclusion is right, I've to follow these numbers in the other PR too.

🤔 I am not sure to understand why FUNC_OPTIONS exists and seems to be not working at all

I will investigate more about this

[williamdes]

sql-parser/tests/Components/OptionsArrayTest.php

    public function testParseProcedure()
    {
        $component = OptionsArray::parse(
            new Parser(),
            $this->getTokensList(
                'CREATE DEFINER=`root`@`%`'
                . ' PROCEDURE `test2` (IN `_var` INT)  NOT DETERMINISTIC NO SQL'
                . ' SQL SECURITY INVOKER NO SQL SQL SECURITY INVOKER SELECT _var'
            ),
            CreateStatement::$FUNC_OPTIONS
        );
        $this->assertEquals(
            array(),
            $component->options
        );
    }

From my debug is seems to step on some token for some reason

Merging your PR and switching back to phpMyAdmin main to merge PRs

[iifawzi]

This bug seems to be in PMA not the Parser, the query stored correctly in the information_schema.routines