Bug #35924 DEFINER should be stored 'quoted' in I_S
Submitted: 9 Apr 2008 8:39 Modified: 17 Oct 2008 17:24
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.0.51a OS:Any
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: qc
Triage: Triaged: D2 (Serious)

[9 Apr 2008 8:39] Peter Laursen
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!
[9 Apr 2008 9:15] Valeriy Kravchuk
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.
[9 Apr 2008 9:18] Peter Laursen
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?
[21 Apr 2008 10:23] Bugs System
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).
[23 Apr 2008 9:39] Alexander Nozdrin
Review comments sent over the email. Patch is approved with a few changes.
[23 Apr 2008 10:33] Bugs System
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()).
[2 Oct 2008 11:58] Bugs System
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()).
[2 Oct 2008 14:16] Bugs System
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()).
[9 Oct 2008 17:28] Bugs System
Pushed into 5.0.72  (revid:sergey.glukhov@sun.com-20081002115752-s1coraxbp7gg2z2a) (version source revid:vvaintroub@mysql.com-20081002154837-s9bff7wyoftpkjf3) (pib:4)
[9 Oct 2008 17:43] Bugs System
Pushed into 5.1.30  (revid:sergey.glukhov@sun.com-20081002115752-s1coraxbp7gg2z2a) (version source revid:mats@sun.com-20081008113713-2vxny72m5w1tywoi) (pib:4)
[15 Oct 2008 15:03] Paul Dubois
This is actually pushed to 5.1.29, not 5.1.30.
[16 Oct 2008 14:56] Paul Dubois
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.
[17 Oct 2008 16:46] Bugs System
Pushed into 6.0.8-alpha  (revid:sergey.glukhov@sun.com-20081002115752-s1coraxbp7gg2z2a) (version source revid:sergey.glukhov@sun.com-20081002140357-1fa0k9axkespo07x) (pib:5)
[17 Oct 2008 17:24] Paul Dubois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:06] Bugs System
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)
[28 Oct 2008 22:25] Bugs System
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)
[1 Nov 2008 9:51] Bugs System
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)