| 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: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | 5.0.51a | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
| Tags: | qc | ||
[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)
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!