Bug #35924 DEFINER should be stored 'quoted' in I_S

Description:
CREATE
DEFINER = 'aa@@'@'@@bb'
VIEW `test`.`vi` 
AS
SELECT 1;

and

DELIMITER $$
CREATE
    DEFINER = 'aa@@'@'@@bb'
    TRIGGER `test`.`tr2` BEFORE INSERT
    ON `test`.`tab1`
    FOR EACH ROW BEGIN
    -- nothing
    END$$
DELIMITER ;

.. both execute.  In I_S.triggers and I_S.views tables DEFINER is stored as

"aa@@@@@bb"  without quotes.

this is ambigious.  How to tell that it is not 'aa@'@'@@@bb'  or  'aa@@@'@'@bb'

How to repeat:
see above

Suggested fix:
Store in 'user'@'host' format - with quotes.

I am setting this as 'serious' (S2) as it is not possible to generate a generic CREATE TRIGGGER statment from I_S.  And SHOW CREATE TRIGGER is not supported. Also no ALTER TRIGGER.

We want to code a client side ALTER TRIGGER functionality.  This seems impossible to do safe!

Thank you for a bug report. Verified just as described:

mysql> CREATE
    -> DEFINER = 'aa@@'@'@@bb'
    -> VIEW `test`.`vi`
    -> AS
    -> SELECT 1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1449
Message: There is no 'aa@@'@'@@bb' registered
1 row in set (0.01 sec)

mysql> delimiter //
mysql> create table tab1(c1 int)//
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE
    ->     DEFINER = 'aa@@'@'@@bb'
    ->     TRIGGER `test`.`tr2` BEFORE INSERT
    ->     ON `test`.`tab1`
    ->     FOR EACH ROW BEGIN
    ->     -- nothing
    ->     END//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1449
Message: There is no 'aa@@'@'@@bb' registered
1 row in set (0.00 sec)

mysql> select * from information_schema.triggers where trigger_name='tr2'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: NULL
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: tr2
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: NULL
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: tab1
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN

    END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SU
BSTITUTION
                   DEFINER: aa@@@@@bb
1 row in set, 2 warnings (7.19 sec)

mysql> select * from information_schema.views where table_name='vi'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: test
     TABLE_NAME: vi
VIEW_DEFINITION:
   CHECK_OPTION: NONE
   IS_UPDATABLE: NO
        DEFINER: aa@@@@@bb
  SECURITY_TYPE: DEFINER
1 row in set, 1 warning (0.00 sec)

mysql> show create view vi\G
*************************** 1. row ***************************
       View: vi
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`aa@@`@`@@bb` SQL SECURITY DEFIN
ER VIEW `vi` AS select 1 AS `1`
1 row in set, 1 warning (0.02 sec)

mysql> show create trigger tr2\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'trigg
er tr2' at line 1

So, there is NO workaround for triggers.

also mysqldump gets fooled here.

CREATE TRIGGER using  DEFINER = 'nithin@@'@'@ss'

MySQL dump dumps as  DEFINER=`nithin@@@`@`ss`

I think that it is 'assumed' that hostname cannot start with @.  Why then let it create?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45752

ChangeSet@1.2611, 2008-04-21 15:14:58+05:00, gluh@mysql.com +5 -0
  Bug#35924 DEFINER should be stored 'quoted' in I_S
  The '@' symbol can not be used in the host name according to rfc952.
  The fix:
  added function check_host_name(LEX_STRING *str)
  which checks that all symbols in host name string are valid and
  host name length is not more than max host name length(HOSTNAME_LENGTH).

Review comments sent over the email. Patch is approved with a few changes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45878

ChangeSet@1.2611, 2008-04-23 15:25:07+05:00, gluh@mysql.com +5 -0
  Bug#35924 DEFINER should be stored 'quoted' in I_S
  The '@' symbol can not be used in the host name according to rfc952.
  The fix:
  added function check_host_name(LEX_STRING *str)
  which checks that all symbols in host name string are valid and
  host name length is not more than max host name length
  (just moved check_string_length() function from the parser into check_host_name()).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55067

2694 Sergey Glukhov	2008-10-02
      Bug#35924 DEFINER should be stored 'quoted' in I_S
      The '@' symbol can not be used in the host name according to rfc952.
      The fix:
      added function check_host_name(LEX_STRING *str)
      which checks that all symbols in host name string are valid and
      host name length is not more than max host name length
      (just moved check_string_length() function from the parser into check_host_name()).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55086

2694 Sergey Glukhov	2008-10-02
      Bug#35924 DEFINER should be stored 'quoted' in I_S
      The '@' symbol can not be used in the host name according to rfc952.
      The fix:
      added function check_host_name(LEX_STRING *str)
      which checks that all symbols in host name string are valid and
      host name length is not more than max host name length
      (just moved check_string_length() function from the parser into check_host_name()).

Pushed into 5.0.72  (revid:sergey.glukhov@sun.com-20081002115752-s1coraxbp7gg2z2a) (version source revid:vvaintroub@mysql.com-20081002154837-s9bff7wyoftpkjf3) (pib:4)

Pushed into 5.1.30  (revid:sergey.glukhov@sun.com-20081002115752-s1coraxbp7gg2z2a) (version source revid:mats@sun.com-20081008113713-2vxny72m5w1tywoi) (pib:4)

This is actually pushed to 5.1.29, not 5.1.30.

Noted in 5.0.72, 5.1.29 changelogs.

Hostname values in SQL statements were not being checked for '@',
which is illegal according to RFC952.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.8-alpha  (revid:sergey.glukhov@sun.com-20081002115752-s1coraxbp7gg2z2a) (version source revid:sergey.glukhov@sun.com-20081002140357-1fa0k9axkespo07x) (pib:5)

Noted in 6.0.8 changelog.

Pushed into 5.1.29-ndb-6.2.17  (revid:sergey.glukhov@sun.com-20081002115752-s1coraxbp7gg2z2a) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:sergey.glukhov@sun.com-20081002115752-s1coraxbp7gg2z2a) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:sergey.glukhov@sun.com-20081002115752-s1coraxbp7gg2z2a) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)